Upgrading to Microsoft SQL Server 2014 and to Microsoft Azu - PowerPoint Presentation

Slide1
Slide2

Upgrading to Microsoft SQL Server 2014 and to Microsoft Azure

Mike SheltonRoss Loforte

BRK2577Slide3

Preparing to UpgradeSlide4

Preparing to

Upgrade

Research

current environment

-

MAP tool

Define

a current environment baseline

-

Run Performance Monitor

-

Management Data Warehouse

Choose

target environment

Choose

an edition of SQL Server 2014

-

Allowed paths by

Editions

Migration to

Azure Slide5

Demo

Microsoft Assessment and Planning Toolkit

Mike SheltonSlide6

Choose Upgrade Tools

SQL Server 2014 Upgrade Advisor

Analyzes SQL no longer valid on SQL Server 2014

Generates a report

Requirements

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 Slide7

Demo

SQL Server 2014 Upgrade Advisor

Mike SheltonSlide8

Other Upgrade Tools

Best Practices Analyzer for SQL Server

2005/2008 R2/2012

Gathers information

about

Microsoft SQL

Server

Determines if the configurations are set according to the recommended best practices

Reports on all configurations, indicating settings that differ from recommendations

Indicates potential problems and Recommends solutionsdownload from: www.microsoft.com/download

SQL Server Profiler

Create

and manage SQL traces

Replay SQL trace

S

aved

in a trace file

or table, can later be analyzed System MonitorSQL Server: Deprecated Features Object SQL Server 2014 SetupSystem Configuration Checker For installation rules Slide9

Demo

Best Practices Analyzer for SQL Server

Mike SheltonSlide10

Developing an Upgrade PlanSlide11

Upgrade Strategies

In-Place upgradeRestrictions:

All SQL

Server components be upgraded

together

F

rom

a 32-bit instance

to

a 64-bit instance not supported Complex restore policySQL Setup steps to upgrade:Installs prerequisitesChecks for upgrade blocking issues If pending restart exists, restart Installs SQL Server 2014 executables and support filesStops the legacy SQL Server service, no longer available

Updates the selected component data and objects

Removes

the legacy executables and support files

SQL

Server 2005/2008/2008

R2/2012

client tools

not removed Database compatibility level not changed

SQL Server 2005/8/R2

Instance

SQL Server

2014

InstanceSlide12

Setup Requirements for an In-Place Upgrade

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 2014Not supported: 32-bit to 64-bit (x86 to x64) versions and vice versa

Supported version of the Windows operating

systemSlide13

Upgrade Strategies

Side-by-Side upgrade

Manual intervention

Data files

Database objects

Configuration

settings

Security settings

SQL Server Agent jobs

SQL Server Integration Services

(

SSIS) packages

Need

s

erver and storage capacity

Application name changes

Simpler backup strategy

Compare

and

Verify

SQL Server

2005/8/R2/2012

Instance

SQL Server

2014

Instance

Verified!

Moving the database

Backup/Restore Upgrade Method

Detach/Attach Upgrade Method

Copy Database Wizard Upgrade Method

Log ShippingSlide14

Upgrade options and

considerations

In-PlaceSlide15

Upgrade options and

considerations

Side-by-side (migrate)Slide16

Software Requirements for SQL Server 2014

Setup will install .NET 4.0, the SQL Server Native Client, and support files

.NET requirements:

SQL Server 2014 on a Windows Server 2008 SP2 server, install .NET Framework 3.5

On Windows 7 and Windows Server 2008 R2 SP1, enable .NET Framework 3.5

Windows PowerShell 2.0 is required

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 supportedSlide17

Other Upgrade Considerations

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-sp1-cumulative-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

More info: https://technet.microsoft.com/en-us/library/ms143393.aspxSlide18

Other Upgrade Scenarios

Upgrading Both Windows and SQL Server

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

Upgrading

Multiple

SQL InstancesShared components will be upgradedBackup plan, in case the windows server is inoperativeBackup all databasesSlide19

Plan for Backups

Before upgrade, make

a backup of the user databases and

data

U

pgrade complete

,

perform

backups

After configuration changes, take full database backupsAfter user acceptance and validation, take full database backupsSlide20

Minimizing Upgrade Downtime

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 componentsSelect the optimal side-by-side upgrade strategyUse new service accountsCheck data

consistency

Back up data before and after the upgradeSlide21

Upgrading Failover Cluster Instances

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 upgradeSlide22

Upgrading AlwaysOn Availability Groups

During the upgrade, only back up from the primary replica

Prevent unintended failovers, turn off automatic failover to synchronous-commit replicas

Upgrade remote secondary replicas first

Upgrade local secondary replicas next

Failover the availability group to an upgraded secondary synchronous-commit replica server

If failover to an asynchronous-commit secondary replica, data loss may occur

Upgrade the primary replica Slide23

Upgrade Assistant for SQL Server 2014 (UAFS)

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/20122Capture a PlaybackBaselineSQL 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 2014Slide24

Post Upgrade ConsiderationsSlide25

Post Upgrade Considerations

Checklist

Success Criteria

Jobs

Maintenance Plans

DBCC

Update Statistics

Client strings connections

HA/DR testing

Performance validationSlide26

Moving

SQL Server to AzureSlide27

Moving SQL Server Databases to Azure

W

izard

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

/Slide28

Demo

Moving to Azure SQL DatabaseRoss LoforteSlide29

Backup to

Azure

Backup to Azure

Managed Backup

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 configurationManage at database level or instance levelSlide30

Resources

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

http://www.microsoft.com/en-us/server-cloud/products/sql-server/Resources.aspx

SQL

Database Migration Wizard

http://sqlazuremw.codeplex.com/

Slide31

Visit

Myignite

at

http://myignite.microsoft.com

or download and use the

Ignite

Mobile

App

with

the QR code above.

Please evaluate this session

Your feedback is important to us!Slide32