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)
- IP Address
- 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
- 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!!