© dbi services

dbi services Migration to SQL Server 2012

1 26.04.2012 www.dbi-services.com

Antonio De Santo

Consultant

Stéphane Haby

Consultant

© dbi services

Agenda

dbi services

i. Introduction

ii. Why ?

iii. How ?

iv. After…

v. Conclusion

Migration to SQL Server 2012

26.04.2012 2 www.dbi-services.com

© dbi services

Introduction

what is your migration?

www.dbi-services.com 3 26.04.2012

You have SQL Server

You have another RDBMS

© dbi services

Migration between distant generations are more difficult

Only 2 generations away for a migration:

Migration from 2000 to 2012 requires an intermediate step

Introduction

SQL Server generation

www.dbi-services.com 4 26.04.2012

7.0 2000 2005 2008/R2 2012

© dbi services

Agenda

dbi services

i. Introduction

ii. Why?

iii. How?

iv. After…

v. Conclusion

Migration to SQL Server 2012

26.04.2012 5 www.dbi-services.com

© dbi services

Why?

From SQL 2000 to 2005

Solid offering for departmental and mid-market IT and enhanced

enterprise data platform:

SQL Server Management Studio

High Availability: Database mirroring, piecemeal restore, indexation online

Partitioning (tables and indexes)

XQuery and CLR

Snapshot isolation

Service broker

© dbi services

Why?

From SQL 2005 to 2008

Comprehensive data platform solution for the enterprise:

Resource governor

Transparent data encryption

Auditing

Data and backup compression

Policy-based management

New data types (spatial, dates, and time)

Filestream

New cube designer

Write-back to MOLAP

SSAS performance and scale

© dbi services

Why?

From SQL 2008 to 2008R2

Comprehensive, integrated data management and business

intelligence (BI):

PowerPivot for Microsoft SharePoint

Report Builder 3.0

Support for up to 256 logical processors

Master data services

Multi-server management

Data-tier application component (DAC)

Complex event processing

System preparation

Unicode compression

© dbi services

Why?

From SQL 2008R2 to 2012

Facility to manage High availability, performance features:

AlwaysOn

Windows Server Core support

ColumnStore Index

Visual Studio 2010 & SQL Data Tools

FileTable

Contained Databases

Sequences, new functions…

PowerView

MDS, DQS

© dbi services

Why?

SQL Server & OS

SQL Server 2000:

Not available of Windows 2008 & 2008 R2

SQL Server 2005:

Windows 2000 SP4, 2003 SP1 & 2008(SP2 for SQL Server)

SQL Server 2008:

Windows 2003/R2 SP2, 2008 & 2008 R2(SP1 for SQL Server)

SQL Server 2008 R2:

Windows 2003/R2 SP2, 2008 SP2 & 2008 R2

SQL Server 2012:

Windows 2008 SP2 & 2008 R2 SP1

Windows 2008 R2 SP1 server Core

© dbi services

Why?

Microsoft support

SQL Version Mainstream Support Extended Support

SQL Server 2000 SP4 08.04.2008 09.04.2013

SQL Server 2005 SP4 12.04.2011 12.04.2016

SQL Server 2008 14.01.2014 08.01.2019

SQL Server 2008 R2 14.01.2014 08.01.2019

Windows Version Mainstream Support Extended Support

Windows Server 2000 30.06.2005 13.07.2010

Windows Server 2003 13.07.2010 17.07.2015

Windows Server 2008 09.07.2013 10.07.2018

Windows Server 2008 R2 09.07.2013 10.07.2018

© dbi services

Agenda

dbi services

i. Introduction

ii. Why?

iii. How?

iv. After…

v. Conclusion

Migration to SQL Server 2012

26.04.2012 12 www.dbi-services.com

© dbi services

How?

Microsoft Assessment and Planning

MAP version 6.5

Functions

Discovery and inventory

Database Consolidation Appliance

SQL Server Licencing

Detail reporting

Link:

http://technet.microsoft.com/en-us/solutionaccelerators/dd537572.aspx

© dbi services

How?

SQL Server update

Platform change?

Db granular control?

Fallback

?

parallel versions

?

Direct update Parallel update

© dbi services

How?

Direct update

Simple

Fast

Same server for applications

Update System Data's

little control

non-granular

Downtime

No fallback (need a reinstall)

SQL Server 2008 R2: Since 2000 SP4, 2005 SP2 & 2008

SQL Server 2012: Since 2005 SP2, 2008 & 2008 R2

© dbi services

How?

Direct update

Prerequisites installation

Stop Services

redirects services to the new version

Single user Start

Attach resource db

Stop Services

Check Binaries

installation BEGIN

Restart services

Update all databases

Update Scripts

Delete old binaries

END

© dbi services

How?

Parallel update

Change platform

Can be tested

Db granularity

Fallback

Complex

Volumes copy

Change instance or/and server name

New instance or Server

Backup/restore or attach/detach

© dbi services

How?

Preparation checklist

Schedule

Known your applications

Document and analyze the source platform

Test and compare the application’s behavior

Document the scenario and the migration procedure

Script and automate the migration

Test the scenario

and test turning back

Check your backups

"I love it when a plan comes together"

© dbi services

How?

Preparation

Security: users/logins mapping

ALTER USER user WITH LOGIN = login

Clonage : http://support.microsoft.com/kb/918992

Transfer SQL Agent jobs

Script or SSIS job

Maintenance plan

DTS / SSIS

Verify / Revise / Recopy

Migrate DTS to SSIS package

Others

Stored procedure in master

SQL Mail

© dbi services

How?

SQL Server Upgrate Advisor

Analysis of several components, including DTS

Analysis of traces or SQL queries files

Displays a detailed report

Provides actions to take before and

after the update

... But does not replace the test

© dbi services

How?

Documentation

Uses Tools

SQLDiag.exe

Sp_configure, sp_dboption

storage, network, security configuration

SQL Agent Job

DTS / SSIS

Performance's monitoring

Perfmon

SQL Profiler

Application’s response time

© dbi services

Agenda

dbi services

i. Introduction

ii. Why?

iii. How?

iv. After…

v. Conclusion

Migration to SQL Server 2012

26.04.2012 22 www.dbi-services.com

© dbi services

After...

Tests before migration

Is essential

Migration changes the performance of my queries and stored

Procedures in good…and sometimes in bad!

An update change your RDBMS Engine

The optimizer behavior is different

Query plans are different

Procedure’s cache management is different

© dbi services

After...

Analysis Tools

SQL Profiler

Capture activity

Replay and recapture

RML Utilities (Traces from 2000 to 2008R2 not 2012)

ReadTrace

Replay & Reports

Distributed Replay (Traces from 2005 to 2012)

The optimizer behavior is different

Query plans are different

Procedure’s cache management is different

© dbi services

After...

Improve performances

Update statistics (FULL SCAN if possible)

Review or delete query hints

Rewrite queries

Measure tempdb usage

Check query plans, using the DTA

Use the new features

Partionning

Columns Store index, filtered indexes

© dbi services

After...

Checklist

Review all log (eventlogs, errorlogs)

Check the integrity

DBCC CHECKDB ( For SQL 2000: DBCC UPDATEUSAGE)

Check the scheduled tasks and maintenance plans

Check the security

Update statistics

Reconfigure and use the new features

Perform validation tests, including fallback

After validation: decommission / uninstall the old servers

© dbi services

Agenda

dbi services

i. Introduction

ii. Why?

iii. How?

iv. After…

v. Conclusion

Migration to SQL Server 2012

26.04.2012 27 www.dbi-services.com

© dbi services

Conclusion

SQL Server Migration Assistant

Free download

For Sybase, Oracle, MySQL or Access

Migrate to 2005, 2008/R2, 2012 or Azure

Provide an estimate report

Automates main operations

Data types mapping

Objects Migration

Data Migration

© dbi services

Conclusion SSIS Packages for Data Migration Generator

Develop by Pascal Belaud, Microsoft France

Automatic generation of SSIS Packages for data’s transfer from all

OLE DB connector to SQL Server

Free on codeplex: http://SSISGenerator.codeplex.com

© dbi services

Conclusion

SQL Server Analysis Services

Structural changes

No virtual cubes

No virtual dimensions

Direct upgrade supported

Parallel upgrade with MigrationWizard.exe

Install backward compatibility

© dbi services

Conclusion

SQL Server Reporting Services

Since Reporting Services 2008

Not on IIS

uses native http.sys

Direct upgrade supported

Manually Upgrading to a SQL database using SSRS 2000 in

remote

© dbi services

dbi services

Any Questions? Please Do Ask!

32 26.04.2012 www.dbi-services.com

„We look forward to working with you!“

Antonio De Santo

Consultant

Stéphane Haby

Consultant