[email protected] Website: www.teltone.com
Application Note 116:
Gauntlet System High Availability Using Replication
Overview
Ensuring the high availability of a Gauntlet system can be accomplished using several methods:
• Data backup and restore
• Disk mirror or RAID
• Database replication
• Clustering
This document describes using SQL Server transactional replication to provide a cost-effective and robust solution. Although replication requires a minimum of two servers with Microsoft SQL Server 2000 software, the duplicate database is
updated in near real-time, bandwidth requirements are low, and, if the source database fails or is unavailable, an administrator can switch to the duplicate relatively quickly. If the two servers are located in different areas, replication also provides business continuity during natural and other disasters.
In essence, transactional replication ensures that all changes to the source database are also copied to a duplicate database. Once the transactional replication is set up, the duplicate database is updated whenever a change is made to the source. The Microsoft SQL Server replication process uses the publishing industry as a metaphor, where the source server is a publisher and distributor and the duplicate server is a subscriber.
The transactional replication setup, described in this document, uses two servers:
one for the source database publisher and distributor and a second for the
subscriber with subscription to the distributor (see Figure 1). Data is pushed from the distributor to the subscriber as Gauntlet administrators make changes to the
Subscription Database
Subscriber
CCC Server (TSTFMDIST)
Duplicate of Source CCC Database Server
CCC Web Application
Publication Database
Distribution Database
Source CCC Database Server
Publisher
Distributor
Snapshot Folder
Push Agent Snapshot Agent
CCC Server (TSTTOREPL)
Figure 1. Transactional Replication Diagram
The publisher contains publication(s), which are collections of one or more articles sent to a subscriber database via the distributor. An article, the basic unit of
replication, can be a database table or a subset of a table.
The distributor manages the flow of data through the replication system and contains the distribution database.
The subscriber receives data from another database. A subscription, which can be to one or more publications, is the data that a subscriber receives. With a push subscription, the distributor sends transactions to the subscriber. Other methods of data distribution are available, but this application note only covers the push subscription method.
About This Application Note
This application note provides Gauntlet-specific instructions for setting up SQL Server transactional replication to maintain system high availability.
Getting Additional Information
For general information about Microsoft SQL Server and replication, please refer to the Microsoft SQL Server documentation, Help topics, and online information at www.microsoft.com.
If you have Gauntlet-related problems, please contact Teltone technical support at 425-951-3390 or send an email to [email protected].
Requirements
Hardware
A minimum of two CCC servers. For CCC server hardware requirements, see the Gauntlet System Installation Manual.
Replication additionally requires:
• Hard drive space for each published database Software
For CCC server basic software requirements, see the Gauntlet System Installation Manual.
Replication additionally requires:
• Windows Server 2003 Enterprise Edition or Standard Edition (Windows Small Business Server 2003 is not supported.)
• Microsoft SQL Server 2000. Transactional replication is a standard component of all editions of SQL Server 2000. (MSDE is not supported.) Preparing Two Servers
Important: Only members of the system domain administration role can set up and configure replication. If you do not have these rights, you cannot set up replication.
For these instructions, the examples use a server named TSTTOREPL (the test database to replicate) as the primary server and configure it as publisher and
For each server:
1. Before installing the CCC software (see the Gauntlet System Installation manual), follow the steps below to install Microsoft SQL Server 2000.
2. Create a folder at the root and name it C:\Gauntlet. The default instance of SQL Server must be in this location for the Gauntlet CCC software to find it.
3. Install SQL Server and set it up for mixed authentication (Windows and SQL). Note the system administrator (sa) password used during SQL installation. This password will be needed during the Gauntlet CCC software installation.
4. After installation, verify that you see C:\Gauntlet\MSSQL$Gauntlet.
After completing the above steps on both servers:
1. Open SQL Server Enterprise Manager, and register both servers so that both are visible from either server.
2. Verify that you can connect from one server to the other within SQL Server (see “Troubleshooting,” page 20). Without verified connectivity, replication will not work properly.
3. For both servers, follow the instructions for installing the CCC software in the Gauntlet System Installation manual. When entering the “Strong Sys Admin Password” during CCC installation, enter the SQL Server sa password defined previously. If the sa password does not match,
Gauntlet administrators will not be able to change the SQL database.
4. Verify that SQL Server and Gauntlet CCC are up and running on both servers before starting the replication setup.
5. Open Microsoft Internet Explorer and connect to the CCC URL. If successful, the CCC login page is displayed. Resolve any error messages before continuing.
Transactional Replication Setup
The following instructions guide you through:
• Creating a new remote server to be the secondary database
• Creating a primary SQL server publisher and distributor
The following example screen captures are from a Windows XP operating system.
Creating a New Remote Server
To start, on the primary server, open SQL Server Enterprise Manager by clicking Start > All Programs > Microsoft SQL Server > Enterprise Manager.
Navigate to the TSTTOREPL\Gauntlet server and expand the Security folder. Remote servers are listed in the right-hand pane.
Select remote servers and right-click. On the shortcut menu, select New Remote Server.
Enter the remote server name. In this example, it is TSTFMDIST\GAUNTLET.
Select RPC.
Leave remote login mappings default option, Same Name, selected.
Click OK.
The new remote server is listed in the right- hand pane.
Configuring a Publisher and Distributor
In SQL Server Enterprise Manager, navigate to the TSTTOREPL\Gauntlet server.
Select Replication.
Right-click the Replication folder. On the shortcut menu, select Configure Publishing, Subscribers and Distribution.
Click Next.
Leave the default option, Make <server name> its own distributor, selected.
Click Next.
Leave the default option, Yes, configure the SQL Server Agent service to start
automatically, selected.
Click Next.
Leave the default network path to the snapshot folder.
Click Next.
Click Yes.
Leave the default option, No, use the following default settings, selected.
Click Next.
Click Finish.
A message box indicates the distributor has successfully been enabled.
Click OK.
Click Close.
If this warning is displayed, click Yes.
Leave the default option, No, I will manually refresh…, selected.
Note that the Replication Monitor has now been added to the distributor server.
Creating and Managing Publications
From SQL Enterprise Manager toolbar, select Tools > Wizards.
Expand Replication.
Select Create Publication Wizard. use new shot with correct stuff highlighted.
Click OK.
As the publisher database, select ccc.
Click Create Publication.
Click Next.
Verify that ccc is selected.
Click Next.
Select Transactional Publication.
Click Next.
Select the types of subscribers for this publication.
In this example, leave the default option, Servers running SQL Server 2000, selected.
Click Next.
For the Tables and Views object types, select Show and Publish.
Select Show unpublished objects.
Click Next.
An Article Issues message window is displayed.
Note that only one issue, Tables referenced by views are required, should be displayed.
Click Next.
Enter a publication name. In this example, the publication name was changed from “ccc” to
“Gauntlet ccc Publication.”
Click Next.
Leave the default option, No, selected.
Click Next.
To create the publication, click Finish.
A progress screen is displayed. The pointer moves down the list until all items are completed.
Click Close.
Creating a New Push Subscription
Click Push New Subscription.
Click Next.
Expand SQL Server Group and select the subscriber database.
Click Next.
Verify that CCC is selected.
Click Next.
Leave the default option, Continuously, selected.
Click Next.
Leave the default option Yes, initialize the schema and data selected.
Select Start the Snapshot Agent…
Click Next.
Leave the default option, SQL Server Agent, selected.
Click Next.
Verify the details (subscription to
TSTTOREPL\Gauntlet publisher from
TSTFMDIST\Gauntlet subscriber).
Click Finish
A progress screen is displayed. The pointer moves down the list until all items are completed.
Upon completion, a message window indicates that subscriptions were successfully created at the following subscriber:
TSTFMDIST\Gauntlet. Click Close.
Click Close.
This completes the setup of the transactional replication. However, it is recommended that you test your setup as described next.
Testing
To verify that the subscription has been created, open Microsoft SQL Server Enterprise Manager and navigate to the TSTFMDIST\Gauntlet server. Expand the Replication folder and select Subscriptions. Click Refresh.
A subscription named TSTTOREPL\Gauntlet:ccc is displayed in the right-hand pane, along with the name of the database and other related information.
To further test the subscription, you can check recent activity in the CCC log.
Navigate to TSTFMDIST\Gauntlet\Databases\ccc\Tables. The tables in the CCC database are displayed in the right-hand pane.
Select the CCC_LOG table and right-click.
To verify that changes to the primary database are replicated in the subscriber database, add a calling user to the CCC database and then check that the user has been added to the subscriber database.
For example, log in to CCC and create a new calling user, with a login ID of
“Jack_Flash.”
Navigate to TSTFMDIST\Gauntlet\Databases\ccc\Tables again. Open the CCC_LOG table. The addition of user “Jack_Flash” is the last event.
This completes the testing of your transactional replication setup.
Primary CCC Database Failure (Changing Roles)
If a failure occurs and the publisher database is no longer available, use one of the following approaches to provide access to the subscriber database:
• Change the subscriber server’s name and IP address to those of the publisher server.
or
Troubleshooting
Most problems experienced while setting up replication are related to account access rights, naming details, and connectivity (or the lack thereof) between the SQL publisher/distributor and subscriber.
To verify connectivity in SQL:
1. On the publisher server, open Microsoft SQL Server Enterprise Manager.
2. Expand Microsoft SQL Servers and SQL Server Group to show all servers.
3. Right-click the subscriber server and select Connect.
Successful connection is indicated by the server’s arrow indicator changing from no color to green. No alarms or warnings should be generated as a result of this action.
