Database replication: Modify table structure, new tables, new stored procedures are replicated to subscribers

SQL Server High Availability database replication: Modifies table structure, new tables, new stored procedures are replicated to subscribers?

After you have configured database replication on SQL Server:

1. If you add a field to the table, will it be copied to the subscriber? What if 1 fields are deleted?

2, if the newly created 1 tables, will not be synchronized past it?

3. If a new stored procedure is created, will the past be synchronized?

This article will be an experiment to answer the above questions.

1. First, you need to configure database replication.

For the sake of simplicity, there are 2 instances of SQL Server 2008r2 on the machine.

Two examples are: MSSQLSERVER, sql2008r2.

At configuration time, MSSQLServer is the publisher, and SQL2008R2 is the Subscriber and is configured as a pull subscription.

Detailed configuration method, you can refer to this article, the difference is that the following article is configured as a push subscription, what do not understand, you can leave a message to me:

SQL Server High Availability database replication: Copy data from table A to table B in SQL Server 2008r2 with database replication

Run the following code on two instances to create the database and table:

Create DATABASE Wgouse wgocreate table www (id int primary KEY, V varchar (10))

The following configuration is complete:

Publishing Server

Subscriber server

2, for the published table www, add 1 columns, will be automatically synchronized?

Run the following code at the Publisher:

ALTER TABLE WWWADD v2 varchar (10)

From the subscriber's diagram, there is no new v2 column on the WWW table, stating that the past is not synchronized, so what should I do?

At this point, try to "reinitialize the subscription":

Here for reinitialization, success. If this is not successful, you can disable the Distribution Agent by following the prompts in the error window that pops up, and then reinitialize the subscription again:

Look at the subscriber again, discover the WWW table, and already have the V2 column:

Next, at the Publisher, add a field, and discover that the Subscriber also automatically adds this field without having to reinitialize:

ALTER TABLE WWWADD v3 datetime

We then delete the V3 field at the publisher, and the V3 field is automatically deleted at the Subscriber.

But sometimes, there will be an error, this time, you can try again, you can generally delete this field.

3, the new creation of 1 tables, will not be synchronized past it?

This depends on the table, if there is no primary key, then there is no way to replicate to the subscriber. If you have a table with a primary key, you can copy it.

Create 2 tables at the publisher first:

--No primary key select * into Abcfrom sys.objects--has primary key CREATE TABLE abc123 (a int primary key,b varchar (), C datetime)

We found that after creating a table at the publisher, unlike new and deleted columns, it is not automatically copied to the Subscriber.

In fact, very simple, just need to find the release, then right-click, select "Properties":

In the pop-up box, select "Items" on the left, "show only selected items in list" On the right, and then tick the abc123 table, that is, let SQL Server also copy the table to the Subscriber, or SQL Server does not know whether it needs to be complex:

However, it is important to note that there is a red circle on the ABC table because there is no primary key, which means that it cannot be copied.

Looking at the Subscriber again, the table is synchronized and there are 3 stored procedures for each table being synchronized, corresponding to insert, UPDATE, delete, respectively:

If there is no synchronization, you need to re-initialize it with the method mentioned above.

4. If a new stored procedure is created, will the past be synchronized?

In fact, this is the same as the new table, you need to set the properties of the publication "Project", to publish the stored procedures, tick on it.

What if you want to modify the stored procedure?

This is usually automatically synchronized, so after the change, the automatic synchronization to the Subscriber.