An upgrade, in this context,refers to the process of moving from the SQL server version 2008 to the new version 2012.
There are two approaches when upgrading:
The migration from SQL server 2008 to 2012 must be well prepared and tested before going to production.
I have to migrate several databases, the integration services (SSIS) packages, analisys services (SSAS) packages and reporting services (SSRS) reports.
Database administration knowledge is also important, like knowing what is a backup and a restore.
Some T-SQL knowledge is also assumed.
There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. You must read it to ensure that you ate not using a feature that breaks the SQL 2012 compatibility.
After reading the article and fixing any issues, the upgrade of the databases can be implemented in the following steps:
Nevertheless, it is a good practice to think of the architecture of your databases e configure them accordingly.
The following script can be useful you have several databases to migrate:
ALTER DATABASE [mydatabase] SET COMPATIBILITY_LEVEL = 110
where [mydatabase] is the database to change the compatibility level
or goto the database properties and on the options select the Compatibility Level 110.
DBCC CHECKDB([myDatabase]) WITH NO_INFOMSGS
where [mydatabase] is the database to run the integrity checks
NO_INFOMSGS option suppresses all informational messages.
If If DBCC printed any error messages you must fix them so that your database will work correctly.
The Business Intelligence Development Studio (BIDs) is replaced by the SQL Server Data Tools (SSDT).
SQL Server 2012 SSIS offers a wizard for upgrading most of the solution components, but a few settings may be needed to be changed manually.
The wizard appears when you open a SQL Server 2008 package on the SQL Server data tools.
Microsoft has a white Paper that gives you 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012. You can read it.
SSIS 2012 supports two deployment models:
The migration of the integration services (SSIS) packages
If there is a conversion error by the wizard, when you open the package in Visual Studio it is immediately converted. This methodology allows that you can easly control the errors and correct them.
The approach to keep the existing cubes in the multidimensional model is the one I selected, the main reasons are:
In the future I pretend to explore the new models, but for now the mature multidimensional model is the best option.
The analysis services migration, with the selected approach, can be performed in the following simple steps :
- Errors in the back-end database access module. The provider 'SQLNCLI10.1' is not registered.
- The following system error occurred: Class not registered
This error hints that there is a problem with the Data Sources connection string.
When I tried to open a data source the in project and pressed the edit button to edit the connection string, I got the error:
"The specified provider is not supported. Please choose different provider in connection manager."
The SQL Server 2008 Native Client is not installed in the Sql Server 2012 server, so I changed the connection string provider to the native client 11.0 and the issue was fixed.
Another option, if strictly necessary, is to Download and install the SQL Server 2008 SQL Native Client or the SQL Server 2005 SQL Native Client, depending on the connection string provider you want to use.
After his issue was fixed the processing occurred correctly and smoothly.
The SQL Server 2012 Reporting Services (SSRS) has two processing modes:
1) SSRS 2012 report processor. A report that is successfully converted to SSRS 2012 format is executed in this mode and can use the new SSRS features.
2) Backward-compatibility mode processor. A report that cannot be converted to SSRS 2012 is processed in backward-compatibility mode and the new features are not available, but the report is still rendered.
You can find more information .
This approach by Microsoft gives a high degree of compatibility and I don't expect to have any issues in the migration.
The reporting services migration steps are: