Database Mirroring in SQL Server 2008

In this post

-Introduction
-Database mirroring Terminology
-Database Mirroring Topology
-Facts about database mirroring
-Modes of Database Mirroring
-Mirroring States
-Example Database Mirroring

- Conclusion

Introduction

Database mirroring is a solution for increasing availability of data. Database Mirroring apply availability by at least two sides of server (SQL Server) with power of synchronous or asynchronous methodology techniques. It's let user to select one of two mode for availability (high performance or high safety).modes of availability will effects for how can failover server depending on database mirroring topology.

by the simple words, the idea of database mirroring is the way of coping data from the basic server(Principle
Server) to put it into another server (Mirror Server). if any disaster will occur for the basic server, then the second one can lead the dataflow of clients.

Database mirroring increase data protection by resolving specific kinds of errors that make corruption of reading data page (read that).
 

The advantage of the database mirroring is failures are automatically detected and failovers can be made automatic, these are not applicable for log shipping or transactional replication solutions .

Database mirroring can be used with the following in SQL Server.

-Log shipping

-Full-text catalogs

-Database snapshots

-Replication

Database mirroring Terminology

·         Principal Server : is the server that host a main database which need to increase high availability.

·         Principal Database : database selected that has access by clients.

·         Mirror Server : this server maintain a copy of data for principal database into second database.

·         Mirror Database : database that is created in the mirror server to save all transaction that occurred in the principal database.

·         Witness Server : this server monitor principal and mirror server, it's optional.

·         Endpoint : connectivity object that communicate server together across network protocol .

Database Mirroring Topology

The simplest topology can be drawn is two server one of them is principal server and the second named mirror server. each of these servers install SQL Server. the principal server has a database that clients will connect to, and mirror server has a copy of principal server database into another database called mirror database.

Each of these server communicate together by endpoint object that created in both servers. the principal server is only online database that can accessed by clients and mirror server has many states according with conditions that occurs between two servers but no way to read the data in the mirror server except the snapshot database that can be taken from mirror database.

The another topology, is add witness server between principal and mirror server . witness server can monitor activity between other servers and can make automatic failover if any disasters occurs for principal server.

The previous topology can't control disaster by automatic failover, the administrator will do his activity to solve that (manual failover)

Facts about database mirroring

1- Database mirroring supported with full Recovery mode only . Simple and bulk-logged recovery model not supported.

2- All database compatibility level can be working with Database mirroring .

3- Database mirroring need to be same name in both of two sides servers.

4- Can't use Detach with database mirroring .

5- Administrator must check for allowance of Ports that is used in Endpoint objects of database mirroring .

6- Insure that account specified in database mirroring configuration is right.
7- No mirroring with streaming configuration database, it must be disabled from database level.

Modes of Database Mirroring

·         Synchronous Mirroring (High Safety)

o   Transaction can't commit on principal server until all transaction log records have been successfully copied to the mirror.

o   High Safety can be

§  With automatic failover

Require a Witness Server

§  Without automatic failover

No Witness Server Found

·         Asynchronous Mirroring (High Performance)

o   Commit changes at the principal server and transfer them to mirror server .

Mirroring States

After the configuration of mirroring between two servers  and starting the mirror action between them , each server has a specific state which describe the communication state, data flow and also differences of data between servers.

in the simplest topology of database mirroring we can draw data flow between two server which named (principal server and mirror server). each of them have a SQL instance that has a monitor action against each other using mirroring state of each database in them.

if we enlarge our topology, then we can set third server between principal and mirror server, it's name is witness server. in this case each of other servers monitor witness using connection state (connected/disconnected)

the following is the list of mirroring states in SQL Server.

SYNCHRONIZING state

·         In this state, there are differences between contents of principal server and mirror server.

·         principal server send log records to mirror server and then mirror server send that to mirror DB to apply it.

·         starting of DB mirror session the principal DB is in the SYNCHRONIZING state and the mirror DB try to catch up that .

SYNCHRONIZED state

·         In the case of SYNCHRONIZING state if mirror DB reach to completely caught up all that is sent  from principal DB then the state will change to SYNCHRONIZED state.

·         SYNCHRONIZED state mean principal server continues to send changes to  mirror server and the mirror server continues applying changes to mirror DB. 

·         if transaction safety set to full, then automatic and manual failover are supported and no data loss after failover.

·         if transaction safety set to off, then some data will be loss.

SUSPENDED state

·         this state mean mirror DB is not available .

·         principal server is running without sending and log records to mirror server.

·         this state occur after failover or administrator pauses the session.

·         this state will work until mirror server will be start up again.

PENDING_FAILOVER state

·         this state found only on the principal server after failover has begun but the server has not transitioned into the mirror role.

DISCONNECTED

·         principal server has lost communication with mirrored server.

Example Database Mirroring

Assume that database 'northwind' in server 'Server08' need to be mirrored on server 'Server08\INST2' .

then consider the folwoing facts :

- Principal Server : 'Server08'

- Principal Database : 'northwind'

- Mirror Server : 'Server08\INST2'

- Mirror Database : Must be named 'northwind'

To create Database Mirroring, It's need to create Full backup from Principal Database but Before doing backup action ensure that your database in Recovery Model Full .

Note : Don't conflict between the need of make a Full Backup and Full Recovery model option on database.

The screen below show how to create backup action.

After that, goto the Mirrror Server 'Server08\INST2'. Right click on Databases folder and select Restore Database .

Now, the Mirror Database on Mirror Server will look like this.

Return back to Principal Server, in the Database Properties window of the Principal Database. select Mirroring tab option in the left pane .

Click on Configure Security Button, the following wizand will appear .

This step ask about Witness Server, which is not found in current example therefore select No option.

the Princopal Server instance is selected by default 'server08' because of the example begin the Database Mirroring configuration from this instance.

In this step, the configuration will create Endpoint object for Principal Server with the following information :

- Endpoint Name : Mirroring

- Listener Port : 5022 

This endpoint let the Mirror Server to allow access to Princopal Server.

In the Mirror Server Instance step, Click on Connect button to select the second server that wanted to be Mirror Server. This step will create Endpoint object for Mirror Server with name Mirroring and Listener port 5023.

If two server (Principal and Mirror) in the same doamin, specify the service account for each server.

Press Finish button to start the configuration process.

Press Start Mirroring

Because of the Principal and Mirror servers in the same machine (there are two instance in the same windows server), it's not important to write servers nework addresses as a fully qualified name. but if the two server in separate servers, it's high recommended to specify every TCP/IP for servers as Fully qualified TCP address.

in the corrent situations press Yes

The Configuration of Database Mirroring finished now, in the Screen below note that both the Principal and  Mirror endpoint are specified now by TCP://ServerName:Port_Number template .

Also note that in the section of Operating mode, there are two mode only available forg mirroring. the High safety with automatic failover is the only option not supported in the current situations, because of it's required a Witness Server which is not configured.

The results of Database Mirroring configuration is shown below.

the principal server

the mirror server

End point for Principal and Mirror server

Finally, if need now to Failover the Principal Server then, any of the following scenario should be met.

scenario 1

The Principal and Mirror server havn't any disaster (there are in good state), but the administrator in specific time need to failover the Principal because of any issue related with troubleshooting and performance for current Principal Server .

In this case, goto the Database Properties for Principal Database and from Mirroring Tab in the left pane Click on the Failover button .

The failover by this way will switch the principal to mirror server and mirror to Principal one .

scenario 2

If the administrator need to swich the Principal Database to new Principal Server, then from Mirroring tab in the Database Properties windows click on Remove Mirroring button and try to prepare full backup from Principal Database to restore it into the new Principal Server and then make a new Mirroring with Mirror Server.

scenario 3

The Principal Server has a risk or disaster, try to make a full backup and transactional log backup for Principal database and restore it to Mirror Database (Restore with Recovery) .

This way will swith Mirror Database to online database. after that think about any new strategy mirorring for this online database.



Conclusion

For more details please checkout the following links,

Database Mirroring

http://msdn.microsoft.com/en-us/library/bb934127.aspx

Database Mirroring Best Practices and Performance Considerations

http://technet.microsoft.com/en-us/library/cc917681.aspx

Data Collection SQL Server 2008

Introduction

Disk usage, Query Statistics and Server Activity are most important information for helping administrators to identify SQL Server performance problems. SQL Server 2008 has a feature of Data Collection that can gather information about Disk usage, Query Statistics and Server Activity and take a chance for view these result by set of specific reports.

Actually, Data Collection creates integration services package that is used to gather data and then put it to specific database (database whcih created by data collection configuration.) this database sometimes called Managment Data Warehouse (MDW).
Data Collection also create a set of jobs for gathering required information and add new tables also in MSDB database to save logging and other configuration information.

Benefits of Data Collection

Central Data Repository : One database store all information related with performance.

Selected data that needed to collect : It can select specific collection set to collect and upload to database warehouse.

Display Performance Reports : View specific report for speciafic field of performance (Disk usage, Query Statistics and Server Activity)
 
Configuration Data Collection

Before starting configuration of data collection, Sart SQL Server Agent service. then goto Managment folder in SQL Server instance and then right click on Data collection icon .

from the list of Data Collection select Configure Managment Data Warehouse.

Click the Next button

before working with Data Collection, it must be configured first by choosing the first option.

This form need the database that will store Data Collection information (MDW), select one of listed database or create new one.

Click New Button to create new database (if needed)

What is the logins that need to maped in MDW database that is specified in prevous step . select logins from the first grid and map it to suitable database role membership in the second grid.

Now the configuration of Data Collection Successfully finished .

Set Up Data Collection

It is now the time of set up Data Collection to enable it work . To do that return again to Data Collection icon in the Managment folder in you instance server. Right click on it select Configure Managment Data Warehouse .

in the Select Configuration Task form select the second option to set up Data collection .

Specify the previous database created for Data Collection (MDW database). the collection data by default cached to Temp directory before upload it to MDW database. if needed to change temp directory, type the alternative path in Cache direcroty field that shown below.

after review all steps click Finish to start set up Data Collection.

Now Data Collection ready to Collect and upload result to created MDW database that let to administrator to review result by reports of Data Collection .

The results of set up data collection .

the set up od Data Collection create three Collection Sets named (Disk usage, Query Statistics and Server Activity) each of them collect and upload specific information in MDW database .

Note that all collection sets start by default, it can be stoped by right click on specific collection set and then select Stop data Collection Set option.

It can specify also certain data collection set to do it's collection and upload job by option of Collect and Upload Now .

Also note the creation of the following jobs in SQL Server Agent. Each of collection set has specific jobs for collect and upload data.

Reports of Data Collection

Right Click on Data Collection, Select Report > Managment Data Warehouse. then the list of report will show three kinds of report (Disk usage, Query Statistics, Server Activity)

Disk usage

Query Statistics

Server Activity

Using Transparent Data Encryption Feature of SQL Server 2008

The following example describe how to protect you database backup by using Transparent Data Encryption

-- create database
create database mydb1
go
use mydb1
go

-- create table mytable in database mydb1
create table mytable
(
id int primary key,
nam char(10)
)

-- insert record to table
insert into mytable values (1,'Mohamed')


-- make a backup for database mydb1
backup database mydb1 to disk ='c:\myback.bak'

goto file myback.bak that is found in 'c:\', open it by Notepad program and try to search for a keyword 'Mohamed'. note that it's can clearly see data into backup file.

to protect data in backup, do the following steps.

-- create master key for your instanceUSE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'


-- create certificate object for your instance
CREATE CERTIFICATE mycert
WITH SUBJECT = 'My Certificate for database'


-- in specific database 'mydb1' create encryption key that associated with
-- certificate object of server that created in previous block.
USE mydb1
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE mycert
GO


-- open feature of encryption in database scope
ALTER DATABASE mydb1
SET ENCRYPTION ON
-- try to make backup again
backup database mydb1 to disk ='c:\myback2.bak'

goto again to 'c:\' try to open file 'myback2.bak' by Notepad program, note that all content of backup encrypted now.

now if need to restore this encrypted backup into another database in diffirence instance, the following error will be occurs.

To restore this backup without any error, wite the following code in the first server (the server which hold the main database)

USE master
GO
BACKUP CERTIFICATE mycert
TO FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' )
Go

The above code create backup for the exist server certificate to file named 'mydb_file.cer' and backup key of database also (private key) in file 'mydb_key.pvk'

Now go to another server (which want to restore encypted backup) and write the following code.

-- creating master key for another server instance
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EnKrYpt3d_P@$$w0rd'
Go

-- create certificate object for this server instance, this certificate
-- restored from file 'pvk', 'cer'
CREATE CERTIFICATE Inst2Cert
FROM FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk',
DECRYPTION BY PASSWORD = 'mY_P@$$w0rd');
GO

The data retored successfuly now in the second server.


** Some Verification for SQL object

* Verify Master key on the server

USE master
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

* Verify Certifications for master key

SELECT * FROM sys.certificates

* Verify database encryption states

SELECT
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO

and also

SELECT
NAME AS DatabaseName
,IS_ENCRYPTED AS IsEncrypted
FROM sys.databases
GO

SQL Server 2008 Implementing Log Shipping

In this post

- Introduction
- Process of Log Shipping
- Log Shipping topology
- Log Shipping Prerequisites
- Step by step example
- How to Failing Over
- Conclusion
- Resources

Introduction

SQL Server provide a way to backups automation that let to others servers to maintain these backups in the scale out of the main server. it's Log Shipping ! . Log Shipping is a way to  make high availability to solutions by making backups from the main server and restore it to one or more other servers . administrator can recover the main SQL server database (for any fail) from other servers by restore the database from one of other server to main server.

Process of Log Shipping

Log Shipping focus on transaction log file of the database . Log Shipping make a backup for that type of file only . therefore the recovery mode for the main database must be in Full or Bullk-logged option.

the following is the main processes of log shipping work .
1- Backup transaction log file of the main database to a folder founded in the main server.
2- Coping the transaction log backup from main sql server to one or more other sql server
3- Restore the transaction log backup that is exists in others sql servers.

Log Shipping topology

Log shipping consists of the following servers.

- Primary Database Server : that contain the primary database the need to maintain high availability.
- Secondary Database Server : in this server the second database that save the transaction log data from the primary database server
- Monitor Server : track the process of log shipping process in other both servers (primary, secondary).

Log Shipping Prerequisites

1- Primary database must to be a Full or Bulk-Logged recovery model.
2- Configurations need high permissions on SQL server and SQL Server Agent Service also.
3- at least two Database Servers or two SQL Server instances.
4- Folder for copying the transaction logs must be Shared and has a suitable permissions access.
 
Step by step example

1- First, SQL Server Agent Service for both server (Primary, Secondary) must enabled (Started Service)

2-Right Click on primary database for primary server. and then select Properties. from the left pane select Transaction Log Shipping .

3- click on checkbox Enable this as a primary database in a log shipping configuration to enable log shipping in this database. and then click on Backup Settings button.

4- in Backup Settings window, set network path for backup folder . this path let sql server in primary server to save backup result on it. and also you can set path in local machine (in the second textbox) if backup result will be set on the primary server not in nework path.

it's mandatory to put network path to can leave this step. Consider also that network path must be a shared folder with suitable permission to let log shipping done task job well.

after that click on Schedule Button to plan the time of backup job implementation.

5- Returning again to Database Properties window to setting up the first secondary SQL Server. in the intermediate part of window ckick on Add Button .

Press Connect Button, and select your second SQL Server.

after selecting the second server, select the first option in the Initialize Secondary Database tab. this option mean that log shipping will generate a full backup of the primary database and restore it into the secondary database . log shipping will create the secondary database if it doesn't exist.

Now, goto second tab CopyFiles to set path folder for coping backup files after backup job finish. this coping process need to automate . for automate that  press the Schedule button to set appropriate time job .

in the last tab Restore transaction log select the second option Standby mode . the Standby mode mean that second database in the second server will be read only database. it's suitable situation if need working with second database by select query only, it's let customers to connect to this database for reading but it's reading only.

there is second option No recovery mode that is mean the second database will not be available for any one , it's just working for log shipping processes.

select on Schedule button to set appropriate time job for recovering.

after finish three tab options press ok to return Database Properties window for primary database. the secondary database will appear like this.

press Add button to add another SQLServer that will act as others secondary database servers.

6- in the last part of Database Properties window, optionally click on use a monitor server instance checkbox option to determine a sql server that act Monitor Server.

7- in Database Properties window click ok, to take processes of log shipping work.

8- in the stage the log shipping work well . to check the effects of log shipping check the added jobs in the both SQL agent service for two servers.

Note that because of Standby mode that selected for second database server, the second database server will act a read only database as show below.

checkup also the result file in both paths that sets in the log shipping configuration .

How to Failing Over

1- After failing primary server, it's may be to find backup files in the backup folder not transfered to destination folder. copy all that files that is not transfered to destination folder(is the secondary server).

2- Restore all tranaction log backup files in the destination folder sequencelly (identify that by date of files) to secondary sql server.

3- If it's possible to access primary database server take a (Tail log) backup (BACKUP LOG-WITH NO_RECOVERY) this leaves the database in the inconsistent state (restoring state) . and then restore (tail log) backup to secondary SQL server with a (RESTORE LOG-WITH RECOVERY)

after these steps the primary database in unavailable and second database is online now, it's time for administrator now to redirect users to second server.
 
Conclusion

Log shipping is a way from ways that can achive a level of high availability, design a suitable topology with right configuration with prerequisites to have a higher power benefits of log shipping.

Resources

Failing Over to a Log Shipping Secondary
http://msdn.microsoft.com/en-us/library/ms191233.aspx

SQL Server 2008 Log Shipping
http://blogs.technet.com/b/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx

An Overview of Log Shipping in SQL Server 2005
http://www.codeproject.com/KB/database/LogShipping.aspx