Search This Blog

2009-10-18

Sql Server 2005 Mirroring within Workgroup or Domain without FQDN

Database Mirroring is a technology for increasing database availability. Database Mirroring transfers transaction log records from one server to another, allowing quick fail over to the standby server. In the event of a failover, client applications can automatically redirect their connection to the standby server. Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. However, Database Mirroring can fail over quickly with no loss of committed data. In addition, Database Mirroring does not require proprietary hardware, and it is easy to set up and manage.

For Sql server mirroring you need atleast two Sql Server running on different PC.
First you have to configure Sql server for remote connections (in both the instances)so that it can accept incoming request from another SQL Server running on different PC.Follow steps 1-7.

Step 1.Open the "Sql Server surface area configuration".

Step 2.Click on the "Surface area configuration for services and connections"

Step3.Select "using both TCP/IP and named pipes"

Step 4.Be sure that the Sql Server Browser is running

Step 5.Click on "Sql server Configuration manager"

Step 6.Configure your Sql server instances.Put the IP address of your PC in the TCP/IP Properties

Step7.Restart your SQL Server Services.

Now create your database in your principal / First Server and take a full backup and restore it in other SQL server(Mirror Server/second server).Follow step 8 to step 10.

Step 8.Click on "Backup"


Step9.Take a full backup of your database.

Step 10.Restore your database with norecovery option in the mirror server



Step 11.Now its time for configure mirroring.Run the following code in the principal server.
--STEP 11.1: Run this on testServer1
USE master
--drop CERTIFICATE testServer1_cert
--drop master key
--drop endpoint Mirroring
--select getdate(),* from sys.certificates
--select * from master.sys.endpoints

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';
GO
--STEP 11.2:
CREATE CERTIFICATE testServer1_cert WITH SUBJECT = 'testServer1 certificate
for database mirroring',expiry_date ='10/31/2015',start_date='10/31/2008'
GO
--STEP 11.3
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP(LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer1_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

--STEP 11.4
BACKUP CERTIFICATE testServer1_cert TO FILE = 'C:\testServer1_cert.cer';
GO
--STEP 11.5: Copy C:\testServer1_cert.cer to testServer2(Mirror server) on C:\ drive

Step 12.Run the following steps in the mirror (testserver2) server.
--Step 12.1:
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';
GO
--STEP 12.2:
CREATE CERTIFICATE testServer2_cert WITH SUBJECT = 'testServer2 certificate
for database mirroring',expiry_date ='10/31/2015',start_date='10/31/2008'
GO
--drop endpoint Mirroring
--STEP 12.3
CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP(LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE testServer2_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

--STEP 12.4
BACKUP CERTIFICATE testServer2_cert TO FILE = 'C:\testServer2_cert.cer';
GO
--STEP 12.5: Copy C:\testServer2_cert.cer to testServer1(Principal Server) on C:\ drive

Step 13:Run the following steps again in the principal server
--STEP 13.1: Configure testServer1 for Inbound connections. Run this on
--testServer1.Principal Server
USE master

CREATE USER user1_mirroring
FOR LOGIN sa
GO

--STEP 13.2:Associate the certificate with the user
CREATE CERTIFICATE testServer2_cert
AUTHORIZATION user1_mirroring
FROM FILE = 'C:\testServer2_cert.cer'
GO


--STEP 13.3:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO sa;
GO

Step 14.Prepare for mirroring. Run this on the MIRROR (testServer2) first before
doing it on the PRINCIPAL (testServer1,IP-192.168.1.11)
--Step 14.1
USE master
GO
ALTER DATABASE MRI SET PARTNER = 'TCP://192.168.1.11:7024';
GO

Step 15.--Prepare for mirroring. Run this on the PRINCIPAL (testServer1) after
running the previous script on the MIRROR (testServer2,IP:192.168.1.11)
--Step 15.1
USE master
GO
ALTER DATABASE test4 SET PARTNER =
'TCP://192.168.1.11:7024';
GO

Your server is now ready for mirroring.

Step 16.If you want to make testserver2 as a principal server run the following SQL in the testserver1

alter database test4 set partner failover

Step 17.Again If you want to make testserver1 as a principal server run the following SQL in the testserver2

alter database test4 set partner failover

Step 18.if your testserver1 is running as principal server and suddenly it is stopped and you want to recover data from Mirror server(testserver2) run the following code in the mirror server.

alter database test4 set partner off
restore database test4 with recovery

Now you understand how it is easy configure mirroring in sql server 2005 with the IP address whether it is in workgroup or in domain.

1 comment:

Varun Sharma said...

Hi There.. Till now my thinking was that we require 3 servers for this purpose.. but this was a break through... Thanks for showing the wayout with 2 servers..