Search This Blog
Showing posts with label High Availability. Show all posts
Showing posts with label High Availability. Show all posts

Sql Server 2008 new features Compression, FILESTREAM and TDE compatibility with with all High Availability (HA) options

Some of the new features in Sql Server 2008 are very useful (and long over due). Features like
  • Compression
  • FileStream
  • TDE (Transparent Data Encryption).
Though they have their own limitations, they are very useful when available natively.
Here we’ll look into their compatibility with existing High Availability (HA) features.
  • FailOver Clustering
  • Database Mirroring
  • Replication
  • Log Shipping
Lets start with the matrix, and then we’ll go into details:
Compression
FileStream
TDE
FailOver Clustering
Y
Y
Y
Database Mirroring
Y
No
Y
Replication
Y
Y
Y
Log Shipping
Y
Y
Y
When you look at it this way, it makes it much easier to remember all the options. Of all, as you can see, only one stand out i.e. FileStream is not configurable in Database Mirroring. All the other configurations are fairly well supported. Please know that there are, in fact, some limitations for these features in each HA implementation (but, that is for an article some other day).
Hopefully, future versions of Sql Server will support FileStream in Database Mirroring.

Compare features in different High Availability options in SQL Server.

Since Sql Server 2005, High Availability (HA) has become very easy to implement in a Sql Server environment. Also, starting Sql 2005, there are 4 methods to implement HA.
  • Failover Clustering
  • Database Mirroring
  • Replication
  • Log Shipping
Though, Failover clustering still is the most popular option, my guess is the other options might make it a more level field going forward.
In the following matrix, I compare some features of each HA option in one table. This gives a good comparison picture of the features available in each option.
Property \ FeatureFailoverClusteringDB MirroringReplicationLog Shipping
Server LevelYesNoNoNo
Database LevelNoYesYesYes
Automatic FailoverYesYes (Only HA Option)NoNo
Automatic Failure DetectionYesYes (Only in HA option)NoNo
Hot, Warm or Cold standbyHotHot and Warm OptionsWarmWarm
Server Level Objects FailoverYes (Automatic)ManualManualManual
Is data on Failover available for queries while the Primary server is running?NoYes (use Database Snapshots)YesYes (Standby Mode)
Are changes allowed at Failover location?NoNoYes (Merge and TRN Replication with updating subscribers)No
Is Main and Failover server in a 1-1 relationship?YesYesNot RequiredNot Required
Is 1-Many relationship possible between the Main and Failover servers?NoNoYesYes
Is Many-1 relationship possible between Main and Failover server?NoNoYesNo
Is Automatic Application redirection possible after failover?YesYesNoNo
Recovery model required for the participating databases?AnyFULLANYFULL & BULK
Can you filter what data is sent to the Failover server?NoNoYesNo
Where does the logic HA reside?OS + SqlSqlReplication Agents (they run outside Sql ServerSql Agents (Run outside Sql Server)
If you want a reports server, what is the best solution?Not PossibleWorks, but needs constant refresh of the Snapshot database for latest data.Best Option.Works, but users connections need to be kicked out, when new log is applied.
Best solution if the Main and Failover server are separated by long distance?Not good.Works, but not recommended.Works, but with high latencyBest Option. (Log compression reduces latency significantly.

Setting Up Database Mirroring on Sql Server 2008 - Part 1


Following are the steps that I follow to set up DB Mirroring between Sql Server 2008 databases on separate servers.
Overview:
  1. Create Sample Database for Mirroring on Principal Server
  2. Create some database objects in the database to test i.e. tables, stored procedures, etc
  3. Create Database Mirroring EndPoints on Principal, Mirror and Witness
  4. CREATE necessary accounts and GRANT permissions.
  5. Prepare Mirroring database
  6. Enable / Set up mirroring.
  7. Create Snapshot database on Mirror database.
  8. Failover:
    1. Test Failover
    2. Force Failover
  9. Server Objects.
  10. Other points.
Server Names:
Initial Instance Role
Domain Service Account
Server Name
Principal
AD\sql1
SalesDB.AD.com
Mirror
AD\sql1
SalesDBFailOver.AD.com
Witness
AD\sqlWitness
WitnessDB.AD.com
Assumptions:
  • Principal, Mirror and Witness services run with domain accounts.
  • All users and application connections to Sql Server are also domain accounts.
Step 1: Create Sample Database for Mirroring on Principal Server
First let’s create a database on Principal server for mirroring. FULL recovery model is required for databases participating in mirroring.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-- Step 01: CREATE Principal Database.
-- Run on Principal Server
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 
CREATE DATABASE DBMir
ON
PRIMARY
(
NAME            = N'DBMir_PRIMARY_Data'
, FILENAME       = N'E:\MS SQL\MSSQL_2K8\Play1\MDF\DBMir_Primary_Data.MDF'
, SIZE           = 1024 MB
, MAXSIZE        = 2 GB
, FILEGROWTH = 10
)
LOG ON
(
NAME            = N'DBMir_Log'
, FILENAME       = N'E:\MS SQL\MSSQL_2K8\Play1\LDF\DBMir_Log.LDF'
, SIZE           = 1024 MB
, MAXSIZE        = 2048 MB
, FILEGROWTH = 10
)
GO
 
--    SET recovery model to FULL
ALTER DATABASE [DBMir]
SET RECOVERY FULL
GO
Step 02: Create some database objects and server level objects to test after failover i.e. logins, tables, stored procedures, etc.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--   sample server level object
USE MASTER
GO
 
CREATE LOGIN [test] WITH PASSWORD = 'test'
WITH  DEFAULT_DATABASE = [DBMir]
, DEFAULT_LANGUAGE = [us_english]
GO
USE [DBMir]
GO
 
CREATE USER [test] FOR LOGIN [test]
GO
 
--  Sample table
CREATE TABLE dbo.Test
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, Name   VARCHAR(40)     NULL
, Age    INT             NULL
, Blob   VARCHAR(MAX)    NULL
)
GO
 
-- Insert a few records, before setting up Mirroring.
INSERT INTO dbo.Test (Name, Age, Blob) VALUES('Name1', 10, REPLICATE(CONVERT(VARCHAR(MAX), 'ac'),10000))
GO 10
Step 03: Create Database Mirroring EndPoints on Principal, Mirror and Witness.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Create endpoint on Principal
CREATE ENDPOINT DBMirror_Principal
STATE = STARTED
AS
TCP (LISTENER_PORT = 7022) -- Do not use port 7022 on prod. server
FOR DATABASE_MIRRORING (ROLE = PARTNER)
GO
 
-- Create endpoint on Mirror
CREATE ENDPOINT DBMirror_Mirror
STATE = STARTED
AS
TCP (LISTENER_PORT = 7023)
FOR DATABASE_MIRRORING (ROLE = PARTNER)
GO
 
-- Create endpoint on Witness
CREATE ENDPOINT DBMirror_Witness
STATE = STARTED
AS
TCP (LISTENER_PORT = 7024)
FOR DATABASE_MIRRORING (ROLE = WITNESS)
GO
Step 04: CREATE necessary accounts and GRANT permissions
Run on Principal
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- CREATE LOGIN for Witness (service account) on Principal
-- and GRANT CONNECT permission on Principal's EndPoint
CREATE LOGIN [AD\sqlWitness] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Principal TO [AD\sqlWitness]
GO
 
-- CREATE LOGIN for Witness (service account) on Mirror
-- and GRANT CONNECT permission on Mirror's EndPoint
CREATE USER [AD\sqlWitness] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Mirror TO [AD\sqlWitness]
GO
 
-- CREATE LOGIN for Principal/Mirror(same service account on both) on Witness
-- and GRANT CONNECT permission on Witness's EndPoint
CREATE LOGIN [AD\sql1] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::DBMirror_Witness TO [AD\sql1]
GO
Step 05: Prepare Mirroring database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Run on Principal
BACKUP DATABASE DBMir TO DISK = N'E:\MS SQL\MSSQL_2K8\Play1\Backup\DBMir_Principal.BAK'
GO
 
BACKUP LOG DBMir TO DISK = N'E:\MS SQL\MSSQL_2K8\Play1\Backup\DBMir_Principal.TRN'
GO
 
-- Copy the database files over to Mirror Instance
 
-- Run on Mirror
RESTORE DATABASE DBMir FROM DISK = N'E:\MS SQL\MSSQL_2K8\Play2\Backup\DBMir_Principal.BAK'
WITH MOVE 'DBMir_Principal_Data' TO 'E:\MS SQL\MSSQL_2K8\Play2\MDF\DBMir_Mirror.MDF'
, MOVE 'DBMir_Log' TO  'E:\MS SQL\MSSQL_2K8\Play2\LDF\DBMir_Mirror_Log.LDF'
, NORECOVERY
, REPLACE
GO
RESTORE LOG DBMir FROM DISK = N'E:\MS SQL\MSSQL_2K8\Play2\Backup\DBMir_Principal.TRN'
WITH NORECOVERY
GO
Step 06: Enable / Set up mirroring
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Run on Mirror: Inform Mirror about Principal (first step)
ALTER DATABASE DBMir
SET PARTNER = 'TCP://SalesDB.AD.com:7022'
GO
 
-- Run on Principal: Inform Principal about Mirror
ALTER DATABASE DBMir
SET PARTNER = 'TCP://SalesDBFailOver.AD.com:7023'
GO
 
-- Run on Principal: Inform Principal about Witness
ALTER DATABASE DBMir
SET WITNESS = 'TCP://WitnessDB.AD.com:7024'
GO
Step 07: Create Snapshot database on Mirror database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Create snapshot on Mirror to run sample queries
USE MASTER
GO
CREATE DATABASE DBMir_SnapShot
ON
(
NAME        =   'DBMir_Primary_Data'
, FILENAME   =   'E:\MS SQL\MSSQL_2K8\Play2\MDF\DBMir_SnapShot2.DS'
)
AS  SNAPSHOT OF DBMir
GO
 
SELECT COUNT(*) FROM dbo.Test
GO
Step 08: Failover
If you want to test the cussecc of failover in our environment use the following FAILOVER command.
1
2
3
4
5
6
-- To test FailOver, run the following query on Principal
-- Smooth failover happens. If there are any SQL Logins,
-- To map orphan users, run sp_change_users_login on Mirror
ALTER DATABASE DBMir
SET PARTNER FAILOVER
GO
But if the Principal goes down and you want to force FAILOVER to
the mirror server, use the following command. This could result in
some data loss.
1
2
3
4
5
-- To force failover, when the Principal is unavailable, run this on Mirror
-- Pre-requisite: Principal must be unavailable
ALTER DATABASE DBMir
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO
Step 09:  Server Objects
A note on logins: Make sure logins are created in the same order on the Mirror server as on the Principal server. This way login SID for will remain the same and will not create any orphans.
Any other server level objects could be transferred using an SSIS package designed specifically for this purpose.
Step 10:  Other points
If you want to run any maintenance on one of the servers participating in Mirroring the following commands will come to good use. Lets say, you want to upgrade patches on Principal and Mirror database server.
  1. First, you want to perform a manual FAILOVER [from Principal to Mirror]
  2. Suspend DB mirroring [stop any data flow from new Principal to new Mirror]
  3. Upgrade original Principal Server
  4. Bring Principal up and running
  5. Resume DB Mirroring to bring previous Principal (now Mirror) up to date.
  6. Perfom another manual FAILOVER to the newly upgraded Mirror (original Principal).
  7. Suspend Mirroring
  8. Upgrade Mirror (original)
  9. Bring it back up.
  10. Resume DB Mirroring.
To perform all the above steps, the following commands will be useful.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- To drop DB Mirroring
ALTER DATABASE DBMir
SET PARTNER OFF      -- to DROP DB Mirroring
GO
 
-- To re-establish DB Mirroring
ALTER DATABASE DBMir
SET PARTNER ON
GO
 
-- To temporarily suspend mirrorring
ALTER DATABASE DBMir
SET PARTNER SUSPEND
GO
 
-- To resume, suspended mirroring
ALTER DATABASE DBMir
SET PARTNER RESUME
GO
Hope this helps,
Subscribe to: Posts (Atom)