Sql2008 R2 synchronizing data using FTP Publish and subscribe

Sql2008 R2 synchronizing data using publish and subscribe

Because many pictures this article does not have the picture detail to enter the download page http://download.csdn.net/download/yefighter/7603741

1: Publisher: 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 start automatically

Right-click Properties

Open SQL Server

Click New Local Publish first publish will prompt to create a publication database Select the first line

Select the database you want to publish

Choose a Publication type

These types of differences are in the release type description:

However, please note that:

Snapshot Publishing: The table is rebuilt and is passed through the entire file stream

Transactional publication: Update only the tables that need to be updated (such as adding deletes and modifications to the table); Pass only those changed data

Where a transactional publication and a transactional publication with a subscription need a table with a primary key;

Merge publication and transactional presentation with subscriptions add a field to a table

Such as:

All articles in publications that allow updatable subscriptions contain a uniqueidentifier column named "MSrepl_tran_version" that tracks changes made to replicated data. SQL Server adds this column to the published table that does not contain this column.

We choose to use Transactional publishing, and select one of the tables to test

Set schedule time: Click Change can be modified, this test use 1 minutes Automatic Update

Where the SQL Server login role must contain the role of sysadmin or the subscriber does not select SQL Server Agent; this time use Jdf_dev account for subscribers to connect

Enter the publication name 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 servers to synchronize file permissions files; We use FTP to synchronize

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

Select an ftp Snapshot

This time we need to create an FTP site If you do not use IIS to bring your own FTP can be used server-u , etc. FTP software operation

About using server-u in the next section

This way, we use IIS 's own FTP

First Open 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 go to the repldata file.

Set the port to 3347 and 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 flashftp for connection testing

Connection Successful

Return SQL just added subscription

Right-click Properties to select an ftp Snapshot

OK after right click New Publish Select View Snapshot state Select restart

This publisher configuration is complete

2: Accept Server Subscribers

Server configuration is Window server sqlserver2008 R2 English version

You need to create a new SQL alias because you want to connect to a 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 and password with Administrator privileges we will use the account previously used by the publisher server Jdf_dev

When you choose, you'll see a new release on the publisher side just now

Select a synchronization method

The first is the way the publisher's SQL Agent synchronizes push ( push ) and the publisher's initiative; This consumes more memory from the publisher .

The second approach is to synchronize the Publisher data in a way that the subscriber proactively requests data and pulls ( pull ) , which consumes more subscriber memory

See your server configuration and business needs above

This second method of use

Then the corresponding if using the second way to synchronize requires

You must turn on the SQL Server Agent service and start automatically

Back

Previous step 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 Publisher using the Jdf_dev account .

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