On April 12, 2016 Microsoft will end extended support for SQL Server 2005.  This means no more bug fixes, no patches and no support.

On April 13, CIO’s everywhere will come to their DBA’s desk, eyes bulging in panic, when they realize this lack of support puts them out of compliance with a wide range of laws and contractual requirements. You know, the rules that require data be kept secure on supported systems.  Those unfortunate DBA souls who did not alert management to this upcoming end of support will now be condemned to the worst sort of fear-driven stampede to get the databases upgraded.

You, however, will be able to sip a drink and enjoy the first days of spring because you will have followed the suggestions in this post and planned the transition out, obtained the resources you need and followed best practices (including extensive testing of your applications) to ensure a smooth transition well before April of 2016.

Upgrading SQL Server is a project and should be treated as such.  At a minimum, every application that touches the database will need to be tested to make sure it works in the new environment.  There are also a number of questions that need to be addressed before you start your project, just to get the roadmap to your end state.  These questions include:

• How many databases and servers are involved?
• Do the servers and the server OS require upgrades?
• On-premises or Cloud-based upgrades?
  • If on-premises do you perform the upgrade “in place” leaving the databases on the “old” server or port the databases to either new “bare metal” hardware or a new virtual machine (VM)
  • If cloud based, should you use a SQL Azure VM or Azure SQL?
• What applications are using each database?
• Are you using any obsolete or deprecated code? If yes, then how will you address this?
• Do you add any new functionality?
• Do you leverage any of the new features in SQL Server 2014 or 2016?

Scope

Like any project, this project must start out with a definition of scope.  Here, one would answer questions such as:

• How many servers? Make sure you include development and QA servers.
• How many databases?
• How many applications and what would need to be changed?
• What is in those databases? What will “just go” and what needs to be re-worked?
• Does the business have new requirements, including performance requirements that should be met as part of this project?
• Are there any technical requirements (e.g. from your security team) that must be met as part of the upgrade?

For this phase of the project technical resources need to team with business analysts to define the scope.  I’ll focus on the technical team’s work for purposes of this article.

Your first step should be to make sure you have identified all of your servers, including anything installed on someone’s personal computer or laptop.  All of these should be upgraded to avoid leaving any holes or back doors for a hacker.

Microsoft provides an Assessment and Planning toolkit (MAP toolkit) that will survey your domain and gain counts of the number of databases and servers and the state of their readiness.  The tool will provide you with a rough estimate or “tee-shirt size” (small, medium, etc.) estimate for your upgrade project.  Open the MAP tool.

 

Click on the “Collect inventory data” link and you will be taken to a wizard.  Follow the wizard through the steps.  The wizard will then survey the systems on the domain per the rules you configured using the wizard.

Once the wizard has collected data on your domain you will be able to drill down on the Database view.

This graphical report is useful, and far more useful are the two spreadsheets you can generate from this data.  First, select “Generate SQL Server Assessment Report” and review this data.  You should find a good deal of information on current server state (amount of memory, number of processors) as well as data concerning what services are actually running, such as this survey of a few laptops I took for demonstration purposes.

Next, click on “Generate SQL Server Database Details Report” and generate a report with some of the data you will need to properly scope this project.  The report will contain object counts for each database, collation levels and the sizes of each individual databases.

Right now I can only get the tee-shirt size for this project, but I do not have sufficient information to list out the project steps or ascertain what resources I may need to move forward.  For example, do we need .NET developers to clean up SQL Statements embedded in code?  Are there third party vendors who need to certify software for more recent versions of SQL Server?  Are you relying on unsupported or deprecated code?

We can gain some insight into the details of the work involved using the SQLAzureMW (SQL Azure Migration Wizard).  Don’t let the name throw you as you can also use this tool to analyze your current databases for an upgrade to SQL Server 2014.

As you can see from the UI, this will be a simple wizard.  Note, however that you can use a SQL Profiler trace file.  The best practice is to run the wizard once on the database you are interested in porting, and again on a trace file.

Note that you should gather the SQL Profiler trace data at the appropriate time.  For example, if you are examining an accounting database, then month-end close may be the most appropriate time to gather your trace records.  In other words, time your trace to gather data during peak usage periods.

Once you have the application open, select the target server. In this case I will use Azure SQL Database.  You will next be prompted to connect to the relevant server then you will be asked to select one or more databases for analysis.  In this case I will analyze the Adventure Works database for SQL Server 2005.  You will then be asked to select which database objects should be scripted and analyzed.  In this case I accepted the default “Script all database objects”.

The Azure ML tool allows you to script the movement of data as well as the schema, but in this case I simply want to examine the schema only.  Once you have selected the objects to analyze, select the “Advanced” button.

In the “Script Table/Data” line you will see the default “Table Schema with Data” selected.  For purposes of this article I selected “Table Schema Only”.

Review your selections and start your analysis.

There are numerous objects in the Adventure Works 2005 database that are not supported by SQL Azure.  The tool attempts to fix some of these common mistakes.  For example, SQL Azure requires every table to have a clustered index.  The tool converts a non-clustered index on such a table to a clustered index. Such changes will appear in brown print in your output.  Objects that cannot be easily converted, such as XML Schema Collections are labeled in red, as seen below.

 

Your next step would be to gather Profiler data to see how the application is actually used and the actual SQL executed on the database.  In this case the more relevant data, the better.  Using the accounting system as an example again, I would recommend that you run profiler the entire time it takes the finance staff to complete month or quarter end closings, typically several days.

Conclusion

Upgrading from SQL Server 2005 to any platform should be considered a major project.  As you can see from our results with Adventure Works, some of the issues that arise may require major re-writes of portions of the database.

Once we have this data, we can then go back to the project manager and business and give them a proper estimate of what it will take to upgrade these systems.

You don’t have to tackle this alone. RBA has helped many organizations prepare for the SQL Server End of Support. Contact us to learn more: [email protected].