Today’s script is one I have used to test one possible upgrade method from SQL Server 2008 to SQL Server 2012. If truth be told this would be my prefered upgrade method I’ll explain why…
I have database mirroring in production on SQL Server 2008 two physical servers in an active passive cluster configuration as the PRINCIPAL and the FAILOVER PARTNER is a third physical server. My plan is to create another active passive cluster with SQL Server 2012 installed and configured then break the existing mirroring partnership and setup a new mirroring partnership to the new cluster. All this work can be done without any downtime to the current environment! Once the new mirroring partnership is setup I can schedule a failover and a few seconds later I’m on SQL Server 2012 in production.
I can then rebuild the old SQL 2008 PRINCIPAL and FAILOVER PARTNER servers with SQL Server 2012 and create availability groups, Wohooo! I'm way to excited about availability groups, it opens up so many possibilities!!!
/*
-----------------------------------------------------------------
Test upgrading SQL Server 2008 to SQL Server 2012
Server1 is the PRINCIPAL and Server2 is the FAILOVER PARTNER
The test database is called DenaliHA
The test table is called HATest
You will need to specify a login to have permissions granted on
the endpoints
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- *** RUN AT THE PRINCIPAL ***
-- Create test database
USE [master]
GO
CREATE DATABASE [DenaliHA] ON PRIMARY
( NAME = N'DenaliHA_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DenaliHA_Data.mdf' , SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DenaliHA_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DenaliHA_Log.ldf' , SIZE = 1048576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [DenaliHA] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DenaliHA].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
-- Create test table
USE DenaliHA
GO
CREATE TABLE HATest (
HATestID INT IDENTITY(1,1),
Forename VARCHAR (100),
Surname VARCHAR (100)
);
-- Insert some data
INSERT INTO HATest (Forename, Surname) VALUES ('Chris','McGowan')
GO 10000
CREATE CLUSTERED INDEX [IDX_HATest:Composite1] ON HATest (HATestID);
GO
-- Backup database and transaction log
BACKUP DATABASE DenaliHA TO DISK = 'C:\DenaliHA\DenaliHA.bak';
GO
BACKUP LOG DenaliHA TO DISK = 'C:\DenaliHA\DenaliHA.trn';
GO
-- Create endpoint
USE master
GO
IF NOT EXISTS ( SELECT *
FROM sys.endpoints
WHERE name = 'DenaliHADatabaseMirroringEndpoint' )
CREATE ENDPOINT [DenaliHADatabaseMirroringEndpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 1430, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM AES);
GO
-- Grant permissions on endpoint
IF EXISTS ( SELECT name
FROM sys.server_principals
WHERE name = '' ) -- Must add Login Name
GRANT CONNECT ON ENDPOINT::DenaliHADatabaseMirroringEndpoint TO [Login Name Here]; -- Must add Login Name
GO
-- *** RUN AT THE FAILOVER PARTNER ***
USE master
GO
-- Create endpoint
IF NOT EXISTS ( SELECT *
FROM sys.endpoints
WHERE type_desc = 'DATABASE_MIRRORING' )
CREATE ENDPOINT [DenaliHADatabaseMirroringEndpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 1440, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM AES);
GO
-- Grant permissions on endpoint
IF EXISTS ( SELECT name
FROM sys.server_principals
WHERE name = '' ) -- Must add Login Name
GRANT CONNECT ON ENDPOINT::DenaliHADatabaseMirroringEndpoint TO [Login Name Here]; -- Must add Login Name
GO
-- Copy backup files from server1
-- Get file locations for the restore
USE DenaliHA
GO
sp_helpfile
GO
-- Restore backups
USE master
GO
RESTORE DATABASE DenaliHA FROM DISK = 'C:\DenaliHA\DenaliHA.bak' WITH REPLACE, MOVE 'DenaliHA_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Data.mdf', MOVE 'DenaliHA_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Log.ldf', NORECOVERY;
GO
RESTORE LOG DenaliHA FROM DISK = 'C:\DenaliHA\DenaliHA.trn' WITH REPLACE, MOVE 'DenaliHA_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Data.mdf', MOVE 'DenaliHA_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Log.ldf', NORECOVERY;
GO
-- Enable database for mirroring
ALTER DATABASE DenaliHA SET PARTNER = 'TCP://Server1.GPGROUP.COM:1430';
-- *** RUN AT THE PRINCIPAL ***
-- Enable database for mirroring
ALTER DATABASE DenaliHA SET PARTNER = 'TCP://Server2.GPGROUP.COM:1440';
-- Insert some more data to prove the database mirroring session is working
USE DenaliHA
GO
INSERT INTO HATest (Forename, Surname) VALUES ('Chris2','McGowan2');
GO 10000
-- Failover!!!
ALTER DATABASE DenaliHA SET PARTNER FAILOVER;
/*
It is at this point where the database will be online on the SQL 2012 instance
NOTE - Databasebase Mirroring will be suspsended and errors like the below will be received;
'TCP://Server1.GPGROUP.COM:1430', the remote mirroring partner for database 'DenaliHA', encountered error 948, status 2, severity 20. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.
Error: 1453, Severity: 16, State: 1.
This is beacuse Database Mirroring works from SQL 2008 to SQL 2012 for upgrades only! Mirroring SQL 2012 to SQL 2008 will not work!!!
*/
-- *** RUN AT THE PRINCIPAL ***
-- Remove mirroring
ALTER DATABASE DenaliHA SET PARTNER OFF;
-- *** RUN AT THE FAILOVER PARTNER ***
-- Bring original database online
RESTORE DATABASE DenaliHA WITH RECOVERY;
-- Drop Databases
DROP DATABASE DenaliHA;
GO
-- *** RUN AT THE PRINCIPAL ***
DROP DATABASE DenaliHA;
GO
Enjoy!
Chris
