Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Manage SQL Server Instances for Decommissioning and Upgrade Projects


By:   |   Read Comments (1)   |   Related Tips: More > 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.

Define your environments

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:

 

Make an icon for each instance

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.

 
In your PowerPoint document, copy a green icon card.

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.

 Classify each instance into an environment

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.

Go to the last slide of your PowerPoint presentation.

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.

De-scope instances

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.

The significant databases are those not compatible with your target SQL Server environment
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


Last Update:






About the author
MSSQLTips author Graham Okely Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, December 27, 2013 - 12:23:29 PM - Jeff Bennett Back To Top

Very good advice, Graham.   Been looking for a way to represent this to management and your method is informative and intuitive.

 

Thanks


Learn more about SQL Server tools