Replication: a few Questions and Answers

Here are some interesting questions, and their respective answers, which may clarify a few things in regards to replication:

Best practices replication performance:

The first place to look is the SQL Server 2008 Books Online, especially the chapter "The System Has a Performance Issue" under Replication->Troubleshooting->Troubleshooting Concepts.
This chapter summarizes basic steps and also provides further links to a wealth of details. These chapters are based on experience from previous SQL Server versions and tests of the current version.

Best practice replication accounts:

Again the Books Online give all important details. See chapter "Security and Protection (Replication)" under the Replication topic for an exhaustive discussion.

VMWare supportability in replication:

This is the same as described in KB article 956893:
Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment
<https://support.microsoft.com/kb/956893/en-us>
In summary, if your environment is covered through this article and replication components can connect to their replica partners, replication is supported on virtualized environments.

Sharing distribution agents:

This feature is known as "independent agent" and can be configured through the publication properties for transactional replication. An independent agent is an agent that services only one subscription, whereas a shared agent services multiple subscriptions in a way that each Publisher database/Subscriber database pair has a single, shared agent. If multiple subscriptions using the same shared agent need to synchronize, by default they wait in a queue, and the shared agent services them one at a time. So this is only important if you have several publications in the same database, and more than one of them subscribe into the same subscriber database.

Main advantage is that latency is reduced when using independent agents, because the agent is ready whenever the subscription needs to be synchronized. Potential disadvantage is that more distribution processes are needed, which might be a problem if you have a lot of push subscriptions: each Windows server can only handle a specific number of system processes, so running several hundred distribution jobs at the Distributor could exhaust all system resources.

Merge replication always uses independent agents, so this choice can only be made for transactional replication.

XML: Replicating XSD:

[Is it possible to replicate the XSD associated to a XML column ? If yes, when changing the XSD, are the changes replicated to the subscriber ? (similar to the table structure changes being propagated to subscribers)]

Yes and no. This is again covered in the SQL Server 2008 Books Online:
Publishing Data and Database Objects
Limitations on Publishing Objects
- XML schema collections can be replicated but changes are not replicated after the initial snapshot.

So once the XML schema has been published and replicated, it can be changed on the publisher; but the new XSD won't be copied to the subscribers.

Subscribe without snapshot, but snapshot new tables:

[We want to be able to subscribe without snapshot ("subscriber does not need initialization"), but would like to change this option so that new tables (afterwards) send a differential snapshot to subscribers. ]

Regarding database backups:
This is not possible. The feature to initialize a subscriber through a backup has been greatly enhanced in SQL Server 2008 (Books Online: "Initializing a Transactional Subscription Without a Snapshot"). But this only applies to the initial synchronization. If you add articles at a later time, you need to run the snapshot agent so that the new articles can be transferred to the subscribers. From the internal design of this feature, I strongly doubt that it will be add this functionality even in a later version.

Another option would be that after adding a table, you drop the existing subscriptions and initialize them again from a fresh backup. This requires a lot of administrative overhead though, and is not practicable if you have a lot of subscribers.

Regarding automatic initialisation of a new article for nosync subscriptions:
If you have a subscription defined with "replication support only" (was: "subscriber already has schema and data" in SQL 2000), no snapshot data will be transferred to the subscribers, except for replication procedures and metadata.
So if you add an article to the publisher, you will have to initialize this article by yourself on the subscribers: create the table according to the article definition, and transfer the data manually.

If you add the table and article on the publisher but NOT on the subscribers, and then change data for this article, the distribution agent will fail until you also create the subscriber table manually.

This might be possible if you have only a few subscribers or only a few tables to add; otherwise it will be a major administrative issue. It might be a better idea to add the manual, "replication support only" subscription only in an emergency, and replace it with a regular subscription at a later time during a maintenance window.
For manually initializing a new article, you still can use some of the snapshot functionality: let the snapshot agent create the scripts and the BCP data, and use those files yourself for creating the subscriber tables.

Replicate a column under a different name or position:

For Peer-to-Peer replication, the schema must be identical on all peers in the topology.

For simple transactional replication, my recommendation is to keep both name and position identical to the definition of the transactional article. The name might not be critical, but the stored procedures that apply changes to the subscribers won't know about the change of column order. Changed columns are identified by their position for performance reasons, so if the order is different, changes will be applied to the wrong columns. Different name might become critical if the article's @status option is set to 8 or 24 (=> includes the column name in INSERT statements).

If this is a major requirement, consider publishing both the table and a view based on that table. You might then change the view definition on the subscriber (e.g. through a post-snapshot script) to match the required names and ordinal positions. A variation would be to not publish the view, but simply create it through the post-snapshot script.
Note that the "sp_addarticle" procedure distinguishes between @source_table and @destination_table. You could e.g. set @source_table='Table1', @destination_table='Table1_table', and then create a subscriber view named "Table1" with different column names and ordinal positions.

The order of the columns will always be expected to be the same as defined in the article. If you define your own stored procedures, the order of the parameters that are passed to this procedure will remain equal to the actual order in the article. There is no way to change the order in the snapshot files. My recommendation is to keep it as simple as possible, and not to change the column order and names. It will give you more flexibility and options in case of a failure. Examples are: data validation, using the tablediff tool for comparing data on publisher and subscriber, using the default snapshot files for creating subscriber tables manually. The easier the configuration is, the more maintainable it is. Complexity always means more chances to fail and possibilities for slow performance.

In SQL Server 2000, it was possible to define an article together with a DTS package ("transformable subscription"). This feature has been removed in SQL Server 2005 and beyond, mainly because of performance issues. It also was a "hot spot" for replication failure, because a slight change in either table definition or DTS package could break the complete replication topology.

Credentials prerequisites:

What are the credential prerequisites for the different SQL services (Database engine, Agent, etc.)?
What are the best practices to define the accounts (Local, AD, proxies, user rights, etc.) if we use:- SMTP servers- FTP replication- SQLCMD
What happens if there's an AD issue (ie: virus)

The Books Online chapter "Security and Protection (Replication)" under the Replication topic will answer most questions regarding the replication agents and objects.
The recommended method is to use Windows Authentication as far as possible, and to avoid that the replication agents impersonate the account of the SQL Server Agent service.

If subscriber servers are not part of the company network, e.g. stand-alone machines in remote locations, it might be necessary to use SQL Server authentication. In this case it is recommended to use SSL protocol encryption through certificates to avoid security risks.

In case of a network issue where no domain controller is reachable, the replication agents will not be able to authenticate themselves, so replication will not work. If you have remote locations that are part of the company domain, consider placing a secondary domain controller at each site. If this kind of outage needs to be avoided at all cost, consider using local accounts instead: create the same accounts with identical names and passwords at each machine. Those local accounts can then be authenticated by each other, because non-domain accounts will be checked simply for their account name and the password, but not the originating machine name ("pass-through authentication").

There is not much of a difference between "normal" connections and connections to/from SMTP/FTP/SQLCMD. You need a user account that has sufficient permissions to start the services. And the accounts used by the services need to have sufficient permissions to access the necessary resources. For example, the distribution/merge agent would need read access to the FTP folder, whereas the snapshot agent would need write permissions to the folder that is shared through FTP.