Sql2008 R2 synchronizing data using FTP advertisement and subscription

Sql2008 R2 using announcements and subscriptions to synchronize data

Because there are a lot of pictures in this article no picture details can go to download page http://download.csdn.net/download/yefighter/7603741

1: Advertisement server: publisher sql2008 R2 iis7.5 Windows Server

Please log in to the server do not connect remotely with SQL Server

You must turn on the SQL Server Agent service and boot yourself actively

Right-click Properties

Open SQL Server

Click on the new local announcement when the first announcement will be prompted to create an advertisement database select the first one

Select the database you need to publish

Select advertisement Type

These types of differences are in the announcement type description:

But please note:

Snapshot advertisement: The table is built again and is passed through the entire file stream

Transaction announcement: Only update the tables that need to be updated (such as deleting and altering the table); Passing only those changed data

There is a primary key to the announcement of transactions and the need for a transactional publication with a subscription.

Merge announcements and transactional announcements with subscriptions add a field to a table

Such as:

All items in the advertisement that agree to use updatable subscriptions include the uniqueidentifier column named "MSrepl_tran_version", which is used to track changes made to the replicated data. SQL Server joins this column to the published table that does not include this column.

We choose to use the transactional announcement method. and choose one of the tables to test

Set schedule time: Click Change can be changed. This test uses 1 minutes of its own initiative to update

the role of SQL Server login must include the role of sysadmin or the Subscriber will not select SQL Server Agent; this time use Jdf_dev account for subscribers to connect

Enter the name of the publication to complete

To see if the status is successful

And see if the file is generated

D:\Program Files (x86) \microsoft SQL server\mssql10_50.mssqlserver\mssql\repldata\unc\ay140102185627z_jdf_dev_ff\ 20140705175515

They're going to have more files.

Because of the file transfer, in order to resolve the two server synchronization file permissions file; We use FTP to synchronize

Select the local advertisement you just created; right-click Properties

Select an ftp Snapshot

This time we need to create an FTP site Assuming that IIS comes with ftp that can use server-u and so on FTP software operation

About assumptions using server-u in the next section of the commentary

This way, we use IIS 's own FTP

First open the server management

Installing the FTP service

After the FTP service is created

The physical path enters the file address that you just generated D:\Program files (x86) \microsoft SQL Server\mssql10_50.mssqlserver\mssql\repldata\

Just need to repldata file

Set port to 3347 at the same time remember to add the inbound port to the firewall

Re-entry into Computer Management

New Windows user for ftp use

This test uses FTP01 users

Edit Permissions

Test after FTP creation is successful

I use the flashftp to connect the test

Connection Successful

Return SQL just added subscription

Right-click Properties to select an ftp Snapshot

OK after right-click New Announcement Select View Snapshot status Select Start Again

The published party configuration is complete.

2: Accept Server Subscribers

Server configuration is Window server sqlserver2008 R2 English version number

You need to create a new SQL alias because you want to connect to remote server

Open SQL Configuration Center

New SQL connection alias

New test database accepts synchronized data

And then create a new subscription

Choose Find SQL Server Publisher

Enter a SQL account with Administrator privileges and password we will use the account previously published by the server Jdf_dev

When you choose, you'll see the announcement that the server side has just added

Select a synchronization method

The first is the method of synchronous push ( push ) by the published party's SQL Agent and the Advertisement party's initiative; This will take up more memory of the advertised party .

The other way is to synchronize the published party data by The subscriber's unsolicited request for data and pull . This consumes more subscriber memory

See your server configuration and business requirements above

This time using a different way

Then the corresponding hypothesis uses a second way to synchronize the need

You must turn on the SQL Server Agent service and boot yourself actively

Back

Previous Select Next Select the database that the Subscriber has just started to synchronize

The above login number and password are used to connect to the published server using jdf_dev account

When you click Finish , a subscription will be given on-premises .

Sql2008 R2 synchronizing data using FTP advertisement and subscription