Setting up transactional replication using T-SQL
Step 1: Set up a shared folder for snapshots.
Step 2: Configure the distributor and publisher:
use master
exec sp_adddistributor @distributor = N'SSLMATTB2'
, @password = N''
GO
exec sp_adddistributiondb @database = N'distribution'
, @data_folder = N'C:\MSSQL\SQLData'
, @log_folder = N'C:\MSSQL\SQLLogs'
, @log_file_size = 2
, @min_distretention = 0
, @max_distretention = 72
, @history_retention = 48
, @security_mode = 1
GO
use [distribution]
if (not exists (
select *
from sysobjects
where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (
select *
from ::fn_listextendedproperty('SnapshotFolder'
, 'user'
, 'dbo'
, 'table'
, 'UIProperties'
, null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder'
, N'C:\MSSQL\SQL_Share'
, 'user'
, dbo
, 'table'
, 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder'
, N'C:\MSSQL\SQL_Share'
, 'user'
, dbo
, 'table'
, 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'sslmattb2'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'C:\MSSQL\SQL_Share'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO
Step 3: Configure a database for replication, create a publication, and add an article:
use [AdventureWorks2008]
exec sp_replicationdboption @dbname = N'AdventureWorks2008'
, @optname = N'publish'
, @value = N'true'
GO
use [AdventureWorks2008]
exec sp_addpublication @publication = N'AW_products'
, @sync_method = N'concurrent'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'false'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'false'
, @allow_sync_tran = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'AW_products'
, @frequency_type = 1
, @frequency_interval = 1
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 8
, @frequency_subday_interval = 1
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 1
use [AdventureWorks2008]
exec sp_addarticle @publication = N'AW_products'
, @article = N'Product'
, @source_owner = N'Production'
, @source_object = N'Product'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'Product'
, @destination_owner = N'Production'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_ProductionProduct'
, @del_cmd = N'CALL sp_MSdel_ProductionProduct'
, @upd_cmd = N'SCALL sp_MSupd_ProductionProduct'
GO
Step 4: Backup the database on the publisher and restore to the subscription instance.
Step 5: Configure a subscription (because I am creating a push subscription this script should be run on the publisher).
use [AdventureWorks2008]
exec sp_addsubscription @publication = N'AW_pub'
, @subscriber = N'sslmattb2\INST2'
, @destination_db = N'AW_products'
, @subscription_type = N'Push'
, @sync_type = N'automatic'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'AW_pub'
, @subscriber = N'sslmattb2\INST2'
, @subscriber_db = N'AW_products'
, @job_login = N'NT AUTHORITY\SYSTEM'
, @job_password = null
, @subscriber_security_mode = 1
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20120514
, @active_end_date = 99991231
, @enabled_for_syncmgr = N'False'
, @dts_package_location = N'Distributor'
GO
Transactional Replication is what we used to call "tightly coupled" replication, or
realtime replication. Essentially, transactional replication uses the Microsoft
Distributed Transaction Coordinator, and it requires all of the servers involved in
replication to complete their transactions before they are finalized. In other words,
if any of the servers that are involved in replication are not available, no transactions
can complete. The upside is that there can never be any conflicts because all of the
servers involved in the replication are always consistent with each other.
Merge replication is a little bit looser than that. If a server isn't available, the updates
to that server are queued up, and when the server becomes available it will receive all
of its updates. Unfortunately, if two users update a record at the same time, merge
replication will result in conflicts which will need to be manually resolved.
Either one of these types of replication is valid when there are multiple servers which may update
a database. The differences are in overhead and reliability. Transactional replication
will have a higher overhead in terms of CPU utilization, network traffic, disk time,
and record locks. Merge replication has more administrative overhead, because someone
has to handle conflict resolution.
So, when do you use Transactional Replication? Well, if you can deal with the overhead
(minimal if you are careful) and if you can guarantee
that all of the servers will always be able to talk to each other, then you can use
Transactional Replication. Transactional replication shouldn't be used for failover.
Why not? Well, if one of the servers involved in the replication becomes disabled because
of a hardware issue (It's never a software issue, after all, Windows 2000 never crashes,
right?) then there won't be any writes allowed in the data involved in replication even
on the server that is still online. A good example of a system that would work well with
transactional replication is an accounting system that is used to generate a lot of
reports with minimal updates. Then the reporting load could be balanced across the two
boxes and the data could be guaranteed consistent.
Merge replication is great for doing things like load balancing for geographically redundant
sites. For example, if you have an Internet order taking system, having multiple SQL
Servers avaiable to replicate those transactions around is a good idea in case there is a
fibre cut or some such nonsense. Then when full service is restored the servers will
probably come back online with no issues except for a manual conflicts to resolve.
Since it is an order taking system, chances are pretty good that the only conflicts
will be related to orders that were
being changed when the server failed.
Transactional
Replication is what we used to call "tightly coupled" replication, or
realtime replication. Essentially, transactional replication uses the
Microsoft Distributed Transaction Coordinator, and it requires all of
the servers involved in replication to complete their transactions
before they are finalized. In other words, if any of the servers that
are involved in replication are not available, no transactions can
complete. The upside is that there can never be any conflicts because
all of the servers involved in the replication are always consistent
with each other.
Merge replication is a little bit looser than
that. If a server isn't available, the updates to that server are
queued up, and when the server becomes available it will receive all of
its updates. Unfortunately, if two users update a record at the same
time, merge replication will result in conflicts which will need to be
manually resolved.
Either one of these types of replication is
valid when there are multiple servers which may update a database. The
differences are in overhead and reliability. Transactional replication
will have a higher overhead in terms of CPU utilization, network
traffic, disk time, and record locks. Merge replication has more
administrative overhead, because someone has to handle conflict
resolution.
So, when do you use Transactional Replication?
Well, if you can deal with the overhead (minimal if you are careful) and
if you can guarantee that all of the servers will always be able to
talk to each other, then you can use Transactional Replication.
Transactional replication shouldn't be used for failover. Why not?
Well, if one of the servers involved in the replication becomes disabled
because of a hardware issue (It's never a software issue, after all,
Windows 2000 never crashes, right?) then there won't be any writes
allowed in the data involved in replication even on the server that is
still online. A good example of a system that would work well with
transactional replication is an accounting system that is used to
generate a lot of reports with minimal updates. Then the reporting load
could be balanced across the two boxes and the data could be guaranteed
consistent.
Merge replication is great for doing things like
load balancing for geographically redundant sites. For example, if you
have an Internet order taking system, having multiple SQL Servers
avaiable to replicate those transactions around is a good idea in case
there is a fibre cut or some such nonsense. Then when full service is
restored the servers will probably come back online with no issues
except for a manual conflicts to resolve. Since it is an order taking
system, chances are pretty good that the only conflicts will be related
to orders that were being changed when the server failed. - See more at:
http://www.sqlteam.com/article/choosing-a-replication-type#sthash.3bzrWpnd.dpuf
Transactional
Replication is what we used to call "tightly coupled" replication, or
realtime replication. Essentially, transactional replication uses the
Microsoft Distributed Transaction Coordinator, and it requires all of
the servers involved in replication to complete their transactions
before they are finalized. In other words, if any of the servers that
are involved in replication are not available, no transactions can
complete. The upside is that there can never be any conflicts because
all of the servers involved in the replication are always consistent
with each other.
Merge replication is a little bit looser than
that. If a server isn't available, the updates to that server are
queued up, and when the server becomes available it will receive all of
its updates. Unfortunately, if two users update a record at the same
time, merge replication will result in conflicts which will need to be
manually resolved.
Either one of these types of replication is
valid when there are multiple servers which may update a database. The
differences are in overhead and reliability. Transactional replication
will have a higher overhead in terms of CPU utilization, network
traffic, disk time, and record locks. Merge replication has more
administrative overhead, because someone has to handle conflict
resolution.
So, when do you use Transactional Replication?
Well, if you can deal with the overhead (minimal if you are careful) and
if you can guarantee that all of the servers will always be able to
talk to each other, then you can use Transactional Replication.
Transactional replication shouldn't be used for failover. Why not?
Well, if one of the servers involved in the replication becomes disabled
because of a hardware issue (It's never a software issue, after all,
Windows 2000 never crashes, right?) then there won't be any writes
allowed in the data involved in replication even on the server that is
still online. A good example of a system that would work well with
transactional replication is an accounting system that is used to
generate a lot of reports with minimal updates. Then the reporting load
could be balanced across the two boxes and the data could be guaranteed
consistent.
Merge replication is great for doing things like
load balancing for geographically redundant sites. For example, if you
have an Internet order taking system, having multiple SQL Servers
avaiable to replicate those transactions around is a good idea in case
there is a fibre cut or some such nonsense. Then when full service is
restored the servers will probably come back online with no issues
except for a manual conflicts to resolve. Since it is an order taking
system, chances are pretty good that the only conflicts will be related
to orders that were being changed when the server failed. - See more at:
http://www.sqlteam.com/article/choosing-a-replication-type#sthash.3bzrWpnd.dpuf
Replication Optimization Tips
1. Avoid publishing unnecessary data.
Try to restrict the amount of published data. This can result in significant performance benefits as
SQL Server will publish only the amount of data required. At the same time, this can reduce network
traffic and boost the overall replication performance.
2. Place the published database log and distribution database log on separate disk drives.
Because logging is more write-intensive, it is important that the disk arrays containing the SQL
Server log files have sufficient disk I/O performance. Separating the logs onto two drives ensures
high disk I/O performance.
3. Do not configure the distribution database to expand or shrink automatically.
Microsoft recommends to set a fixed size for the distribution database. Setting a database to
automatically grow results in some performance degradation; thus, you should set a reasonable
initial size of the distribution database.
4. Place the distribution component of replication on its own dedicated server.
This topology is used for performance reasons when the level of replication activity increases
or the server resources become constrained. It reduces Publisher loading, but it increases
overall network traffic. This topology requires separate Microsoft SQL Server installations --
one for the Publisher and one for the Distributor.
5. Run the Snapshot Agent as infrequently as possible.
The Snapshot Agent bulk copies data from the Publisher to the Distributor, which results
in some performance degradation. Try to schedule the agent during CPU idle time and slow
production periods to minimize performance loss.
6. Avoid using continuous replication.
If possible, schedule replication to occur at regular intervals instead of using continuous replication.
7. Avoid replicating text, ntext and image columns.
These data types require more storage space and processing than other column data types.
8. Replicate the execution of stored procedures when a large number of rows are affected.
For example, instead of replicating a very large number of insert, update and delete statements,
you can create a stored procedure which contains all of these statements. Replicate to subscriber
only the execution of this stored procedure. This can reduce network traffic and boost overall
replication performance.
9. Set the "Maximize Throughput for Network Applications" option.
This can increase SQL Server performance as Windows NT will allocate more RAM to SQL Server
than to its file cache. To set this option, do the following:
Double-click the Network icon in Control Panel.
Click the Services tab.
Click Server to select it, and then click the Properties button.
Click Maximize Throughput for Network Applications, and then click OK.
Restart the computer.
10. Specify the 'min server memory' option.
This option is used to set a minimum amount of memory allocated to SQL Server. If the server
is a remote Distributor or a combined Publisher and Distributor, Microsoft recommends that
the 'min server memory' option be set to at least 16 MB of memory to avoid low memory
availability during replication activities, .
You can also change these options when SQL Server works on the same computer with other
applications. In this case, the 'min server memory' option is used to allow SQL Server to
work when other applications attempt to use all available memory.
11. Try to enable pull or anonymous subscriptions to increase the Distributor performance.
This can increase the Distributor performance, because the Distribution Agent processing
will be moved from the Distributor to Subscribers.
12. Increase the MaxBcpThreads property of the Snapshot Agent.
This property specifies the number of bulk copy operations that can be performed in parallel.
By increasing this value, bulk copy operations can run faster, because they will be performed
in parallel.
To increase the MaxBcpThreads value in the Snapshot Agent profile, you can do the following:
Run SQL Server Enterprise Manager.
Expand a server group, then expand a server.
Expand Replication Monitor, then expand the Agents and click the Snapshot Agents folder.
Right-click appropriate publication and select Agent Profiles...
Click the New Profile button to create the new profile with the appropriate MaxBcpThreads value.
Choose the newly created profile.
Note. Do not set this property too high -- it can result in some performance degradation,
because SQL Server will have to spend extra time managing the extra threads. First increase
this property to 2 and continue monitoring performance.
13. Set the OutputVerboseLevel property of the Distribution Agent, the Log Reader Agent,
the Merge Agent, and the Snapshot Agent to 0.
This property specifies whether the output should be verbose. There are three available values:
0 - only error messages are printed
1 - all of the progress report messages are printed
2 - all error messages and progress report messages are printed
The default value is 2. You can increase performance by printed only error messages.
To set the OutputVerboseLevel value to 0, you can do the following:
Run SQL Server Enterprise Manager.
Expand a server group, then expand a server.
Expand Replication Monitor, then expand the Agents and click the appropriate agent folder.
Right-click appropriate publication and select Agent Properties...
On the Steps tab, double-click the Run agent step, and then add the -OutputVerboseLevel
0 in the Command text box.
14. You can minimize the performance effect of history logging by selecting 1 for the
HistoryVerboseLevel property of the Distribution Agent, the Log Reader Agent, the Merge Agent,
and the Snapshot Agent.
This property specifies the amount of history logged during distribution operation
(for a Distribution Agent), during a log reader operation (for a Log Reader Agent),
during a merge operation (for a Merge Agent), or during a snapshot operation (for a Snapshot Agent).
To set the HistoryVerboseLevel value to 1, you can do the following:
Run SQL Server Enterprise Manager.
Expand a server group, then expand a server.
Expand Replication Monitor, then expand the Agents and click the appropriate agent folder.
Right-click appropriate publication and select Agent Properties...
On the Steps tab, double-click the Run agent step, and then add the -HistoryVerboseLevel
1 in the Command text box.
15. If you work with SQL Server 2000, consider using the -UseInprocLoader agent property.
If this option was set, the in-process BULK INSERT command will be used when applying snapshot
files to the Subscriber. You cannot use this property with character mode bcp, this property
cannot be used by OLE DB or ODBC Subscribers.
To set the UseInprocLoader property, you can do the following:
Run SQL Server Enterprise Manager.
Expand a server group, then expand a server.
Expand Replication Monitor, then expand the Agents and click the Distribution Agents or
Merge Agents folder.
Right-click appropriate publication and select Agent Properties...
On the Steps tab, double-click the subscription agent step, and then add the -UseInprocLoader
property in the Command text box.
16. Increase the Log Reader Agent ReadBatchSize parameter.
This parameter specifies the maximum number of transactions read out of the transaction log
of the publishing database. The default value is 500. This option should be used when a large
number of transactions are written to a publishing database but only a small subset of those
are marked for replication.
17. If you work with transactional replication, increase the Distribution Agent CommitBatchSize
parameter.
This parameter specifies the number of transactions to be issued to the Subscriber before a
COMMIT statement is issued. The default value is 100.
18. Create an index on each of the columns used in the filter's WHERE clause.
If you do not use indexes on the columns used in filters, then SQL Server must perform a table scan.
29. If you work with merge replication, use static instead of dynamic filters.
Because SQL Server requires more overhead to process the dynamic filters than static filters,
for best performance you should use static filters whenever possible.
Comments
Post a Comment