Category Archives: Database Administration Tasks
Sunday Funday
Today’s installment of Sunday Funday brings you a great little Tic-Tac-Toe game with T-SQL exercise by Adam Haines found on SQL Server Central. Enjoy!
SQL Formatting Tip
Last night was the first meeting for the new Tampa side SQL Server User Group and the turnout was great. I gave my first user group presentation and I was a little more nervous than the last time I gave this presentation. At this point, I am going to attribute that to the fact that I have to see this group every month where as in Pensacola I do not. But I am moving closer to completing my goals for the year and that is a great thing. To be a great speaker, obviously you have to practice as a speaker, seems simple enough.
Moving forward, I wanted to pass along a great tip last night from Pam Shaw (blog|twitter), our fearless leader. She shared a site with us called Instant SQL Formatter that provides automatic formatting and coloring of many different flavors of coding including SQL Server and Oracle. It is a pretty nifty utility especially with all of the available formatting options. Check it out and thanks Pam!
Master Maintenance Plan, Part One
For the last two days, in between other tasks, I have been kicking around ideas to organize or reorganize scheduled jobs and maintenance plans into a grander scheme solution that sends me ONE e-mail a day. Sounds like a minor operation, but I have 32 SQL servers including four clusters. Currently, many of the 300 databases send me nightly e-mails to let me know whether the individual operations succeeded or failed. There are still some maintenance plans that I have not replaced yet. I have also implemented Ola Hallengren’s backup solution on many of the servers which greatly reduced the number of e-mails that I receive. The plan is to install that solution on every server. This solution has saved me hours upon hours of work, check it out if you have not already.
Sometime ago, I wrote or found a script (no, I cannot remember) that would send me an e-mail letting me know the backup type and date/time of the last backup. So here we are, and I am trying to take this to another level by exploring solutions to greatly reduce e-mail traffic and reduce the amount of time it takes me to assess my server’s overnight maintenance thus allowing me to spend more time being proactive. With all that being said, I found the following article, Use PowerShell to Report SQL Server Backup Status, written on the Hey, Scripting Guy! Blog.
As this plan develops, I will post new blog posts. Enjoy or let me know how you handle it. Let’s share ideas!
Database Tracking
This morning one of our VMWare clusters went down when our SAN guys inadvertently unplugged the wrong storage array. Oops! We had to reboot some of our development and test servers including my SQL Servers. No big deal, this happens from time to time.
Good manners dictate that I notify the effected application administrators so they don’t panic seeing their connections drop. This is a pretty normal procedure. In my previous positions I had at the most five servers and I knew every database and who it belonged to. However, in this environment, I have almost 300 databases and knowing what they go to and who to contact is handled by a spreadsheet that was created before I started. Seems like there should be a better way to track this and to notify me when new databases are created. How are you handling this task?
Common Request from Vendor
These days it seems that I am getting more and more requests like this one:
Can you please backup XYZ database and then run the following command, UPDATE XYZtable.column SET FIELD=’somevalue’ WHERE KeyField = ‘value’?
Mind you, that XYZ database is a production database with a legitimate development and/or acceptance counterpart depending upon the importance of the data. Here is my usual response:
Sure. I would be happy to run that for you in the DEVELOPMENT environment. <Pause> What do you mean production is down and this needs to go straight to production? <Pause> So who changed this value to cause your application to go down? <Pause> The vendor says this happens all the time with the application? And this is the fix?
Unfortunately, these types of requests seem to be more common place when dealing with vendor databases, at lease in my environment. Having been a developer in a previous lifetime, this really bothers me. We called that a patch, where the vendor fixes the application to stop causing the problem especially when data was involved. Fix the problem, not the symptoms people. Enjoy!
AD Accounts Are Great, But…
Using Windows Authentication for your database is a great best practice to follow. However, one caveat to remember as one of my administrators found out today. Do not use that account as a login if it is also the account you use for your database.
We setup an AD account for his database, that part is great. He thought that was also the account to use as his application administrative account. Not a good idea because when he fat fingered the password this morning a few times, as people tend to do on Monday mornings when their coffee hasn’t fully kicked in, he locked out his application as well as the database sending 150 alert messages all over the department. This set off a chain reaction where he rebooted his server after unlocked the account and proceeded to call me and tell me that SQL was down and sounded the sky is falling alarm. He overreacted essentially.
When I figured out what he did, I explained the situation and told him that he needed another AD account for his application or I could setup another one for his database. He said he installed his application under that account and cannot change either one. Not the best scenario, but every time you fat finger the account you will bring the world down upon you. Your choice. He chose the road well traveled unfortunately. Enjoy!
Color Coding SSMS
Finally got around to color coding my SQL Server Management Studio (2008) on my desktop today, which is a great feature if you haven’t set it up already. Essentially it changes the color at the bottom of your query window which is usually a light yellow. Why bother?
Well for me I set my production boxes to red, acceptance boxes to yellow, and development boxes to green to remind my brain whether it is safe to make changes to the environment. Once you have a few query windows open, you tend to forget to which environment that window belongs to. This is not foolproof, but it is a handy reminder to be mindful of your environment.
Not sure how to set it up?
Well using the registered servers feature, add your server and select the properties. Then choose the connection properties tab and select the ‘use custom color’ option and then press select to choose your color. Easy and remember to restart SSMS for this to take effect. Enjoy.
Presentation Ponderings
After my first presentation at SQL Saturday Pensacola, I am ready to do another and need to submit for SQL Saturday Orlando later this month. All of that is not a problem, I just don’t know if I should do what I did in Pensacola and expand on it because the audience seemed to enjoy it or forge a new path. I have good arguments for both, maybe I will submit both and see which one gets picked, if they get picked, of course. I have three weeks to decide, but I should not wait until the last minute.
The presentation that I did in Pensacola was the Seven Deadly Sins of the SQL Server DBA. The other one I am thinking about doing is just a straight up best practices install guide. I am very passionate about best practices and there are so many people who I’ve spoken with said that they wish they had known some of the best practices at the time of their install instead of correcting them after the fact. It also may help that I was asked to give the Seven Deadly Sins at the next Tampa Bay user group meeting this month, so we will see how that goes, maybe it was a one-hit wonder and the decision will be made for me? What do you think?
SQLChicken’s New Tool
Preparing to blog, I saw a new blog post by Jorge Segarra (blog | twitter) that will identify and alert you when you have long-running alert jobs. This is something that I have had on my list to sit down and write when I found some extra time (translation: never going to happen any time soon). I was so excited, I thought I would pass it on to you before I even played with it myself. The life of a DBA sometimes does include doing the happy dance when you see something totally cool. Enjoy!
Know Your (Pre-Defined DB) ROLE!
Yesterday we talked about the Rock and how he can help you with SQL Server security ala “know your role.”
Do you know your SQL Server pre-defined database security roles?
These pre-defined roles will allow their respective members to perform defined sets of activities within each database. It is important to realize that these exist in every database and you cannot drop these roles. They are as follows:
- db_owner Role: This is the Mac Daddy of fixed database roles as it can perform all configuration and maintenance activities on the database including dropping the database. It is the only role that members can add additional users to this role.
- db_securityadmin Role: Members of this role can modify role membership (except db_owner) and manage permissions.
- db_accessadmin Role: Members of this role can add or remove access to the database for Windows logins, Windows groups, as well as SQL Server logins.
- db_backupoperator Role: Members of this role can back up the database, that is all.
- db_ddladmin Role: Members of this role can run any Data Definition Language (DDL) command in a database, in other words they can run ALTER, CREATE, DISABLE TRIGGER, ENABLE TRIGGER, DROP, TRUNCATE, and UPDATE STATISTICS statements.
- db_datawriter Role: Members of this role can add, delete, or change data in all user tables.
- db_datareader Role: Members of this role can read all data from all user tables.
- db_denydatawriter Role: Members of this role cannot add, modify, or delete any data in the user tables within a database.
- db_denydatareader Role: Members of this role cannot read any data in the user tables within a database.
There are several of these that I rarely use, but they are there if I need them. Like the Rock says “know your role!” Enjoy your weekend!
SQL Swampland 