SQL Server replication phase: Level 1-Introduction to SQL Server replication

In Sebastian Mui, 2012/12/26

the series This article isPart of the "Stairway series: SQL Server replicated ladder" SQL replication resolves many issues in running database-driven applications. The publishing/subscriber model is not entirely easy to understand, and the complexities of scripting and monitoring replication systems need to be considered. Here, finally, is a series of articles that note the generation of a jargon-free method for all types of SQL Server replication.levelIntroduction to 1:SQL Server replicationMain articles:Rep-li-ca-tionpronunciation:\？ Re-pl? -' Ka-sh? n \function: noun Date:14th centuryCopy the word from the Latin wordReplicare means repetition. Replication describes the process of copying or copying (www.merriam-webster.com). Replication in SQL Server is exactly the case; it replicates or replicates data. You can use replication whenever you need to create a copy of the data, or reproduce changes to that data. The copy can be created in the same database or in a remote location on a separate server.replicas can be kept in sync with the source data continuously or at scheduled intervals. One-way synchronization and bidirectional synchronization are possible. Replication can even be used to keep several datasets in sync with each other. at the first level, I'll cover basic replication components and describe how they work together to replicate data and change data. We'll also look at a detailed example of setting up a simple replication scenario. Replication ComponentsSQL Server replication consists of three components: the publisher, the Distributor, and the Subscriber. These components work on articles that are defined in publications and subscriptions.article for each copy of theSQL Server object, you need to define a replication project. Each article corresponds to a subset of a single SQL Server object or object. The most frequently copied objects are tables, views, and stored procedures. For a complete list of objects that you can copy, see Publishing Data and Database objects in Books Online. The properties of the article determine whether the article contains the entire object, or whether the filtered subset of objects constitutes a copy of the article. With some restrictions, you can create multiple articles on an object.Publications articles that logically belong to a group can be merged into one publication. The publication defines the options that apply to all articles in the publication. The primary option for publishing definitions is the type of replication to use. published by To make a publication available for replicationAn instance of SQL Server is called a publisher.The Publisher monitors changes to all articles and provides information about the changes available to the Distributor. Distributors Distributors areThe SQL Server instance tracks all subscribers and all published changes and ensures that each Subscriber is notified of each change. Most of the changes are tracked in the allocation database. The distributor can be a separate instance of SQL Server, but the distribution service typically runs on the same computer as the publisher.subscribers subscribers receive all published information through a subscription.An instance of SQL Server.Subscribe The subscription is a copy of the publication. A subscription defines which server (subscriber) will receive updates published in the publication. Each subscription establishes a link between a publication and a subscriber. There are two types of subscriptions: Push subscriptions and pull subscriptions. In a push subscription, the Distributor updates the data in the Subscriber database directly. In a subscription subscription, subscribers periodically ask the distributor if a new change is available, and then update the data itself.

Type of replication in theThere are three primary types of replication in SQL Server. They are snapshot replication, merge replication, and transactional replication.snapshot replication snapshot replication Creates a full copy of the copied object and its data at each run. It usesThe bcp utility for SQL Server writes the contents of each table to the snapshot folder. The snapshot folder is the shared folder location that must be set at the Distributor when replication is enabled. Each participant in the replication settings needs access to the snapshot folder.each time you run snapshot replication, replication starts from scratch and therefore has high bandwidth and storage requirements. All other types of replication (by default) use one copy snapshot, synchronizing all subscribers with the reseller only during the initial setup. Transactional Replication transactional replication works on a transactional basis, as its name implies. Scan each committed transaction to find the changes that apply to the copied article. Scan changes are completed by the Log Reader Agent, which reads the transaction log of the publisher database. If changes that affect published objects occur, those changes are recorded at the distributor in the distribution database. From there they headed for the subscribers. transactional replication allows for near real-time synchronization and leaves only a small amount of space on the publisher. Although there are several options to allow bidirectional data movement, transactional replication was originally designed to work only one way. merge Replication The design of merge replication allows you to make changes to the data at the Publisher and Subscriber side from the outset. Merge replication also allows you to disconnect without connecting users during the day. The user will be synced at night after reconnecting. If a row is updated at the same time in two different places, a conflict occurs. Merge replication has several built-in options to resolve these conflicts. set up transactional replication This section is a step-by-step guide to setting up transactional replication involving a single replicated table. to set up replication, you need to configure the Distributor, Publisher, and Subscriber. can useT-SQL scripts fully set up and control replication. However, the necessary T-SQL involves the use of stored procedures, which together have more than 100 required parameters. So it makes sense to start by using Ssms-gui. The example screen shown here is on a single server (WIN2008A) with a SQL Server instance (R2A) installed. This instance is a Sql-server 2008r2 instance. However, you can also follow the examples on SQL Server 2008 and 2005.Create an Assignment A reseller is the core of transactional replication. All other components need to be available for setup, so they need to be configured first. by openingSSMs and connect to the Sql-server instance that will contain your replication source data. While it is possible for a reseller to be on its own SQL Server instance, in many cases it makes sense to have publishers and distributors on the same computer, as assumed in this example.after connecting to the server, right-click the server underCopy folder, and then select Configure Distribution ..., as shown in 1.

The Configure Distribution Wizard (Configure distribution wizards) will greet you with its splash screen, as shown in 2.

ignoring this screen and clicking "Next" is safe. On the next screen (Figure 3), you will choose whether to run the distribution service on this server or whether there are already configured distributors on your network. The machine running the distribution service will also contain the distribution database. Leave this option as the default, which is to install the release version on this server, and then click Next.

Figure 3: Selecting a distribution server

This will pop up the dialog box in Figure 4, which asks you to select the location of the snapshot folder .

The snapshot folder can be anywhere on the machine or in the network. It is meaningful to create a network share on the reseller. This example uses "\ \ win2008a \ Replicationsnapshotfolder". the settings for the snapshot folder also need to be granted the appropriate permissions. do not go into too much detail now, grant write access to the "authenticated Users" (Figure 5) of the folder itself, and read access to "Everyone" (Figure 6) on the share. More information on how to strengthen security in this area will be given in the subsequent stages of this ladder.

Figure 5: Granting access to the snapshot folder

Figure 6: Granting access to the snapshot folder share

after you have set up your share and put the network path into the wizard's input fields, press next to go to the distribution database form, as shown in Figure 7.

Figure 7: distribution database

Here you specify the name of the distribution database and the location of the data and log files. leave the default values and click Next to go to the publishers screen (Figure 8). on the publishers screen, you are ready for potential publishers to be able to use this distributor. We will install our publisher on the same instance, so you can keep the default values and "Next" in the final question (Figure 9).

The last question is whether you want the wizard to execute your selection immediately or if you want the wizard to create a script that will be executed manually later. again, leave the default setting and last Click Next. you will now see the list of actions shown in 10. Click "Finish" to start the process.

Figure : Wizard Summary

Finally, the screen shown in Figure 11 gives information on the progress and success of the replication configuration.

first time Publication to create a publication, we need to first have a database that contains the tables to be published. Execute SQL script 1 to create a test database for the publication.

Use MASTER;

GO

EXECUTE as LOGIN = ' SA ';

GO

CREATE DATABASE Repla;

GO

Use Repla;

GO

IF object_id (' dbo. Test ') is not a NULL DROP TABLE dbo. Test;

GO

CREATE TABLE dbo. Test (

Id INT IDENTITY (PRIMARY KEY),

Data INT CONSTRAINT test_data_dflt DEFAULT CHECKSUM (NEWID ())

);

GO

INSERT into dbo. Test DEFAULT VALUES;

GO 1000

Use MASTER;

GO

REVERT;

GO

Script 1: Create a test database for your publication now you're ready to set up a publication. Open the Replication folder in SSMs Object Explorer and right-click Local Publishing. Select "New Publication ..." from the drop-down menu (Figure 12).

Figure : New Publication

The Welcome page for the New Publication Wizard appears , as shown in 13.

in The Publish Database box (Figure 14), select the database repla you just created, and then click Next. The publication Type screen (Figure 15) allows you to choose which type of replication to use. Select Transactional publication, and then tap Next.

Now you can choose which articles should be part of this article (Figure 16). Select Table dbo. Test and click Next again to enter the Filter Table Rows dialog box (Figure 17). Filters are a high-level topic that will be introduced in the hierarchy at the back of this ladder, so now just click on "Next" instead of making a selection on this form.

the next three screens process the Snapshot Agent. on The first screen (Figure ), select Create Snapshot Now, and then tap Next. On the Agent Security screen (Figure ), click the Security Settings button, and then on the open form, select run under the SQL Server Agent service account (Figure 20 ).

Script1in theReplaA table is created indbo. Test, and in which you insert a +line. After the initial snapshot is transferred to the Subscriber, you willREPLBfound indbo. Testtable, which contains all +line. Within a few minutes after the setup is complete, you can run the script3"To verify that replication pushes all data to subscribers as expected. This script willReplA.dbo.Testand theReplB.dbo.Testtables are joined together to show which rows are copied correctly. You can now run your own further tests that you can insert and updateReplA.dbo.TestThe lines in and watch these changes magically appear inReplB.dbo.Testthe.

SELECT TOP (a.id) as [repla.id],a.data as [repla.data],b.id as [replb.id],b.data as [Replb.data]

From ReplA.dbo.Test A

Full OUTER JOIN ReplB.dbo.Test B

On a.id = b.ID

ORDER by a.ID DESC

Script 3: Compare publisher and subscriber profiles The objects that are marked as replicated in the database on the SQL Server instance that are called publishers are called articles. The articles are grouped together to become publications. Subscribers get updates to changes to the article through a subscription. Data flows through distributors ' distribution databases. Publishers, distributors, and subscribers can be the same instance, or they can be separate instances on the same or different computers. The source and destination databases can be the same (if the Publisher and the Subscriber are actually the same instance of SQL Server ), but the distribution database must be separate.

SQL Server replication phase: Level 1-Introduction to SQL Server replication