Transcript Upgrading to Microsoft SQL Server 2014 and to Microsoft Azure

•
•
•
•
Microsoft Assessment and Planning Toolkit
•
•
Analyzes SQL no longer valid on SQL Server 2014
Generates a report
•
Windows Vista SP1, Windows 7, or Window Server 2008
R2/2012/2012 R2
The Microsoft .NET Framework 4 (the same version of the .NET
Framework included with SQL Server 2014 and Visual Studio 2010)
Windows Installer 4.5
Processor speed of at least 500 MHz
15 MB of available hard disk space
Supports SQL Server SQL Server 2005, 2008, 2008 R2, and 2012
Download version is available as part of the Microsoft SQL Server
2014 Feature Pack
•
•
•
•
•
•
 Best Practices Analyzer for SQL Server 2005/2008 R2/2012
•
•
•
•
www.microsoft.com/download
 SQL Server Profiler
•
•
•
Create and manage SQL traces
Replay SQL trace
Saved in a trace file or table, can later be analyzed
 System Monitor
•
SQL Server: Deprecated Features Object
 SQL Server 2014 Setup
•
System Configuration Checker For installation rules
Best Practices Analyzer for SQL Server
SQLSQL
Server
Server
2005/8/R2
2014
Instance
•
•
•
1.
2.
3.
4.
5.
6.
7.
•
•
SQL Server 2005: SP4 is required
SQL Server 2008: SP2 is required
SQL Server 2008 R2: SP1 is required
SQL Server 2012: SP1 is required
•
•
•
Sufficient disk space must be available for SQL Server 2014
Not supported: 32-bit to 64-bit (x86 to x64) versions and vice versa
Supported version of the Windows operating system
SQL Server 2005/8/R2/2012
Instance
•
•
•
•
•
•
•
SQL Server 2014
Instance
Compare
and
Verify
Verified!
•
•
•
Moving the database
•
•
•
•
In-Place
Pros
•
•
•
•
•
Easier, mostly automated
Generally fast overall process
System data upgraded
May require no additional hardware
Applications remain pointing to same
server/database name
Cons
• Less granular control over upgrade process – all
or nothing
• Instance remains offline during part of upgrade
• Not best practice for all components
• Complex rollback strategy
Side-by-side (migrate)
Pros
• More granular control over upgrade process
• Database level
• Can be used to perform test migration
• Document process & gather metrics
• Ability to run systems side-by-side for parallel runs
• May require 3rd party application to keep both in-sync
• Relatively straightforward rollback strategy
• Can leverage failover/switchover to reduce downtime
• Upgrade over time
Cons
• Usually require additional hardware
• Additional resources required if on same server
• Server/database name changes
• Not practical for VLDB unless utilizing SAN (hybrid
approach)
• Beware of “loss of quick roll-back”


•
•


•
•
•
•
Application and Connection Requirements
SQL Server native Client 11.0
.NET Framework 3.5 SP1 must be installed
.NET Framework 3.5 SP1 installed by SQL 2014
DB-Library client tools no longer supported
Product Updates (Slipstreaming) in SQL Server 2014 setup,
more info: http://blogs.msdn.com/b/ai/archive/2013/01/14/installing-sql-server-2012-service-pack-1-sp1cumulative-update-in-one-installation-using-sql-server-2012-product-update.aspx
Upgrade from an Evaluation edition of a previous SQL Server
version is not supported
Allowed Upgrade Paths
https://technet.microsoft.com/en-us/library/ms143393.aspx
•
•
•
•
•
•
SQL Server 2014 supports Windows Server 2008 R2 SP1 and Windows Server 2012 and
2012 R2 Server Core
If on Windows Server 2003 or Windows Server 2008, an upgrade of Windows is required
Before Windows operating system upgrade, install the SQL Service Pack supported by
the upgraded operating system
Shared components will be upgraded
Backup plan, in case the windows server is inoperative
Backup all databases
Before upgrade, make a backup of the user
databases and data
2. Upgrade complete, perform backups
3. After configuration changes, take full
database backups
4. After user acceptance and validation, take
full database backups
1.
•
•
•
•
•
•
•
•
•
Check the legacy SQL Server versions
Make sure that installation requirements are met
Preinstall .NET and Windows components
Preinstall Visual Studio 2008 SP1 or a later version
Preinstall SQL Server 2014 common components
Select the optimal side-by-side upgrade strategy
Use new service accounts
Check data consistency
Back up data before and after the upgrade
• Upgrading each node, one at a time
• Hardware and software upgrade, do a side-by-side
• Upgrade passive cluster nodes first
•
•
•
Upgrade at least half of the passive cluster nodes
Stop all traffic for the SQL Server instance, to ensure a consistent state
Brief outage is required to upgrade
Upgrade Assistant for SQL Server 2014 (UAFS) helps DBA to assess application
compatibility and performance difference to upgrade SQL Server from old version to the
latest version, download: http://www.scalabilityexperts.com/tools/downloads.html
Step
Task
Computer
SQL Server Version
1
Create a Test Environment
Baseline
SQL Server 2005/2008/2008R2/2012
2
Capture a Playback
Baseline
SQL Server 2005/2008/2008R2/2012
3
Setup Playback Baseline System
Baseline
SQL Server 2005/2008/2008R2/2012
4
Run Upgrade Advisor
Baseline
SQL Server 2005/2008/2008R2/2012
5
Replay Trace on SQL Server
2005/2008/2008R2/2012
Baseline
SQL Server 2005/2008/2008R2/2012
6
Setup Playback Test System
Baseline to Test
SQL Server 2005/2008/2008R2/2012
7
Upgrade to SQL Server 2014
Test
Upgrade to SQL Server 2014
8
Run Test Trace Playback
Test
SQL Server 2014
9
Compare and Analyze
Test
SQL Server 2014
•
•
•
•
•
•
•
•
•
• Wizard to deploy DB to SQL Server in Azure
VM
• Wizard to deploy DB to Azure SQL
Database
SQL Database Migration Wizard
http://sqlazuremw.codeplex.com/
• DB on-premise Backup to Azure
• SQL Server Backup to URL
•
•
•
•
•
Automated SQL Server Backup Policy
DB on-premise Auto backup to Azure
Agent decides when do backups
Simple configuration
Manage at database level or instance level
SQL Server 2014 Upgrade Guide
http://download.microsoft.com/download/7/1/5/715BDFA7-51B6-4D7B-AF17-61E78C7E538F/SQL_Server_2014_Upgrade_technical_guide.pdf
Microsoft Virtual Academy
http://www.microsoftvirtualacademy.com/
SQL Server 2014 Resources page
SQL Database Migration Wizard
http://sqlazuremw.codeplex.com/
http://myignite.microsoft.com