DATABASE MIRRORING - SQL 2005

Database Mirroring

Let us do database mirroring between two servers. In this example we are using sql 2005 standard edition with service pack 2.

I.Please note the database mirroring can also be done on the same server with multiple instances.

I. Before you get started with database mirroring please make sure the following are checked.
1. The principal and mirror server are either standard or enterprise edition. Please note we can also use the witness server for automatic failover.
2. The principal and mirror server are on the same service pack.(Recommended service pack 2). It is available for download from Microsoft. Please use the appropriate version based on 64 bit edition, 32 bit edition or 32 ia edition.
3. Please make sure there is sufficient disk space available on both the servers principal and mirror server.
4. The principal server should be on FULL RECOVERY option. This can be set by right click on the database on the principal server --- click properties --- click options --- set to FULL RECOVERY.
5. Make sure the SQL SERVER CONFIGURATION --- click on SQL SERVER instance --- properties --- check if this in Built in account or This Account makes sure the same is applicable for the mirror server.
6. Make sure you connectivity to the mirror server from the principal server. i.e., you can connect the mirror server from the principal server, in the sql server management studio.
The following sql script can be used to identify the version and service pack.
--to identify the version of sql
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

II. After check the above now we are ready to create the end points.
-- to create endpoint on principal server
Create endpoint [endpoint1mirroring]
state = started
as tcp (listener_port = 1430, Listener_ip = all)
for data_mirroring (role = partner, authentication = windows negotiate,
encryption = required algorithm rc4)

-- to create end point on mirror server
create endpoint [endpoint1mirroring]
state = started
as tcp (listener_port = 1440, Listener_ip = all)
for data_mirroring (role = partner, authentication = windows negotiate,
encryption = required algorithm rc4)


-- to create end point on witness server we do not have it in this example
create endpoint [endpoint1mirroring]
state = started
as tcp (listener_port = 1450, Listener_ip = all)
for data_mirroring (role = partner, authentication = windows negotiate,
encryption = required algorithm rc4)

--to check the endpoints
select name,type_desc,port,ip_address from sys.tcp_endpoints
select name,role_desc,state_Desc from sys.database_mirroring_endpoints



--granting permissions this is optional from principle server or instance
grant connect on endpoint ::mirroring to [servername\vijay];

---from mirror server or instance this is optional
grant connect on endpoint : : endpoint4mirroring to [servername\vijay];

---from witness server or instance this is optional
grant connect on endpoint : : endpoint4mirroring to [servername\vijay];

III. Now on the principal server we need to the backup.

--backup database
backup database [databasename] to disk = N'E:\Transfer\db1.bak' with format

--backup log
backup log [databasename] to disk = N'E:\Transfer\db1log.bak' with format

IV. Now on the mirror server we need to the restore.

--restore database
restore database databasename
from disk = 'c:\transfer\db1.bak'
with norecovery,
move 'db1_Data' to 'E:\db1_Data.mdf',
move 'db1_Log' to 'F:\db1_Data.ldf',

please note the restore of the log will also have to be with NO RECOVERY option

--restore database
restore database databasename
from disk = 'c:\transfer\db1log.bak'
with norecovery,
move 'db1_Data' to 'E:\db1_Data.mdf',
move 'db1_Log' to 'F:\db1_Data.ldf',



V. Now we have created the endpoints. We can now use the mirroring wizard to create mirroring.

Right Click on the principal database --- click properties --- click mirroring – click on configure security --- say no on witness server option (reminder this example is without witness server) – so check No option – click next --- click on the principal and mirror server and click next ---
The principal server is identified with ip or name with default port number --- click next --- select the mirror server ip --- connection as domain\username --- ( please note there should be no warning message on the screen) --- click next ---- service accounts can be left blank or provide the domain\username ---- click finish --- Start Mirroring.


This principal database will show as databasename (Principal, synchronized)

The mirror database will show as databasename (Mirror,synchronized,restoring) --- please note this database cannot be used at this state.

To check if the database mirroring is working click on the principal server database ---- right click properties --- click mirroring --- click failover.