Migrating SQL Server 2008 to 2012 plus col and date format changes

Question

Migrating SQL Server 2008 to 2012 plus col and date format changes

Michael1

Grasshopper

Points: 17
More actions
February 15, 2016 at 9:13 am

#311433

Hello,
I haven't used SQL or SQL server in three years.
I have been charged to do the migration of a 2008 SQL Server database to 2012 SQL Server.
In addition minor changes must be made to the 2008 database such as: column name changes, zip code format changes, and date and time format changes.
How would you go about doing this, please be specific.
Thanks Mike

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

First: Choose if SQL Server 2012 is the best choice. SQL Server 2014 has been available for almost 2 years now, and SQL Server 2016 should hit the shelves later this year. I do not recomment waiting for it unless you really need one of its features, but I do recommend skipping straight to SQL Server 2014 for a few extra years of Microsoft support (and lots of exciting new features).

Second: Spoend a lot of time reading. About new features, about changes, about removed features. Some of this will be useful during the conversion, and some will give you ideas on what to do next. Good place to start: https://msdn.microsoft.com/en-gb/library/bb500435%28v=sql.110%29.aspx (for SQL Server 2012) or https://msdn.microsoft.com/library/bb500435%28v=sql.120%29.aspx (for SQL Server 2014).

Upgrading from SQL Server 2008 to SQL Server 2012 (or 2014) is a simple supported upgrade. You can do it in various ways, my prefered choice is to build a new server, backup the existing database, then restore that backup on the new server. This will automatically convert the internal structures. (So save that backup, because there is no supported path to move back). The database compatibility level will be left at 100 (SQL Server 2008 compatible), or bumped to 90 (on SQL Server 2012) or 100 (on 2014) if it currently is at a lower value.

The most important thing is to test. Make sure that each and every feature of the database is thoroughly tested. Do not even consider upgrading the actual production database until those tests have been done and signed off.

If you consider setting the compatibility level to 110 or 120, testing becomes even more important. A new cardinality estimator will be used for query optimization once you are at that compatibility level. And while this may greatly increase the performance of some queries, it might also decrease the performance of others. For this, you need to test with a database and a workload that is fully comparable to your production. Note that you can choose which cardinality estimator to use on a per-query level. I will not go in the details here, lots of information can be easily found through your favorite search engine.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Michael1 (2/15/2016)
Hello,
I haven't used SQL or SQL server in three years.
I have been charged to do the migration of a 2008 SQL Server database to 2012 SQL Server.
In addition minor changes must be made to the 2008 database such as: column name changes, zip code format changes, and date and time format changes.
How would you go about doing this, please be specific.
Thanks Mike

Make the "minor" changes first, run them through the QA Gambit and, if they pass, then put them into production. If nothing breaks in a week or two, then migrate your database(s). Don't do both at the same time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 3

Start by running Upgrade Advisor against your database to see if any red flags are raised, e.g. deprecation announcements or features that been dropped from the product in use.

https://msdn.microsoft.com/en-us/library/ms144256(v=sql.110).aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato