The company that I work at the present time has received the new SQL Sever hardware, so now is the time to upgrade from the SQL server 2008 to the 2012.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 u
The company that I work at the present time has received the new SQL Sever hardware, so now is the time to upgrade from the SQL server 2008 to the 2012.
An upgrade, in this context,refers to the process of moving from the SQL server version 2008 to the new version 2012.
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:
1.
In-Place: The
SQL Server is upgraded where it is currently installed
2.
Migration: A a
new environment is installed, the data is copied to it and configured with the
existing data. The content from SQL server 2008 must be migrated to the 2012
supported formats.
The approach I am going to take
is the second, since I have a new hardware and I am going to do a fresh SQL
Server 2012 installation.
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.
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.
Requirements
This article
assumes you already know the SQL Server database engine, integration services,
analisys services, reporting services and it's tools.
Database administration knowledge is also important, like knowing what is a backup and a restore.
Some T-SQL knowledge is also assumed.
Database administration knowledge is also important, like knowing what is a backup and a restore.
Some T-SQL knowledge is also assumed.
Databases
The database
engine isn't a complete rewrite. This means that we can expect a deep
compability level.
There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. You must read ithere 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:
There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. You must read ithere 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:
1.
Create the
databases and configure them on the SQL 2012 Server. If you don't have any
special requirements skip this step, since when you perform the restore the
databases are created automatically.
Nevertheless, it is a good practice to think of the architecture of your databases e configure them accordingly.
Nevertheless, it is a good practice to think of the architecture of your databases e configure them accordingly.
2.
Backup the SQL
Server 2008 databases and restored them in SQL 2012.
3.
Change each
database compatibility level from 2008 to 2012. This action is important since
it allows the usage of the new SQL Server 2012 features.
The following script can be useful you have several databases to migrate:
USE [master]
GO
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.
The following script can be useful you have several databases to migrate:
USE [master]
GO
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.
4.
Check the
logical and physical integrity of all the objects in the upgraded databases:
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.
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.
Don't forget to create the
databases maintenance plans.
Integration Services (SSIS)
In SQL Server
2012 the SSIS Package format changed and the specifications are now Open
Source.
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 ithere.
SSIS 2012 supports two deployment models:
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 ithere.
SSIS 2012 supports two deployment models:
1.
Package
deployment model: In this model the the unit of deployment is the package. This
is the model used in previous versions of SSIS and is the default deployment
model for upgraded packages.
2.
Project
deployment model: . The unit of deployment is the project for this model. This
model is new in SQL Server 2012 and provides additional package deployment and
management features such as parameters and the Integration Services catalog
I have decided
to use the Package deployment model for now, since it is the one the gives more
compatibility with the SSIS 2008 model. When I have more time for testing and
development I am going to convert to the Project deployment model. There is an
wizard the performs this task and that is explained in the white paper I
mentioned previously.
The migration of the integration services (SSIS) packages
The migration of the integration services (SSIS) packages
1.
Open the
solution (sln) file with the packages to migrate
2.
The Visual
Studio Conversion wizard appears. It is very simple and after a few next's
pressed, the Package Management options appear.
3.
In the Package
Management options select validate upgraded packages, so that the packages are
validated and only the ones that pass validation are saved.
4.
Disable Ignore
configurations, so that the configurations are validated during the upgrade
process.
5.
The wizard
ends the conversion and you can close it.
6.
Test each
package and verify that it is working as expected.
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.
Analisys Services (SSAS)
The SSAS 2012
has a great deal of changes. The main new features are:
·
Business
Intelligence Semantic Model
·
Tabular model
·
PowerPivot for
Excel and Sharepoint
·
SQL Server
Data Tools (SSDT)
·
Programmability
with support for the new features
The options
for migrating are:
1.
Use only the
multidimensional model
2.
Convert to the
tabular model
3.
Use the
multidimensional model for existing cubes and use the tabular model for new
developments
4.
Use the
tabular model or the multidimensional model depending on the project
requirements
The conversion
from the multidimensional model to the other models isn't supported by
Microsoft at the date this article was written.
The approach to keep the existing cubes in the multidimensional model is the one I selected, the main reasons are:
The approach to keep the existing cubes in the multidimensional model is the one I selected, the main reasons are:
1.
The existing
cubes can be migrated to the SSAS 2012 multidimensional model, without any
modifications.
2.
The existing
reports and client tools will work without any problems
3.
The model is
more mature and supports much higher data volumes
4.
The team has
knowledge of this model and can continue the development without any
significant changes
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 :
1.
Open the
solution (sln) file with the SSAS databases to migrate
2.
The Visual
Studio Conversion wizard appears. The wizard doesn't have any options, so press
Next and then Finish.
3.
Terminated the
wizard and let it execute
4.
Deploy and
process the SSAS database
5.
Test the SSAS
database and confirm that everything is working as expected
The only issued I faced was that
after processing I got the error:
- 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.
- 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.
Reporting services (SSRS)
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 here.
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:
1.
Open the
solution (sln) file with the reports to migrate
2.
The Visual
Studio Conversion wizard appears. The wizard doesn't have any options, so press
Next and then Finish.
3.
An information
message may appear asking if you want to upgrade the report server project to
the latest version. Press Yes
4.
Let the wizard
execute.
5.
Open each data
source and test the connection string. If there is an error fix it.
6.
Deploy the
reports
7.
Test the
reports and confirm that everything is working as expected