Database Mirroring in SQL Server 2008
来源:互联网 发布:javascript 第六版pdf 编辑:程序博客网 时间:2024/04/30 18:24
What is Database Mirroring?
Database mirroring is the feature in SQL Server 2005 and SQL Server2008 that provides a high availability solution for Databases. This feature canbe enabled and used only on a database with Full recovery models. The databasecan be mirrored from one SQL Server instance to another SQL Server instance.The source instance is called Principal server; the target instance is calledMirrored server. We could have one more server called Witness server--we willtalk about that in later part of this article series.
How does database mirroring work?
The principle server sends the active transaction log record to themirrored server. The mirrored server applies the transaction log record one byone in sequence.
Modes of Database Mirroring
Database mirroring can be configured in two different modes, High-Safetymode also known as synchronous mode and High-Performance mode also known as asynchronously.The term synchronous and asynchronous says it all.
In the synchronous mode, the principal server sends the transactionand waits until the transaction is committed on the mirrored server. Then thetransaction is committed on the principal server.
In Asynchronous mode, the principal server sends the transaction tothe mirrored server and does not wait for the transaction on the mirroredserver to commit.
We will discuss transaction safety in detail in a future installmentof this series.
Now let's setup database mirroring between the SQL Server instancePowerPC\SQL2008 [our principal server] and PowerPC\SQL2k8 [our mirroredserver].
What are the Pre-Requisites of database mirroring?
The following are the pre-requisites for database mirroring.
- Editionof SQL Server should be Standard, Enterprise or Developeredition
- PrincipalDatabase involved in database mirroring should be in full recovery mode
- Beforeconfiguring database mirroring, take a full backup, transactional log backup onthe principal server and restored it on the mirrored server with NORECOVERYoption.
Now let's create a database DB1 on the principal server,PowerPC\SQL2008, using the following transact SQL statement. In this part of articleseries, we are going to discuss database mirroring with synchronous mode andwith no witness server.
USE [master]GO/****** Object: Database [DB1] Script Date: 06/20/2009 21:10:33 ******/IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')DROP DATABASE [DB1]GOUSE [master]GO/****** Object: Database [DB1] Script Date: 06/20/2009 21:10:13 ******/CREATE DATABASE [DB1] ON PRIMARY ( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1.mdf' , \SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO
Now let's create a database DB1 on the mirrored server, PowerPC\SQL2K8,using the following transact SQL statement.
USE [master]GO/****** Object: Database [DB1] Script Date: 06/20/2009 21:10:33 ******/IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')DROP DATABASE [DB1]GOUSE [master]GO/****** Object: Database [DB1] Script Date: 06/20/2009 21:10:13 ******/CREATE DATABASE [DB1] ON PRIMARY ( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf' , SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GO
If the target server does not have the database with same name, youwill get the following error when configuring database mirroring. [Refer Fig1.0]
Fig 1.0
Note: Instead of creating the DB1 database on themirrored server, you could restore the database backup and tranlog backup usingthewith replace option to create and restore at the same time.
Now let's backup the database and transaction on the principal serverusing the following transact SQL statement.
use mastergoBackup database DB1 to disk ='C:\Backups\DB1.Bak' with initgoBackup log DB1 to disk ='C:\Backups\DB1.trn' with initgo
Restore the database on the target server using the following transactSQL statement.
use mastergorestore database DB1 from disk ='C:\Backups\DB1.Bak' with norecovery, replace,move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf',move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.ldf'gorestore log DB1 from disk ='C:\Backups\DB1.trn' with norecovery, replace,move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf',move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.ldf'go
On the target server, if the database is not in restore mode you willget the following error. [Refer Fig 1.2]
Fig 1.2
Configure the database DB1 on the principal server for databasemirroring. Using SQL Server management studio, expand the databases and clickon the Database DB1. Right click on the database DB1 and select properties. Inthe properties window select the "Mirroring" option as shown below. [ReferFig 1.3]
Fig 1.3
Now click on the "Configure Security" button and you willsee the following screen. Since we are not going to setup the witness server,select the option "No" and click next. [Refer Fig 1.4]
Fig 1.4
Select the default port and the endpoint name chosen by the SQL servermanagement studio and click Next. [Refer Fig 1.5] If you are choosing someother port, then make sure that port is open and available.
Fig 1.5
Now select the mirrored server name, click on the "Connect"button and make sure you can connect to the mirrored server. Select the defaultport and the endpoint name chosen by the SQL server management studio and clickNext. [Refer Fig 1.6] If you are choosing some other port, then make sure thatport is open and available.
Fig 1.6
Type the appropriate service account you want to use for the databasemirroring. [Refer Fig 1.7]
Fig 1.7
Double check the summary details and click finish. This will configuredatabase mirroring. [Refer Fig 1.8, 1.9, 1.10]
Fig 1.8
\
Fig 1.9
\
Fig 1.10
On the next screen, click on the button "Start Mirroring". [ReferFig 1.11]
Fig 1.11
On the next screen, click on the "Yes" button. [Refer Fig1.12]
\
Fig 1.12
The following screen shows that database mirroring is configured andrunning. [Refer Figure 1.13]
Fig 1.13
Click OK and refresh the databases. You can see the caption of the DB1database has changed in both principal and mirrored server. [Refer Fig 1.14]
\
Fig 1.14
Conclusion:
Part I of this series explained the basics of the Database mirroringfeature in SQL Server 2008. It also illustrated a step-by-step process on howto create database mirroring. Part 2 of this series we will discuss how to adddata on the principal server and how data is mirrored to the mirrored server.
Link:http://www.databasejournal.com/features/mssql/article.php/3828341/Database-Mirroring-in-SQL-Server-2008.htm
- Database Mirroring in SQL Server 2008
- SQL Server 2008 Database Mirroring
- Configure SQL Server Database Mirroring Using SSMS
- SQL Server How to Perform Database Mirroring
- SQL Server Database Mirroring - Creating and using Mirror Endpoints in Principal, Mirror and Witness
- 比较SQL Server Always On & Cluster & Mirroring
- Database Mail set up in SQL Server 2008
- How to replace expired certificates used in database mirroring
- Database Mail in SQL Server 2005
- Accessing SQL Server Database in Editable GridView
- Database Mirroring FAQ
- 综合Database Mirroring Witness
- Log Shipping VS Database Mirroring
- How to shrink the tempdb database in SQL Server
- How to backup and restore database in SQL Server
- SQL Server 2008如何copy database
- SQL Server system database
- Restore SQL Server database in same SQL server with different name
- 对本人所发资源“金山词霸2010牛津旗舰版安装及故障终极解决方案”的说明
- 一个lambda遗留题
- 事以密成,能者低调
- 在VS2008中使用Qt编程
- js判断浏览器
- Database Mirroring in SQL Server 2008
- [IT最前沿--有点道理] 苹果用户都是受虐狂?---苹果与微软的用户文化
- Linux驱动修炼之道-framebuffer(上)
- BlackBerry Code Key/ signature generate.
- Lucene全文检索1
- 如何查看Brocade光纤交换机的序列号
- 补丁工具quilt学习资源分享
- 我该怎么安排下属的工作-项目经理如何分配任务
- C 语言 fwrite 和 fread 文件读写操作示例