Recently in TANDBERG TMS Category

Database mirroring - specifically for TANDBERG TMS

| No Comments | No TrackBacks

The last few weeks, I have been spending some time on setting up database mirroring. I now have scripts that will quickly set-up a database mirroring along with special steps needed if you are running the database servers of different domains - which do not have a shared user database.

This post will explain how to set-up database mirroring for a product I do custimizations for - TANDBERG Management Suite (or just TMS). TMS is a management and reservation system for video conferencing systems. As it is a vital system in many installations, it is important to have good redundancy on the database. TANDBERG has some recommendations on redundancy, but the options listed for database redundancy are quite pricy and not as simple to use as database mirroring.

For those who don't care about video conferencing, this post can be used to set-up mirroring on other SQL Server 2000 SP1 or higher databases as well, I would assume a simple search and replace for 'tmsng' to 'Northwind' for example will allow setting up mirroring on the Northwind database.

Note: this point outlines setting up a primary and slave mirroring server without a witness. It means that we will have a good failover database (the slave) but due to not using a witness you cannot automatic failover. A description on that will follow at a later date.

What you need;

  • Two database servers running Microsoft SQL Server 2005 Enterprise
  • The servers must be running SP1 or later (mirroring is not supported before this)

So time to get our hand dirty, the table below shows and explains the commands that need to be executed on the Primary and Slave servers using SQL quieries. No wizards here thank you.

Use a database owner/sa to set-up mirroring on the Primary and Slave servers. Expect TMS to be offline/not available while setting up the mirroring.

Primary Server

Slave Server

USE MASTER

-- Set single user mode on the database
-- so we can do what we need. If you are
-- running TMS with the 'sa' user, you will
-- have to stop all TMS* services and the
-- web-server, as with the sa user cannot
-- kickout other sa users. The AFTER 30
-- is used to give a grace periode of 30
--  seconds to kick people out.
ALTER DATABASE [tmsng] SET SINGLE_USER WITH ROLLBACK AFTER 30

-- TMS ships with the AUTO_CLOSE option
-- on (don't know why, basically if the
-- database is not in use for a periode of
-- time, the database connection is closed
--  automatically. Given TMS is using the
--  database all the time for services, it
-- doesn't really make sence... DB
-- Mirroring only works with the off.
ALTER DATABASE [tmsng] SET AUTO_CLOSE OFF
GO

-- Some database are not in full recovery
-- mode (simple instead) mirroring requires
-- full recovery mode on.
ALTER DATABASE [tmsng] SET RECOVERY FULL
GO

-- Now make a backup of the database
-- and database log. Note I am storing the
-- file in c:\ - change the path if you like.
-- You *must* backup both DB and LOG to
-- enable mirroring on the slave.
BACKUP DATABASE [tmsng]
   TO DISK = N'C:\tmsngDBBackup'
   WITH INIT, NAME = N'DBBackup',
   STATS = 10
GO

BACKUP LOG [tmsng]
   TO DISK = N'C:\tmsngLogBackup'
   WITH INIT, NAME = N'LogBackup',
   STATS = 10
GO

-- As we do not assume that the
-- database servers are in the same
-- domain, we do not have a shared
-- user that we can use on both Primary
-- and Slave. Therefore we create a user
-- that authenticates with a certificate
-- and give this user needed rights on
-- both servers to enable mirroring.

-- Start with creating a certificate.
-- First step, create a MASTER KEY
CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = 'password'
GO

-- After creating a MASTER KEY we can
-- create a certificate
CREATE CERTIFICATE PrimaryCert
   WITH SUBJECT = 'PrimaryCert',
   start_date = '01.01.2008',
   expiry_date = '01.01.2015'
GO

-- Now make a backup of the certificate,
-- as we need to 'install' it on the SLAVE
--  database in order to log in to
-- the PRIMARY database from the
-- SLAVE. Again note the file location.
BACKUP CERTIFICATE PrimaryCert
   TO FILE = 'C:\PrimaryCert.cer'
GO

-- Ok, stepping away from users a sec,
-- we need to create and endpoint on the
--  PRIMARY that the SLAVE can use
-- to communicate 'mirroring information'.
-- The Endpoint will allow users using the
-- certificate we created above to use the
--  endpoint.
CREATE ENDPOINT Mirroring
     STATE = STARTED
     AS TCP
       (LISTENER_PORT = 5022)
     FOR DATA_MIRRORING
       (ROLE = PARTNER,
        ENCRYPTION = SUPPORTED,
        AUTHENTICATION =
        CERTIFICATE PrimaryCert)
GO

-- Now you need to switch over to the
-- SLAVEdatabase to do some steps
-- there. Copy the three files we created
-- from the PRIMARY to SLAVE. Use
-- c:\ E.g.
--    1. C:\tmsngDBBackup
--    2. C:\tmsngLogBackup
--    3. C:\PrimaryCert.cer

 
 

-- This script assume you copied all the
-- files mentioned to c:\ on the slave

-- As we did on the PRIMARY, we create
-- the needed certificate to create a user
-- on PRIMARY that can access SLAVE
-- (different direction than we did on
-- PRIMARY)
USE MASTER
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
GO

CREATE CERTIFICATE SlaveCert
      WITH SUBJECT = 'SlaveCert',
      start_date = '01.01.2008',
      expiry_date = '01.01.2015'
GO

-- Make a backup of the certificate, that
-- we need to 'install' on the PRIMARY later.
BACKUP CERTIFICATE SlaveCert
      TO FILE = 'C:\SlaveCert.cer'
GO


-- Next we create an endpoint on SLAVE
-- as well (communication from PRIMARY)
-- accessable to users using the SlaveCert
-- we just created.
CREATE ENDPOINT Mirroring
   STATE = STARTED
   AS TCP
      (LISTENER_PORT = 5022)
      FOR DATA_MIRRORING
         (ROLE = PARTNER,
         ENCRYPTION = SUPPORTED,
         AUTHENTICATION = CERTIFICATE SlaveCert)
GO

-- Now using the certificate from PRIMARY
-- (not SLAVE that we just made, but the
-- one we copied over before we switched
-- to SLAVE) - we create a LOGIN to
-- so that PRIMARY can LOGIN to
-- SLAVE for mirroring.
CREATE LOGIN PrimaryLogin
   WITH PASSWORD = 'password'
GO

-- Now create a user for the login
-- to use the MASTER database of
-- the SLAVE.
CREATE USER PrimaryUser
   FROM LOGIN PrimaryLogin
GO

-- Since we are not using the user's
-- password to to gain access, but a
-- certificate, setup the certificate on
-- the user. Note we are using the
-- certificate from PRIMARY - not
-- the SLAVE (it is PRIMARY that
-- needs access to SLAVE to do the
-- mirroring)
CREATE CERTIFICATE PrimaryCert
   AUTHORIZATION PrimaryUser
   FROM FILE = 'c:\PrimaryCert.cer'
GO

-- Now grant the login rights to use
-- the ENDPOINT we created for
-- mirroring a few steps back
GRANT CONNECT
   ON ENDPOINT::Mirroring
   TO PrimaryLogin
GO

-- Cool, now we have all the user
-- stuff on SLAVE done (still some
-- more to do on PRIMARY). Before
-- we leave SLAVE, lets restore
-- the backup. Mirroring requires
-- us to restore the database
-- 'halfway' - meaning it is not a
-- complete restore, and therefore
-- is not fully usable. This is done
-- by specifiing the NORECOVERY
-- setting. Remember as well, we
-- need to restore both the database
-- and transactional log for mirroring
-- to work... Also note that I use the
-- MOVE keywork to specify where
-- I want the database file and log to
-- be placed. Change this to your
-- preference (and maybe something
-- better than having the DB and LOG
-- on the same disc....)
RESTORE DATABASE [tmsng]
  FROM DISK = N'C:\tmsngDBBackup'
  WITH NORECOVERY, NOUNLOAD,
  STATS = 10, REPLACE, 
  MOVE 'tmsng' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tmsng.mdf',
   MOVE 'tmsng_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tmsng_log.ldf'
GO

RESTORE LOG [tmsng]
  FROM DISK = N'C:\tmsngLogBackup'
  WITH NORECOVERY, NOUNLOAD,
   STATS = 10,
   MOVE 'tmsng' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tmsng.mdf',
   MOVE 'tmsng_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tmsng_log.ldf'
GO

-- Cool, now we are done with SLAVE for
-- now. Before we move over to PRIMARY
-- you need to copy the c:\SlaveCert.cer
-- file over to PRIMARY - in the other
-- direction as the three files we move
-- to SLAVE earlier. This is needed to
-- create a LOGIN and USER for the
-- SLAVE to gain access to PRIMARY

-- Back on PRIMARY now, remember
-- to copy the SlaveCert file from SLAVE
-- first!
-- On Primary

USE MASTER
GO

-- Same stuff we did on SLAVE to
-- create a LOGIN and USER and
-- give access to the ENDPOINT,
-- this time for SLAVE to use PRIMARY
CREATE LOGIN SlaveLogin WITH
   PASSWORD = 'password'
GO

CREATE USER SlaveUser
   FROM LOGIN SlaveLogin
GO

CREATE CERTIFICATE SlaveCert
   AUTHORIZATION SlaveUser
   FROM FILE = 'c:\SlaveCert.cer'
GO

GRANT CONNECT
   ONENDPOINT::Mirroring
   TO SlaveLogin
GO

-- Almost there, hang on! Now we need
-- to enable mirroring. Always do this on
-- SLAVE first, or it will not work!

USE MASTER
GO

-- Now enable mirroring on SLAVE. Note
-- the SQL below will always successed
-- if the syntax is correct. Even if the
-- server/port is incorrect. the connection
-- is first used when you connect the
-- PRIMARY to the SLAVE a bit later.
-- So make sure you get the address
-- right.
ALTER DATABASE [tmsng]
   SET PARTNER =
   N'TCP://full.path/ip.to.primary:5022';
GO

-- That is right, there wasn't anything
-- more to do on SLAVE. Now its time
-- to check if you did everything like
-- I wrote. We start mirroring on
-- PRIMARY, pointing it to SLAVE
USE MASTER
GO

ALTER DATABASE [tmsng]
   SET PARTNER =
   N'TCP://full.path/ip.to.slave:5022';
GO

-- Set safty mode, so that all
-- transactions are commited to
-- both PRIMARY and SLAVE
-- before returning - ensuring
-- a FULL mirror
ALTER DATABASE [tmsng]
   SET SAFETY FULL
GO


-- Set the database back to Multi user
-- mode.
USE MASTER
GO

ALTER DATABASE [tmsng]
   SET MULTI_USER

-- If you disabled TMS services and
-- web-server, now is the time to bring
-- them all back online...

-- Your set! It is getting late, so I'll wait
-- until I get some more time another
-- day, when I will post some trouble
-- shooting tools for Mirroring.
-- I also am writing up a bit about
-- how to failover from PRIMARY
-- to SLAVE and vice-versa.

About this Archive

This page is an archive of recent entries in the TANDBERG TMS category.

SQL Server is the previous category.

Find recent content on the main index or look in the archives to find all content.

Pages

Powered by Movable Type 4.21-en