One of the many perks I have of being on a DBA Team is the number of ideas that are bounced around to find different solutions. One project that we have ran across that we are working on implementing is enforcing policies based on our newly stood up CMS server. The CMS server is nice simply because we can have rapid deployment among 90 some servers.

Not all of these servers will have all of the policies pushed out to them; some might only  have 3 some might have 23 who knows. In order to alleviate some of that pressure we split up our servers into 4 groups within our CMS Server; basically segregating out test boxes, prod boxes, some straggling 2000 boxes (due to third-party apps), and boxes the group doesn’t maintain but still nice to see what they are.

I’m big on  having a plan and road map in place then executing that plan; our team is deciding which group we’d like to tackle first, then choose the policies (standard) out of the box to push first. We can come back in a separate phase and tackle custom policies.

Another great aspect I like about the CMS server-side of things is that we now have a one location repository with all of our server information that we are responsible for. In the repository table we capture the following pieces of information:

• Server Name
• Instance Name
• Server Name \ Instance Name (reporting purposes)
• Domain
• IP Address
• Port
• Memory Size
• CPU Count
• Current Version of Windows
• Windows Name
• SQL Server Product Name
• SQL Server Version
• SQL Server Edition
• SQL Server Service Pack
• SQL Server Language (eh why not throw it in)
• SQL Server Root Directory
• SQL Server Collation
• Methods
• Business Owner
• DBA Notes (in case we need to add special info on the servers)
• Maintained (Do we maintain this server or not; in our industry some servers we do not maintain but we are aware that they are there and in place)
• Category (prod, test, dev, etc)

Having one central location with all this information has helped tremendously. In the next few posts I’ll go over the policies of what are considered some best practices. Each business is different so tailoring them to meet your needs is the fun part.

Let the games begin!!

I haven’t been able to post as much as I would like to and am working on changing that this upcoming year; building it more into my schedule. As this year begins I want to take a moment and implore my readers to learn something new this year. As a SQL DBA, I am always trying to evolve my techniques, learn new ways of doing things, enhancing productivity, streamlining processes, etc. In order to do this I have to stay on top of my game  – always reading articles, blogs, doing extra work on my own time, conferences, SQL Saturday’s.

So I ask the question, what are you going to do in 2012? Will we stagnate? I hope not….take this not as a rallying cry but more of an encouragement; there are so many opportunities out there to get involved in the community, helping others, and just learning in general.

As a SQL DBA my door is always open to help as is so many others. Good luck this year and I wish everyone the best in their endeavors.

This is the newest thing that our team is integrating and so far I’m digging it. It’s nice to have all the servers that a DBA is in charge of held in one general location. I know that there are several good reads off hand on the web that Brent Ozar has done and I believe Kevin Kline has done over at SQLServerPedia. With this great tool also comes great responsibility; say for example you have 80 servers on your CMS system; once you log in to the Central Management Server via Management Studio you will be connected to all 80 servers. In saying that I would strongly suggest that you are very careful on what you are running. I do like the tool though; it is very  handy and I highly recommend it so ~ Check It Out

Have you ever been in a situation where you started to create this masterful script only to find that it was already out and available to begin with. I think that is one of the biggest advantages within the SQL community; everyone really seems to try to help each other out by publishing items, sharing knowledge on issues and solutions, pointing others in the right direction. In saying that I recently wanted to find every job on every server that did not have a notification; simple enough task. As I sat down and began to write the code out I began to read through some blogs and I came across one that was already doing the same thing that I had just started on. Jonathan (a.k.a. FatherJack) wrote an article based on finding notification settings on SQL jobs. After review I ended up tweaking the script somewhat to only pull jobs I had enabled at the time and also to only order by the actual email alert level. Below is the tweaked script (does not include the only enabled filter):

SELECT  [j].[name], 
        CASE WHEN[j].[notify_level_eventlog]=1THEN'On success' 
            WHEN[j].[notify_level_eventlog]=2THEN'On failure' 
            WHEN[j].[notify_level_eventlog]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Event Log Entry], 
        CASE WHEN[j].[notify_level_page]=1THEN'On success' 
            WHEN[j].[notify_level_page]=2THEN'On failure' 
            WHEN[j].[notify_level_page]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Page Alert], 
        CASE WHEN[j].[notify_level_netsend]=1THEN'On success' 
            WHEN[j].[notify_level_netsend]=2THEN'On failure' 
            WHEN[j].[notify_level_netsend]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Net Send Alert], 
        CASE WHEN[j].[notify_level_email]=1THEN'On success' 
            WHEN[j].[notify_level_email]=2THEN'On failure' 
            WHEN[j].[notify_level_email]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Email Alert] 
FROM    [dbo].[sysjobs] AS j 
ORDER BY [j].[notify_level_email] DESC

I give props to Jonathan and to view his script along with an additional script that shows operators you can click here

As a person I’m all about lists; I have to have stuff written down one because I need order and two because I forget things a lot it seems like. So, as one would guess, as a Database Administrator my feelings are no different. I have to have a DBA checklist that I go through daily, weekly, monthly, and quarterly. For me it is a sense of having a guideline to go through; now one will bring up the fact of automation. If everything was automated to alert of any possible issues then there is not a need for a checklist. While some of these points are indeed valid I’ll keep on keeping on with my daily checklist. If you do not have one I suggest you get one; if you have one and need to modify it I suggest you take some time out of your day and review and add where needed. Just a few good checklists that are out there on the web are Brad Mcgehee’s, MSSQL Tips, and many more.

I cannot say that I have followed one particular standard that has been laid out but have taken some ideas from here, some of my own, and some on researching and meshed them altogether; point being I believe it is a great idea to have a checklist in place. DBA’s should be pro-active in their daily activities to find any issues before they arise.

As a database administrator I’ve seen first hand how key communication really is. Throughout my career I would pride myself on bridging the ever so-called gap between developers and database administrators. It’s an age-old additive right? Developers think Administrators are too staunch in allowing them to do what they need to do and that they have no clue on how to develop, and administrators think developers just flat-out write bad code among other things. Well, something hit me this week that I never really gave much thought to and is kind of ironic ~ do I have any gaps between myself and the rest of the database administration team? Granted some people are the sole team but for those who are on more than a one man team how is your communication with your peers? I get so busy in looking at how other departments are doing I forget to check my own door step and see how our team is interacting.

How is your communication?