Wednesday, 30 October 2013

Issue with BLOB rows



Issue with BLOB rows-
Method 1 Set the SubscriptionStreams parameter of the Distribution Agent to a value of 1. Method 2 You can set the OleDbStreamThreshold parameter of the Distribution Agent to a value that is greater than the largest data size for binary large object (BLOB) columns that have to be replicated. Then, the Distribution Agent will not bind binary large object (BLOB) data as a stream. Starting from SQL Server 2008, binary large object (BLOB) data is bound as a stream when the data size of the binary large object (BLOB) data is less than the value of the OleDbStreamThreshold parameter. This behavior is true by default.

USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size';
GO

-- OUTPUT:
-- name                    minimum  maximum     config_value run_value
-- ----------------------- -------- ----------- ------------ -----------
-- max text repl size (B)  -1       2147483647  65536        65536
Output clearly shows maximum value of 65536 bytes. Now, Execute the below set of statements to resolve the issue.
-- For SQL Server 2005
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', 2147483647 ; 
GO
RECONFIGURE;
GO

-- For SQL Server 2008 (and 2008 R2)
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', -1 ;
GO
RECONFIGURE;
GO

Tuesday, 15 October 2013

SQL SERVER REPLICATION

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.