upgradation issues

Question

can anyone please let me know what are the compatibility issues we get when we upgrade from sql 2008 to 2012  and sql2012 to 2014?

please kindly mention some of the issues and how to resolve them for hassle free upgradation.

thanks in advance.

Answer 1

can anyone please let me know what are the compatibility issues we get when we upgrade from sql 2008 to 2012  and sql2012 to 2014?

Known compatibility issues are documented in the Upgrade section of the Books Online:  http://msdn.microsoft.com/en-us/library/bb933942(v=sql.120).aspx.  Pay particular attention to the discontinued and breaking changes section under the Backward Compatibility topic.

please kindly mention some of the issues and how to resolve them for hassle free upgradation.

You can run the Upgrade Advisor to be proactive.  But at the end of the day, there is no substitute for testing, especially for critical and complex applications.  Query performance regression sometimes occurs after a upgrade, often because a query was sub-optimal to begin with.  Index and query tuning addresses the issue.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

Upgradation from 2008 to 2012

1. find old version features discontinued in SQL Server  2012. 

Solution :run upgade advisior and fix the issues 

Note :direct upgrade from SQL 2000 to 2012 is not a supported as as we cannot use the SQL 2012 Upgrade Advisor to examine SQL 2000 instances features discontinued so,
when upgrading from SQL Server 2000 to 2012, consider using both the older 2008 R2 Upgrade Advisor and the new SQL 2012 Upgrade Advisor,
as this will provide a more complete picture of issues you may need to fix.

2. Compatibility issue

The following script can be useful you you have several databases to migrate:

USE [master] GO
ALTER DATABASE [mydatabase] SET COMPATIBILITY_LEVEL = 110
or
or goto the database properties and on the options select the Compatibility Level 110.

3. Check the logical and physical integrity of all the objects in the upgraded databases:

DBCC CHECKDB([myDatabase]) WITH NO_INFOMSGS

If If DBCC printed any error messages you must fix them so that your database will work correctly.


4. find data for values that are no longer valid -EXECUTING DBCC CHECKDB WITH DATA_PURITY
This statement will check your data for values that are no longer valid for the column datatype.
 For databases created prior to SQL 2005, this step is rather important to take.
 For databases created in SQL 2005 and later, the DATA_PURITY check is supposed to be done automatically with a regular CHECKDB.

5.   find any page count inaccuracies EXECUTING DBCC UPDATEUSAGE COMMAND-

This command will help fix any page count inaccuracies that are resulting in the sp_spaceused stored procedure returning wrong results. And much like the DATA_PURITY check, 
this command is also recommended for databases that were created prior to SQL 2005. For databases created in SQL 2005 and later, you should only 
run this command if you feel you are getting inaccurate results from sp_spaceused, and you should note that for very large tables this command could take a long time to execute.

6.Perormance issue UPDATING STATISTICS
This one is not to be skipped and is simply a MUST for any migration or upgrade checklist:


7. REFRESHING YOUR VIEWS USING SP_REFRESHVIEW

So, if you are migrating a database in your environment to a new server then it would be a good idea to refresh your views using sp_refreshview

Answer 3

can anyone please let me know what are the compatibility issues we get when we upgrade from sql 2008 to 2012  and sql2012 to 2014?

please kindly mention some of the issues and how to resolve them for hassle free upgradation.

thanks in advance.

I know this is interview question,

Problems during upgrade totally depends on features you were using in previous version.

Whats new in sql server 2012.

Upgrade advisor is your best bet to know about any known issues

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 4

Hello,

Please take in consideration discontinued features:

http://technet.microsoft.com/en-us/library/cc707782(v=sql.110).aspx (SQL 2012)

http://technet.microsoft.com/en-us/library/cc707782(v=sql.120).aspx (SQL 2014)

The following article can help you avoid possible compatibility issues at the operating system level:

http://support.microsoft.com/kb/2681562

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com