Only yesterday, users received much advice
on everything to lookout for when upgrading to SQL server 2012. Today, merely
two years after the release of the SQL server 2012, there is another new
version - SQL server 2014. Of course, this means that many individuals will
contemplate upgrading to the new version. If you are planning to migrate or
upgrade your database, it is highly important to be aware of a few things.
Sadly, many novice administrators think
that upgrading from one database server to another is as effortless as clicking
some buttons. This could not be further from the truth. While one can click
“Next, Next, Finish” and actually consider the task to be complete, it is
crucial to know that the entire upgrade process is more complex. A proper and
comprehensive upgrade process entails detailed planning, research and execution.
Anything short of thorough upgrade preparation will make your end users to see diminished performance immediately the upgrade is complete. Remember that your goal should always be to increase stability and performance. The following steps are specific for upgrading a database data and schema.
Using the new SQL server 2014 upgrade advisor
This advisor is like a consultant – it does not fix all things that are wrong. Its work is to offer advisory service on the appropriate actions to take during your upgrade process. The upgrade advisor (UA) normally recommends two actions that come in two main forms:
·
The actions that are to be done before a migration
·
The actions that are to be completed after a migration
How to review the “breaking changes” section in books online
Microsoft usually publishes a comprehensive list of breaking changes for every version of an SQL server. Reviewing them is of utmost importance. This does not mean that you memorize all of them. All you need to do is be familiar so that in case something odd takes place, you can have a clue about what it is. In most cases, the UA will advise or alert you whenever there is a breaking change. However, you should not rely on it fully.
Executing the DBCC CHECKDB with Data_Purity
DBCC CHECKDB WITH DATA_PURITY is one of the statements that your upgrade or post-migration should run. This statement normally checks your data for those values invalid for the column datatype. This statement is particularly applicable to all databases created before SQL 2005. For those created during or after the mentioned year, users ought to carry out the DATA_PURITY repeatedly using a regular CHECKDB.
Taking backups
Always remember that you are a DBA. For this reason, SQL backups must always be in your DNA. Before you commence any migration or upgrade, you ought to have taken one - and the best one before turning the database over to the end users. Additionally, you ought to save all outputs from these items listed here. This could prove quite helpful in the event that something goes wrong later.
