- General concepts
- Checking necessary conditions
- Checking connect
- Example
- Literature
General concepts
Replication is the process whereby data is copied between databases on the
same server or different servers connected by LANs, WANs, or the Internet.
Microsoft SQL Server replication uses a publish, distributor and subscribe
metaphor.
Publisher is the server or database that sends its data to another server
or database.
Subscriber is the server or database that receives data from another server
or database.
Distributor is the server that manages the flow of data through the replication
system. This server contains the distribution database.
Publisher contain publication/publications.
Publication is a collection of one or more articles that is sent to a subscriber
server or database. Article is the basic unit of replication and can be a table
or a subset of a table.
Subscription is the group of data that a server or database will receive. This
can be one or more publications. There are push and pull subscriptions.
Push subscription is subscription when the publishing server will periodically
push transactions out to the subscribing server or database.
Pull subscription is subscription when the subscribing server will periodically
connect to the distribution database and pull information.
Microsoft SQL Server support the following replication topologies:
- Central publisher
- Central subscriber
- Central publisher with remote distributor
- Publishing subscriber
Central publisher
This is one of the most common replication topologies. One server is
configured as Publisher and Distributor and another server/servers
is configured as Subscriber/Subscribers.
Central subscriber
This is a common topology in data warehousing. Many servers or databases
replicate their data to a single central server or database.
Central publisher with remote distributor
In this topology distribution database resides on the another server
than publisher. This topology uses for performance reasons when the
level of replication activity increases or the server or network
resources become constrained.
This topology requires separate Microsoft SQL Server installations,
one for the Publisher and one for the Distributor.
Publishing subscriber
This is a dual role topology. In this topology, the publishing server
or database also subscribes to another server or database. In this scenario
two servers publish the same data. This is useful when a Publisher must send
data to Subscribers over a slow or expensive communications link.
Checking necessary conditions
Check the following before setting up transactional replication:
1. Remember that Microsoft SQL Desktop Edition supports only subscriptions to
transaction replications. So you must use Microsoft SQL Standard,
Enterprise or Small Business Server (SBS) editions to publish transaction
replications. So for publish transaction replications you must have
Windows NT Server, Windows NT Server Enterprise Edition or
Windows NT Small Business Server (on Windows NT WorkStation and
Windows 95/98 you may install only Microsoft SQL Desktop Edition version).
2. The Localsystem account has no access to shares on the network as it
isn't an authenticated network account.
So if you want to setting up replication you must change the account
the MSSQLServer and SQLServerAgent services runs under to a account
with the Windows NT administrator's rights.
3. Only members of the sysadmin server role can set up and configure
replication, so if you have not this rights, you can not set up
replication.
4. You must uncheck trunc. log on chkpt option, if you want to set up
transactional replication.
5. Allocate adequate log space for each database that will be published
in transactional replication.
6. Allocate adequate disk space for the distribution database.
7. Ensure the server that is being replicated to is defined as a remote server.
Checking connect
1. Check TCP/IP with ping utility.
2. Check odbc connection with odbcping utility.
It's full syntax of odbcping utility:
odbcping [-S Server | -D DSN] [-U Login Id] [-P Password]
If you receive this message, then you ms sql server start and work okay:
"
CONNECTED TO SQL SERVER
ODBC SQL Server Driver Version: 03.70.0623
SQL Server Version: Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 23 1998 21:08:09
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 4)
"
If you receive this message, then connection could not be established:
"
COULD NOT CONNECT TO SQL SERVER
SQLState: 08001 Native Error: 6
Info. Message: [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState: 01000 Native Error: 2
Info. Message: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
"
In this example I will use two servers: CHIGRIK_A_U - publisher and distributor
and MAKSIMUK_I_L - subscriber. I will use transactional replication with push
subscription.
Register new remote server in which will be replicated.
Enter the name of remote server and choose Remote Login Mapping:
If you have made all correctly, you receive the following:
Select Tools -> Replication -> Configure Publishing and Subscribers:
This will launch the Configure Publishing and Distribution Wizard:
Select the Next button to create the Distributor:
Now you may choose default settings or set the distribution database
name and location, enable other Publishers or set another settings.
Click the Next button.
Click the Finish button.
Now Microsoft SQL Server create the distribution database, enabling
publishing, and setting up the distributor:
As we installed CHIGRIK_A_U as Distributor then Replication monitor
has been added to the console tree on CHIGRIK_A_U server.
Now we are ready to start creating publications and articles. Select
Tools -> Replication -> Create and Manage Publications:
You will see Create and Manage Publications dialog box:
Choose pubs database and click the Create Publication button.
This will launch the Create Publication wizard
Select Transactional publication and click Next button.
You may allow immediate-updating subscriptions on this step. If you choose
this option then all changes will be applied at the Subscriber and Publisher
simaltaneously.
Choose Subscriber types on this step. If all subscribers is Microsoft SQL
Servers then choose first option.
Select tables or stored procedures to publish and click Next button.
Choose publication name and description on this step.
You may define data filters or set another options on this step.
Click Finish button to create the publication.
If publication was successfully created, you receive the above message.
Create new push subscription now.
Click Next button.
Choose subscribers. Click Next button.
Choose destination database. It's pubs database on the MAKSIMUK_I_L server.
If you want to have continuous replication that will send the data as soon
as possible, you can choose Continuously option otherwise choose schedule
option. The shedule option can be very effective when delaing with slow
links. Select the Continuously option and click the Next button.
Initialize subscription if it's needed.
Click Next button.
Click Finish button to create the subscription.
Now you will receive the following:
Literature
- SQL Server Books Online.
- Microsoft SQL Server 7.0 Unleashed
(Publisher: Macmillan Computer Publishing)
Author(s): Simon Gallagher; Sharon Bjeletich; Vipul Minocha; Greg, et al Mable
http://books.internet.com/books/0672312271
- Microsoft SQL Server 7 DBA Survival Guide
(Publisher: Macmillan Computer Publishing)
Author(s): Mark Spenik; Orryn Sledge
http://books.internet.com/books/0672312263
- Special Edition Using Microsoft SQL Server 7.0
(Publisher: Macmillan Computer Publishing)
Author(s): Stephen Wynkoop
http://books.internet.com/books/0789715236
- Using Microsoft SQL Server 7.0
(Publisher: Macmillan Computer Publishing)
Author(s): Brad McGehee
http://books.internet.com/books/0789716283
»
See All Articles by Columnist Alexander Chigrik
» 
Comment and Contribute
