By: Graham Okely | Comments (1) | Related: > Upgrades and Migrations
Problem
A team working on decommissioning about 15 SQL Server instances out of 40 SQL Server instances was finding it difficult to understand the status of the decommissioning efforts. At meetings we needed something simple to give us an overview and summarize our progress. Do you have any suggestions? Check out this tip to learn more.
Solution
I would like to outline a simple and easy way to understand the status of your SQL Server instances. This solution uses PowerPoint. You can print this out and take it to meetings to help your deliberations over resources, planning, commissioning and decommissioning discussions.
A project manager I once worked with suggested this system to me. "Build a three slide power point summary of the instances, legacy, remaining and SQL Server 2012" was all he suggested.
The plan was to upgrade as many databases as possible from legacy SQL Server systems to the most up to date relational database management system at the time, which was SQL Server 2012. Not all databases could be upgraded of course.
We went for the familiar red, orange and green traffic light conditions.
- Red meant we successfully decommissioned the instance
- Orange meant we were either planning to decommission that instance or build that instance. It meant the instance was in transition. It sounds like a bit of a conflict, but it works in the context of the three slides.
- Lastly we used green to indicate the system was up and running.
PowerPoint was available in the client environment so we used that rather than any other application.
At this point open up the PowerPoint presentation SQL Server Overview Template.ppt. If you are listening to the pod cast Overview.wma I will leave a few seconds gap between steps so that you can pause the mp3 while you do the activities.
Step 1 - Define your SQL Server environments
Identify all the environments at your client. They may include Production, Test, Development, etc.
Open the PowerPoint presentation and go to the slide master page. Click on "View" ribbon then the "Slide Master" button.
In the downloadable template I have production, development, test and training. Add or remove lines and section names and build suitably sized areas.
Once finished, click the "Close Master View" button to return to the normal view and save your PowerPoint document.
Now you should have a PowerPoint presentation with three slides and all your environments listed.
Step 2 - Create an icon for each SQL Server instance
Build a list of all the instances you are expected to manage. Even the one that people say "oh do not worry about that one because of ..." some reason. At least show it on your summary and mark it as out of scope. This is wise because it avoids someone mentioning an instance at a meeting and you being not quite sure if it is the one you are thinking of or not.
Gathering an instance list may be difficult or easy. Do not worry if it takes days or weeks to discover them all. As DBAs we cannot be expected to manage instances we have no access to. However we should do our best to connect to any instance we know about and try to understand it and ensure it is being backed up.
I suggest using instance names and not server names should they differ. I prefer to use the command Select @@Servername to get an instance name.
I have found you can find SQL Server instances via these methods:
- Lists from pervious DBAs
- From application owners
- The server team sometimes know the SQL Servers
- Use tip 1113 to use the command sqlcmd -L to list SQL Server instances
- Use tip 2658 PowerShell and the SMO object to list SQL Server instances
- Use the MAP Tool from Microsoft to discover SQL Server instances
- See Tip 2057 by K. Brian Kelley for why listing all of your SQL Servers does not always work
In your PowerPoint document, copy a green icon card. Paste in an icon for each instance you know about. Leave them all green for now, but change the name on each one.
Step 3 - Classify each SQL Server instance into an environment
Move each of the icons into the correct environment on your PowerPoint slides.
If the instance has both a non-production and a production database on it then you should group that instance in the production group. However we had a case where we kept the icon in the training area for a few weeks until the production database was migrated.
Now you should have an icon for each instance you know about and those icons should be approximately in the right environment groupings.
Step 4 - Indicate the status of each SQL Server instance
Go to the legacy or first slide.
Out of all the icons which instances would you like to stop?
Click on the "To be stopped" traffic light on the right.
Click on the Format Painter to capture the correct color.
Click on the icon of an instance you wish to designate as to be decommissioned.
That action should change the icon background color.
Repeat that for every instance you wish to decommission.
Save your PowerPoint document.
Move to the next slide and repeat the color changes.
Now you should have a PowerPoint presentation that has every environment, every instance you know about and every status recorded.
Step 5 - Plan your future SQL Server environment
Go to the last slide of your PowerPoint presentation.
Notice the orange icon says "To be built".
So any future clusters or instances you have in mind or that you have heard about should be listed. Add the icons and make them orange.
List what you think you need and be prepared to defend it at discussions.
Step 6 - Out of scope SQL Server instances
Sometimes there are SQL Server instances just sweetly running the phone system or a CAD system.
A vendor installed it and you may not even have an sa password for it. Mark it clearly as "De-Scoped".
Use the red dashed text outline I have in the presentation and copy it over any icons you want to flag as out of scope.
Step 7 - Consolidate systems
If you have been ordered to consolidate your systems then that is clear direction for you. It will eventually mean less work for you as the DBA as fewer instances means less administrative overhead. If you have not been ordered to consolidate, you should keep in mind opportunities to migrate databases onto new infrastructure for performance gains. If it helps list specific, or key, databases that you need to have in mind at meetings and discussions. Build a color key again and paste in database names as required.
I do not recommend you list every database. We only added significant database names to the icons. Otherwise it could be a full time job keeping your summary diagram up to date. The significant databases are those not compatible with your target SQL Server environment.
Example database status colors.
Next Steps
- Download my example Power Point file
- Look here for Power Point tips
- Place in your replication, mirroring and other important processes.
- Highlight your target systems for new databases.
- Listen to the pod cast Overview.wma
About the author
Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
