Best Practices for Transactional Replication

• Save the scripts: Once a publication and subscription are configured for transactional replication, store the script for later use.
• Make regular backups: Back up the publication, subscription, msdb, and distribution databases right after you add a new publication or schema changes are made on the publication. If you are using updatable subscriptions, back up the subscription database along with the msdb database on the Subscriber server. Periodically test the backup and restoration script. You should also back up the transaction log, since the Log Reader Agent transmits committed transactions from the log to the distribution database.
• Monitor disk space: For each database involved in transactional replication, ensure that you have enough space for the transaction log. The log file is not only used as a writeahead log, it is also being read sequentially by the Log Reader Agent to transmit committed transactions.
• Consider autogrowth mode: If you have situations where either the Log Reader Agent is not running or the distribution database is temporarily down, the transaction log will continue to grow until the transactions have been delivered. In such situations, it is better to set the log file to autogrowth mode.
• Consider sync with backup: Consider using the sync with backup option for the distribution database. This will ensure that the log file on the publication database is not truncated until the corresponding transactions have been backed up in the distribution database.
• Validate the subscriptions: After the backup and restoration of the databases used in transactional replication, you should validate the subscriptions using either the SSMS or the validate subscription script.
• Check primary keys: For tables used as publications in transactional replication, check to see that they have primary keys.
• Avoid truncating data: For articles involved in transactional replication, be careful not to truncate the data, as truncated operations are not logged. Those changes will not be replicated to the subscribing databases.
• Generate performance baselines: Develop a baseline for the performance of transactional replication on the server side for each of the server and hardware resources like processors, memory, and the configuration of the Distributor. You should also develop a performance baseline on the client side for each of the filtering options, the agent parameters, the subscription options, the locks, the transaction log, and the settings of tracer tokensusing the Replication Monitor. At the development stage of transactional replication, you should configure the publication design such that the business requirements are met. Ensure that you do not publish unwanted data, as this will have an impact on the distribution database, and keep the transactions to a minimum. You should also check that the right kind of indexes are created for the DML operations that you are going to use on the publication database. Keep row filters to a minimum, as they affect the throughput of the Log Reader Agent when it scans the transaction log of the publication database.
• Save scripts for profiles: Keep a script for the profiles for each of the agents used i transactional replication. Use tracer tokens: Since latency is an issue in transactional replication, monitor the latency using tracer tokens in the Replication Monitor.
• Use data-modeling tools to design the database: As with snapshot replication, design the database at least up to the third normal form. In designing the publication, add only the articles that you need.
• Plan your replication use: Transactional replication creates stored procedures on the database to perform DML operations, so do not use transactional replication as a stopgap solution to provide real-time data for third-party applications. Plan well ahead, because insert, update, and delete operations use these system-generated stored procedures to monitor the changes in the database and then transmit them to the subscribing servers.
• Plan for high-volume changes: If you are going to use a high volume of inserts or updates (batch operations), write a stored procedure that will perform these operations. The stored procedure will be executed once to deliver inserts or updates. Otherwise, a system-generated stored procedure will be executed for each insert or update operation, which will have an impact on performance, since there will be a considerable amount of throughput involved.
• Minimize the use of row filters: Use row filters for articles involved in transactional replication with caution, since the Log Reader Agent will have to apply the filter to each row affected by the update. The Log Reader Agent will have to scan the log, which will slow down the performance of transactional replication.
• Be careful updating keys: Do not update the primary keys for transactional replication with queued updating subscriptions. The primary keys are used to locate the records, and updating them can lead to conflicts.
• Monitor conflicts: If you need to set a conflict policy for updatable subscriptions, ensure that it meets the business requirements. Monitor the Conflict Policy Viewer to view the conflicts, and use them for troubleshooting purposes.
• Monitor delays in replication: If there are delays in the delivery of transactions to the subscribing servers, consider reinitializing the subscriptions. Use the Replication Monitor to monitor the Undistributed Commands tab, which will show you how many transactions are still waiting to be delivered from the distribution database to the subscription database.
• Reduce contention: As with snapshot replication, you can reduce contention by setting the READ_COMMITED_SNAPSHOT option on.
• Monitor performance: Use the SQL Trace function to trace the data and monitor the performance of transactional replication.
• Change agent parameters: After the configuration is finished, change the parameters for both the Log Reader and Distribution Agents. Set -HistoryVerboseLevel to 1 so that minimal history of the data is logged, and set -OutputVerboselevel to 0 so that only the error messages are logged. Increase the -MaxBcpThreads parameter for the snapshot agent to 2 so that the bcp utility can run faster using parallel processing.
• Monitor the Log Reader Agent’s latency: Use the System Monitor to monitor the Log Reader: Delivery Latency and Dist: Delivery Latency counters.
• Adjust the batch size for the Log Reader Agent: Change the ReadBatchSize parameter for the Log Reader Agent. The default is 500 transactions, and the Log Reader Agent will process this number of transactions from the publication database in one processing cycle until all of them have been read. You should change this number of transactions to suit our needs.
• Use the System Monitor: As with snapshot replication, use the System Monitor to trace the data for server-wide operations, and have separate counters for each of the operations that are used to monitor the trace.

Replication in SQL Server

[Two kinds of replication ]：

• Eager Replication：also known as synchronous replication
  
  
  Detail description as follow：The synchronous nature of data transfer in eager replication facilitates real-time data transfer, and in this situation it would be useful to use eager replication. However, eager replication is not a good choice for a remote or mobile environment, since it reduces update performance. Also, in a mobile environment the nodes are not always connected. It is also not supported in SQL Server 2008.
  
  
• Lazy Replication：also known as asynchronous replication
  
  
  Detail description as follow：With this type of replication, it is possible for two different sites to update the same data on the same destination site. This will lead to a conflict in the updating of the data. Such update conflicts need to be resolved in lazy replication, and this is done by associating timestamps with each of the transaction objects. Each object carries the timestamp associated with the previous update of that data. So when a transaction is sent to the destination site, it first checks to see whether the timestamp associated with the local copy of the replicated data matches the incoming transaction’s old timestamp for that data. Only if they match will the changes, including the transaction’s new timestamp, be applied. If the timestamps do not match at the initial stage, the updated transaction is rejected. SQL Server has a conflict-resolution viewer that deals with updates, inserts, and deletes.
  
  

[Replication in SQL Server]：

• SQL Server follows asynchronous (lazy) replication. It permits three different kinds of asynchronous replication: snapshot, transactional, and merge replication.
• Autonomy and data latency from lowest to highest are 1. distributed transactions、2. transactional replication、3. snapshot replication、4. merge replication.
• In case of snapshot replication, all tables to be published share a lock, so you cannot make any changes during the snapshot generation. In transactional replication there are no shared locks in place during the snapshot generation, so you can work unhindered while replication is generating the initial snapshot files in the background. This process, known as concurrent snapshot processing, is the default for transactional replication.
• Concurrent snapshot of transactional replication processing allows users to make changes while a snapshot is being generated. The Snapshot Agent places a lock on the publication tables at the start of the synchronization process, and an entry is made in the publication database’s transaction log. Until that time, the lock is held in place, but as soon as the entry is made in the log, the lock is released and you can start making data modifications again. Once the snapshot is completed, a second entry is made in the transaction log to mark the completion of the process. Any changes associated with the tables during the process are captured, and the Log Reader Agent forwards these transactions to the distribution database. It is worth pointing out at this stage that backups of replicated databases must be restored on the same server. Otherwise you will lose the replication settings and will have to re-create publications and subscriptions once the backup has been restored.

使用BCP或INSERT INTO的方式來搬移SSIS Package [SQL Server 2008]

匯出匯入SSIS Package，之前上課胡百敬老師有講過兩種方法來搬移，但是當時沒抄到，所以小試了一下，說明如下，只支援SQL Server 2008的搬移：

1. 用BCP IN與OUT的方式來搬移

Source匯出：bcp "SELECT * FROM msdb.dbo.sysssispackages WHERE name LIKE '%XXX_name%'" queryout C:\bcpout.dat -T –n Destination匯入：bcp msdb.dbo.sysssispackages in C:\bcpout.dat -T -n

2. 用INSERT INTO的方法，但要先建立好Link Server

INSERT INTO [LinkServerA].msdb.dbo.sysssispackages SELECT * FROM msdb.dbo.sysssispackages

如果有SSIS Folders的話，也要這樣弄，SSIS Folder相對應的Table為msdb.dbo.sysssispackagefolders

相關網址請參考：

• http://msdn.microsoft.com/en-us/library/aa174646(SQL.80).aspx
• http://msdn.microsoft.com/zh-tw/library/aa337544(SQL.90).aspx