SQL's Corner

Replication: Stream_Blob_Columns enabled by default when Filestream is enabled

clsilva — Tue, 10 Jan 2012 10:11:43 GMT

http://support.microsoft.com/kb/2647118

REPLICATION: Oracle publication triggers sends data for non-published columns when replicating with Microsoft SQL Server

clsilva — Tue, 10 Jan 2012 09:20:18 GMT

http://support.microsoft.com/kb/2310152

REPLICATION: Load Test Platform for Merge Replication

clsilva — Thu, 15 Dec 2011 10:42:29 GMT

Zen4Sync is all about providing an Orchestration and Load Test platform able to validate any SQL Server Merge Replication based Architecture.

http://zen4sync.codeplex.com/

Replication: Watch out for STREAM_BLOB_COLUMNS when setting up replication on your SQL 2008 + server

clsilva — Mon, 31 Oct 2011 10:19:00 GMT

When setting up merge replication on your SQL 2008 + server, please be careful with the parameter STREAM_BLOB_COLUMN.

By default, this option is set to FALSE, as explained here in BOL:

[ @stream_blob_columns= ] 'stream_blob_columns'
Specifies that a data stream optimization be used when replicating binary large object columns. stream_blob_columns is nvarchar(5), with a default of FALSE. true means that the optimization will be attempted.

However one should be careful with the NOTE information (see below):

When filestream is enabled - @schema_option = 0x000000010C034FD1 (100000000 --> Use this option to replicate the FILESTREAM attribute if it is specified on varbinary(max) columns. Do not specify this option if you are replicating tables to SQL Server 2005 Subscribers. Replicating tables that have FILESTREAM columns to SQL Server 2000 Subscribers is not supported, regardless of how this schema option is set. See related option 0x800000000) - the stream_blob_column option is also enabled and a warning is generated:

Warning: To allow replication of FILESTREAM data to perform optimally and reduce memory utilization, the 'stream_blob_columns' property has been set to 'true'. To force FILESTREAM table articles to not use blob streaming, use sp_changemergearticle to set 'stream_blob_columns' to 'false'.

Should this option NOT be necessary (you will not be transfering data greater than 16K) than you should disable the option in order to avoid possible performance issues.

Important
Enabling this memory optimization may reduce the performance of the Merge Agent during synchronization. This option should only be used when replicating columns that contain megabytes of data.

Important

Enabling this memory optimization may reduce the performance of the Merge Agent during

synchronization. This option should only be used when replicating columns that contain

megabytes of data.

Note
Certain merge replication functionalities, such as logical records, can still prevent the stream optimization from being used when replicating binary large objects even with stream_blob_columns set to true.

Note

Certain merge replication functionalities, such as logical records, can still prevent the stream

optimization from being used when replicating binary large objects even with

stream_blob_columns set to true.

Replication: If sql_variant column includes a value with new datetime type, the value is replicated as base type nvarchar even if schema option 0x200000000 is not set

clsilva — Thu, 20 Oct 2011 09:01:00 GMT

Problem:

If sql_variant column includes a value with new datetime type, the value is replicated as base type nvarchar even if schema option 0x200000000 is not set during the application of the SNAPSHOT (only).

After the snapshot has been applied, if you execute the command to verify the property of the sql_variant column:

SELECT SQL_VARIANT_PROPERTY(column_name,'BaseType'), * FROM table_name

You will see that on the Publisher it shows as (example) DATE while on the subscriber its NVARCHAR

Cause:

Its an issue that invovles both the snapshot agent and SNAC10.

Solution/Workaround

THE WORKAROUND THAT WAS POSTED HERE BEFORE HAS BEEN REMOVED AS IT HAD OTHER IMPLICATIONS AND IS NOT RECOMMENDED

Microsoft is still looking into this however the solution to this issue is to verify if sql_variant is the right datatype to use or if there is anything way (within the app code) to work around the basetype difference.

Replication: XML data loss occurs when Merge agent fails with Violation of Foreign Key

clsilva — Wed, 28 Sep 2011 08:04:43 GMT

Problem:

Consider the following scenario:

You use a merge publication operation to replicate XML data in Microsoft SQL Server 2005, in Microsoft SQL Server 2008, or in Microsoft SQL Server 2008 R2.
A foreign key violation error occurs in the same merge replication batch.

In this scenario, you may find the merge replication fails and the XML data is partly replicated or lost. Additionally, an error message that resembles the following is logged in the SQL Server error log if this issue occurs:

The merge process could not replicate one or more Insert statements to the Publisher. A Stored Procedure failed to execute.

Cause:

This issue occurs because the merge replication stays in an error state and cannot handle a sequential stream of the XML data when a Foreign Key violation error occurs.

Solution:

http://support.microsoft.com/kb/2582285

Replication: What is the difference between Database Compatibility Mode and Publication Compatibility Mode

clsilva — Wed, 28 Sep 2011 08:00:21 GMT

Database Compatibility Mode:

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version
differences in the behaviors that are controlled by the relevant compatibility-level setting.

In this link, you see some examples of how the database compt mode alters the way SQL “treats” the database, for example:

With a DB COMPT LEVEL of 90 --> SET XACT_ABORT OFF is not allowed inside a trigger.

http://msdn.microsoft.com/en-us/library/bb510680.aspx

Publication Compatibility Mode:

Merge replication uses the publication compatibility level to determine which features can be used by publications in a given database. The values range from 80RTM (SQL Server 2000 that has no service packs installed) to 100RTM for SQL Server 2008.

Examples of how the publication compatibility level affects the publication:

The following features require a compatibility level of 90RTM or higher:

Logical
records
Subscriber
upload options.
Nonoverlapping
partitions
Business
logic handlers
Schema
changes that use ALTER <OBJECT> statements

http://msdn.microsoft.com/en-us/library/ms143241.aspx

So basically:

DB compatibility mode affects the DATABASE (and what you can do using this database)

Publication Compatibility mode affects the PUBLICATION (and what you can do in replication)

Replication: Snapshot agent execution causes system Available Memory to drop to 0 and possibly fail

clsilva — Wed, 28 Sep 2011 07:53:33 GMT

Problem:

While running the snapshot agent, the available memory of the server goes down to 0 and snapshot may even fail. Should the snapshot complete, the memory is released very slowly.

Cause:

This is because in the current design (SQL 2005 and higher), snapshot agent uses SNAC BCP provider which uses memory mapped files for BCP out data and does not flush the data based on a threshold.

Solution:

http://support.microsoft.com/kb/2582285

Replication: Error "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels" generated when altering published table columns

clsilva — Mon, 08 Aug 2011 14:42:00 GMT

Problem:

Error "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels" generated when altering published table columns in a transactional replication.

Cause:

Unable to alter columns on Published database if using any isolation level besides the READ COMMITTED or REPEATABLE READ isolation level.

READPAST hint is not compatible with READ UNCOMMITTED isolation level, but only with READ COMMITTED and REPEATABLE READ isolation levels.

READPAST hint is used in internal stored procedures in order to resolve issues within replication.

Solution/Workaround:

Other options (other isolations levels) that reduce locking:

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications
using either:

The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
The SNAPSHOT isolation level.

http://msdn.microsoft.com/en-us/library/ms173763.aspx

An alternative would be to create a stored procedure that performs the alter statement, in which we specify the isolation level necessary, and call that stored procedure when the alterations to the columns is needed.

Replication: Merge Performance Tip

clsilva — Mon, 08 Aug 2011 14:19:39 GMT

Problem:

Publisher: SQL Server 2008

Subscriber: SQL Server Compact 3.5

Merge Replication

Performance issue when using keep_partition_changes or precomputed partitions

Cause:

There could be many causes to this and analysis would have to be done however sometimes it may be due to missing indexes.

Solution/Workaround:

In a test environment, use the following indexes and verify performance:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[MSmerge_current_partition_mappings] ([partition_id])

INCLUDE ([rowguid])

//--//

-- changed and new indexes on system tables

DROP INDEX nc5MSmerge_contents ON MSmerge_contents

CREATE NONCLUSTERED INDEX [NCL_marker] ON [dbo].[MSmerge_contents] ([marker], rowguid)

CREATE NONCLUSTERED INDEX [NCL_partition_id_rowguid] ON [dbo].MSmerge_current_partition_mappings (partition_id, [rowguid])

-- update statistics on system tables

update statistics MSmerge_current_partition_mappings with fullscan

update statistics MSmerge_contents with fullscan

update statistics MSmerge_partition_groups with fullscan

-- update statistics on all tables involved in join filters

update statistics <table name> with fullscan

Replication: Fake Conflicts?

clsilva — Wed, 29 Jun 2011 07:47:00 GMT

One problem that is hard to resolve / understand is fake conflicts within a merge replication topology.

Looking at a scenario in which fake conflicts were occurring, we saw that following:

Our affected subscriber is synchronizing with 2 publications that have retention=14. The publisher also has an additional publication with retention=30. All the publications are on the same published database.

For the affected rowguids, we noticed that the MSmerge_contents entry still exists on the publisher, but was recently cleaned up on the subscriber.

Example of what we saw:

For a column-tracking article:

- common colv1 before cleanup shows that columns 1, 2, 3 had been updated previously

- clean-up removes the MSmerge_contents row on sub but not on pub

- sub updates the row, thus inserting a new row into MSmerge_contents

- updated columns are 2 and 3

- synchronisation detects a mismatch, deduces a conflict, and the subscriber loses its data.

Reason for this issue in this scenario is:

The subscriber cleanup takes the 14 days limit, whereas the publisher clean-up uses the 30 day limit.

http://msdn.microsoft.com/en-us/library/ms151778.aspx

The default retention period for publications is 14 days. If an article belongs to several publications, there might be different retention periods. In that situation, the longest retention period is used to determine the earliest possible time that clean-up can occur.

Resolution in this case was to have all the publications set with the same retention period

Note to all Readers

clsilva — Wed, 08 Jun 2011 15:30:43 GMT

Seems like there was an issue with the comments for this blog - I wasn’t getting notified and hence never got to respond.

My apologies to all those that commented and never got a reply.

If you do have a comment, please do not hesitate to e-mail me by selecting "e-mail Blog Author"

Thanks!

REPLICATION: My distribution agent generates a dump when replicating with my Oracle Subscriber

clsilva — Wed, 08 Jun 2011 15:24:19 GMT

PROBLEM:

I have a SQL Server 2005 replicating with an Oracle 11 subscriber.

After installing the OLEDB driver for Oracle 11, a minidump is created every time the distributor tries to connect to Oracle: Distrib.exe crashes with STACK_OVERFLOW exception.

The issue occurs while initializing the subscriber therefore as soon as it tries to connect to Oracle.

In addition to the information mentioned above, I also get the following error when i execute a query against the linked server to Oracle:

SELECT <all column names> FROM [xxxxx]..[xxxxxx].[MSREPL7]

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx" supplied inconsistent metadata for a column.
The column "column_name" (compile-time ordinal 1) of object ""xxxxxx"."MSREPL7"" was reported to have a "LENGTH" of 128 at compile time and 256 at run time.

CAUSE:

This is caused by the fact that the distrib.exe stack size is 256 KB - hence the stack overflow.

WORKAROUND / SOLUTION:

Only known supported solution is to upgrade to SQL Server 2008: SQL Server 2008's distrib stack is 1 MB.

Replication: The row was not found at the Subscriber when applying the replicated command - Error 20598

clsilva — Wed, 08 Jun 2011 09:05:00 GMT

Problem:

I have a transactional replication topology that consists of SQL 2005 and SQL 2000.

I managed to generate the snapshot but when I try to apply it on the subscribers, I get the following error:

Error number 20598: "The row was not found at the Subscriber when applying the replicated command".

Using “Skip Error” doesn’t resolve the issue.

Additional information:

My user tables are heavily used/modified. When the publisher is in single user more, I do not have this issue.

Cause:

I had “READ_COMMITTED_SNAPSHOT ON” and “ALLOW_SNAPSHOT_ISOLATION ON” enabled on the database

Workaround/Solution:

I used @sync_method =’concurrent’

http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx

Replication: Unable to Synchronize subscribers via Web Synchronization

clsilva — Thu, 12 May 2011 12:06:41 GMT

Problem:

The upload message to be sent to Publisher 'XXX' is being generated

The merge process is using Exchange ID 'XXXXX' for this web synchronization session.

No data needed to be merged.

Request message generated, now making it ready for upload.

Upload request size is 1024 bytes.

Uploaded a total of 1 chunks.

The request message was sent to 'https://xxxxx/replisapi.dll'

Downloaded a total of 100 chunks.

Downloaded a total of 200 chunks.

Downloaded a total of 300 chunks.

Downloaded a total of 400 chunks.

Downloaded a total of 500 chunks.

Downloaded a total of 600 chunks.

Downloaded a total of 700 chunks.

Downloaded a total of 800 chunks.

Downloaded a total of 900 chunks.

Downloaded a total of 1000 chunks.

Downloaded a total of 1100 chunks.

Downloaded a total of 1200 chunks.

Downloaded a total of 1300 chunks.

Downloaded a total of 1400 chunks.

Downloaded a total of 1500 chunks.

Downloaded a total of 1600 chunks.

Downloaded a total of 1700 chunks.

Downloaded a total of 1800 chunks.

Downloaded a total of 1900 chunks.

Downloaded a total of 2000 chunks.

Downloaded a total of 2100 chunks.

Downloaded a total of 2200 chunks.

Downloaded a total of 2300 chunks.

Downloaded a total of 2400 chunks.

Downloaded a total of 2500 chunks.

Downloaded a total of 2600 chunks.

Downloaded a total of 2700 chunks.

Downloaded a total of 2800 chunks.

Downloaded a total of 2900 chunks.

Downloaded a total of 3000 chunks.

Downloaded a total of 3100 chunks.

The response message was received from 'https://xxxxx/replisapi.dll' and is being processed.

SynchronizeSubscriptionFull:ComErrorException: -2147467259_The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Cause:

To help avoid potential memory problems on Subscriber computers, Web synchronization uses a default maximum size of 100 MB for the XML file used to transfer changes.

Workaround/Solution:

Best option is to look at the amount of data that is being transferred over. Make sure pre-computed partitions is enabled. If this issue still persists than the solution is to do the following:

PLEASE NOTE:

This registry key ONLY works on SQL 2008 and SQL 2008 R2. If your experiencing this issue in SQL 2005 than an upgrade will be necessary.

http://msdn.microsoft.com/en-us/library/ms151255(v=SQL.100).aspx

The limit can be raised by setting the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Replication

WebSyncMaxXmlSize DWORD 2000000

The range of acceptable values for this key is 100 MB to 4GB. The value is specified in KB. Setting this parameter to a high value does not guarantee that you can synchronize that amount of data. The effective limit is constrained by how much contiguous memory is available on the Subscriber computer. If you must have a value larger than 100 MB, we recommend that you increase the value incrementally and test memory consumption with a typical workload on the Subscriber.

The maximum size for the XML file is 4 GB, but replication synchronizes the changes from that file in batches. The maximum batch size of data and metadata is 25 MB. You must ensure that the data in each batch does not exceed approximately 20 MB, which allows for metadata and any other overhead. This limit has the following implications:

· You cannot replicate any column that causes the data and metadata to exceed 25 MB. This might be an issue when you are replicating rows that contain large data types, such as varchar(max).

· If you replicate large volumes of data, you might have to adjust the Merge Agent batch size.

Batch size for merge replication is measured in generations, which are collections of changes per article. The number of generations in a batch is specified by using the –DownloadGenerationsPerBatch and –UploadGenerationsPerBatch parameters of the Merge Agent. For more information, see Replication Merge Agent.

For large volumes of data, specify a small number for each of the batching parameters. We recommend that you start with a value of 10, and then tune based on application needs and performance. Typically, these parameters are specified in an agent profile. For more information about profiles, see Replication Agent Profiles.

This key needs to be added on the IIS server and the server where the merge agent is running.

Replication: Issue with setting up Transactional Replication with Updatable Subscribers and NFR Triggers

clsilva — Mon, 26 Jul 2010 09:19:54 GMT

Problem:

I have set up a Transactional Replication with Updatable Subscribers on my SQL 2005 server and created user triggers on the Publisher database. These triggers have been configured with “not for replication”.

I update my subscriber and the changes are propagated onto the publisher. I then update my publisher and these changes are not sent to the subscriber even though no error message is flagged. If I attempt to alter the data on the subscriber side, I obtain the following error:

Server: Msg 20515, Level 16, State 1, Procedure sp_MSreplraiserror, Line 9

Updatable Subscribers: Rows do not match between Publisher and Subscriber. Refresh rows at Subscriber. Server:

Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8

Updatable Subscribers: Rolling back transaction.

This issue is not reproducible in SQL 2000

This issue is reproducible in SQL 2008

Cause & Workaround:

Looking at SQL 2000’s behaviour:

There was an issue in SQL2000 in regards to cached plans for stored procedures with regard to repl agent connection: when a proc is first executed by repl agent, the plan contains information about repl agent connection property, this plan is cached, later when proc is executed by non-repl connection the same plan is used hence it will behave as if it was executed by repl agent. In this particular case sync procs on publisher side are created by replication snapshot agent hence the plan was created with repl agent connection flag, when subscriber trigger fires and invoke publisher side sync proc through RPC, it should have been treated as non-repl connection but sync proc on publisher was using the cached plan from before hence it was behaving as if coming from repl agent connection. If you manually drop and recreate the sync procs on publisher from QA window, subsequent subscriber side change will cause user NFR trigger on publisher to fire, this has been fixed in SQL2005 hence the behaviour difference.

Unfortunately the problem in SQL2000, as described above, was indeed a pretty serious one as well, because once sync procs on publishers are created by snapshot agent, it will always behave as if called by repl agent, which means one can in theory invoke them directly in publisher db and it would honour NFR trigger by not firing them. This is undesired and can be considered as a hole. Not to mention if user manually drop and recreate these sync procs for whatever reason, it will behave the same as SQL2005.

Having said these, we realize the inconsistent behaviour and we are taking steps to provide better and more consistent user experience:

1. the fix we made in SQL2005 was meant to plug the hole so NFR triggers are only honoured by repl agent connection
2. there is still the fundamental difference in how RPC and repl agent behave in this case, we hope to provide better support to updateable scenario by adding conflict detection capability into Peer-to-peer replication to in the near future we can consider deprecating 'immediate update' future which means by then all updateable subscription will be serviced by repl agent instead of RPC, which will help providing uniformly consistent behaviour.

Workaround for this issue:
1) consider using 'queued' as the update mode instead of 'failover' - if you leave queued agent running in continuous mode it will work near real-time (within a couple of second) and NFR trigger will always behave as you expected.
2) create publisher side trigger not as NFR and add context_info logic (see 'set context info ..' in BOL) into subscriber side so publisher trigger can figure out if a change was made on subscriber or publisher.

Example:

IF NOT EXISTS(SELECT * FROM master.dbo.sysprocesses
                WHERE spid = @@SPID AND
                      CONVERT(tinyint, SUBSTRING(context_info, 1, 1)) & 0x80 = 0x80)
       RETURN

CDC: CDC Hangs when I try to disable it

clsilva — Fri, 04 Jun 2010 15:05:49 GMT

Problem Description:

SQL Server 2008 SP1

Transactional Replication

CDC enabled

I have an issue that occurs while trying to disable CDC on one of my databases.

When I execute the system stored procedure sys.sp_cdc_disable_db, the procedure will hang waiting on a “REPL_HISTORYCACHE_ACCESS” lock for an indefinite amount of time.

Workaround / Solution:

The cause of the issue is due to when a log reader agent is shut down, the server session of log reader (sp_replcmds) may not be shut down gracefully. It will appear as if there is an active session.

Work around is to completely stop the log reader agent before running ‘remove’ logic.

This issue will be fixed in SQL Server 2008 SP2.

Replication: SQLCESALOG: Is it possible to change the number of files and the size of each?

clsilva — Wed, 26 May 2010 11:51:10 GMT

Yes it is.

Following the following KB article: http://support.microsoft.com/kb/955968

In SP1 for SQL Server Compact 3.5 is different from previous versions. Please check KB article for differences.

MaxLogFileSize and MaxPreviousLogFiles are the two keys that we can use to alter the file size and increase the previous log files.

There is a minimum value for MaxLogFileSize (1 MB) and the default value for MaxPreviousLogFiles is 2.

When enabled, and each time IIS re-starts, a file will be kept in the log folder (up to 3 files created). This can be changed:

The SQL Server Compact 3.5 SP1 log management functionality limits how many old log files are kept on disk before these files are deleted.
Note By default, two old log files are kept on disk. You can configure this number to a maximum of 99. To do this, set the MaxPreviousLogFiles DWORD value for the following registry subkeys:

For SQL Server Compact 3.5 Client Agent logging:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft Sql Server Compact Edition\v3.5\Log\Sqlceca
For SQL Server Compact 3.5 Server Agent logging:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Log\Sqlcesa
For SQL Server Compact 3.5 Replication Provider logging:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Log\Sqlcerp

You can change the maximum size of a log file by setting the MaxLogFileSize DWORD value of the following registry subkeys.

For desktops

For SQL Server Compact 3.5 Client Agent logging:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Log\Sqlceca
For SQL Server Compact 3.5 Server Agent logging:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Log\Sqlcesa
For SQL Server Compact 3.5 Replication Provider logging:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Log\Sqlcerp

For devices

For SQL Server Compact 3.5 Client Agent logging:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Log\Sqlceca

Notes

There is no maximum value for the MaxLogFileSize value.
The minimum value for the MaxLogFileSize value for desktops is 1024 kilobytes (KB). For devices, the maximum is 512 KB.

For SQL Server Compact 3.5 Client Agent logging, log file rotation occurs every time that an application or a process that uses SQL Server Compact is started.
For SQL Server Compact 3.5 Server Agent logging and for SQL Server Compact 3.5 Replication Provider logging, log file rotation occurs every time that IIS is restarted.
If there are no registry entries for log file rotation, rotation occurs with the following default values as soon as logging is enabled:
- MaxPreviousLogFiles: 2
- MaxLogFileSize: 1024 (1 MB)

NOTE: If the registry keys are not found in the Registry, then you will have to create them. You will have to create (for example) a key for LOG, a key for Sqlcesa and then a DWORD reflecting the alteration you want (log file size or number).

Replication: Merge Fails with error : “The process could not increment the lineage version”

clsilva — Thu, 04 Mar 2010 10:45:00 GMT

I have come across this interesting issue in which we have the following scenario:

Merge Replication

All servers are running SQL 2008

When merge replication is processing certain published articles, we see that the merge agent is blocked, and is being blocked by another spid executing another task coming from the same merge agent.

A close look through a profiler trace, we see:

Example:

Spid 59 is blocking on spid 69.

Spid 59:

59 runnable 0x0063 562 SELECT PREEMPTIVE_OS_WAITFORSINGLEOBJEC LPC CLAUDY-MERGETEST-MERGETEST-CLAUDY2

Spid 69:

69 suspended 59 7 KEY: 6:72057594057916416 (1f02b882c015) 0x0005 296281 UPDATE LCK_M_X LPC CLAUDY-MERGETEST-MERGETEST-CLAUDY2

Spid 69 –> sp_MSuplineageversion

Spid 59 –> sp_msenumchangesdirect

Points to mention:

a. same merge agent for both spids

b. LPC being used

c. Waittype of “preemptive_os_waitforsingleobject” for the blocking spid

–> please read the CSS Blog for more information in regards to this waittype (http://blogs.msdn.com/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx)

d. 0x0063 (for spid 59) indicates AsyncNetworkIO

After looking further in depth of this issue, the workaround to use if you are running into this issue is:

Create an alias (the alias name has to be the same name as the server as merge collects information in regards to the servers involved from system tables)

SQL Server Configuration Manager
SQL Native Client
Aliases
Create a new alias with the SQL Server Name

Doing so will Force Merge agent to use TCP/IP (this can be seen in sysprocesses --> net_library column)

Once this is set up, run merge and verify in the sysprocesses that the spids are now using TCPIP.

** More info on this topic when available **

Replication: My merge agent keeps “dropping and creating” my newly added articles!!!

clsilva — Tue, 02 Feb 2010 14:18:00 GMT

Problem:

Consider the following scenario:

You add a new article(s) to an existing publication in your merge replication environment and
Your merge agent uses '-ParallelUploadDownload' parameter with a value of 1 for synchronization.

Note: Merge Agent profile High Volume Server-to-Server Profile enables parameter -ParallelUploadDownload

In this scenario, you will notice that the merge agent enumerates the already replicated schema changes during each sync operation causing performance problems and other issues.

Cause / Workaround:

The schemaversion column in sysmergesubscriptions table at the subscriber (and publisher) stores the version of the last schema that is received. Due to this issue, this value does not get updated and always points to a version that has already been replicated over to Subscriber during the previous synchronization operation.

You can workaround this issue by either disabling or removing the Merge Agent parameter -ParallelUploadDownload for that subscription. Disabling this parameter causes Merge Agent to update schemaversion to the correct value during next synchronization.

To remove or disable this parameter edit either 'Agent job' or 'Agent Profile' using one of the following procedures that is applicable to your situation:

Agent Job:

Open Merge Agent Job properties.
Select "Steps" page.
Select "Run agent" step and press "Edit" button.
In "Command", look for parameter "-ParallelUploadDownload". If it has value 1, disable it by setting it to 0 (i.e. "-ParallelUploadDownload 0"). OR Remove the parameter and its value altogether from Merge Agent command.

Agent Profile:

Open Replication Monitor.
Select Merge Publication.
Right click Subscription and go to "Agent Profile".
Make sure Profile "High Volume Server-to-Server Profile" is NOT selected.
If custom profile is used and if parameter "-ParallelUploadDownload" is available, make sure it is set to value 0.

Note: Merge Agent parameter "-ParallelUploadDownload" is deprecated in SQL Server 2008.

http://support.microsoft.com/kb/2003609

Replication: The schema script could not be propagated

clsilva — Mon, 25 Jan 2010 11:21:00 GMT

Problem:

I create a SQL Server 2005 replication topology on my Windows 2003 server. Everything synchronizes fine.

I then go and add a default constraint to an article.

Subscriber is synchronized and the default constraint is propagated to the subscriber.

Subscriber is marked for reinit with upload first.

When the subscriber is marked for re-initialisation, the schema version will be rolled back to 0. If the upload_first option is used, the merge agent retrieves schema changes from sysmergeschemachanges and tries to apply them at the subscriber before uploading the data. Therefore it attempts to create the constraint again which already exists at the subscriber.

Workaround available:

To avoid data loss at the subscriber, you have to manually resolve the reason for the failure at the subscriber side:

WORKAROUNDS:

1- Drop the DEFAULT constraint on the subscriber before applying the snapshot:
ALTER TABLE <tablename> DROP CONSTRAINT <constraintname>

Note: you will have to do this after each reinit on each subscriber

2- Use sp_reinitmergesubscription ... @upload_first = 'false'

SQL Server: SQL Server and Windows “End of Support” Dates

clsilva — Mon, 18 Jan 2010 10:00:00 GMT

http://blogs.msdn.com/psssql/archive/2010/01/08/important-sql-server-and-windows-end-of-support-dates-you-should-know-about.aspx

taken from the blog above, this information is important for all to know:

Important SQL Server and Windows “End of Support” Dates you should know about….

There are some important dates about the end of support for specific products and releases involving SQL Server and Windows that I want you to be aware of:

Important Upcoming SQL Server Support Dates

1) SQL Server 2005 Service Pack 2 support ends next week January 12, 2010. You should upgrade to SQL 2005 SP3 or SQL 2008 SP1 immediately.

2) SQL Server 2008 RTM support ends on April 13, 2010. You should make plans to upgrade to SQL Server 2008 SP1 soon.

A complete list of our support policy dates can be found on the following web site:

http://support.microsoft.com/lifecycle/

A very good discussion about these upcoming dates was published by our release services team for SQL Server back in October of last year. This includes what choices you have for support and what actions you should consider to stay supported:

http://blogs.msdn.com/sqlreleaseservices/archive/2009/10/08/end-of-service-pack-support-for-sql-server-2005-sp2-and-sql-server-2008-rtm.aspx

What does this “end of support” mean to you?

You cannot call or open a case with CSS for technical support if you are running these versions after the respective dates above. There are only 2 exceptions to this:
- You are contacting CSS to get help with an upgrade to a supported version
- You have a Premier Support Agreement and have purchased something called a Custom Support Agreement (CSA) (contact your Technical Account Manager if you want to consider this option)
Microsoft won’t produce any hotfixes or security updates for these specific versions (Custom Support Agreement customers are the exception)

I encourage you to do a few things to stay “ahead of the game” for the end of support dates for SQL and other Microsoft products in the future:

Frequently review the support lifecycle site.
For SQL Server specific policies, keep up with the SQL Release Services Team blog
Keep track of postings like these on this blog

Important Upcoming Windows Support Dates

While not a SQL Server Support policy, end of support for Windows 2000 Server SP4 on July 13, 2010 could affect many SQL Server customers. Furthermore, Windows Server 2003 and 2003 R2 transition into “Extended Support”. The following article has a nice summary of these Windows Support dates:

http://support.microsoft.com/gp/lifean36

What is the difference between the “end of support” for Windows Server 2000 and “Extended Support” for Windows Server 2003?

The end of support for Windows Server 2000 is like our end of support above for SQL Server 2005 SP2 and SQL Server 2008 RTM. You can no longer contact CSS for support and no security updates or fixes will be available. The only exception is if you purchase a Custom Support Agreement through your Technical Account Manager.

The Windows 2000 End-of-Support Solution Center is a good resource to read about information on the end of support for Windows Server 2000.

Extended Support for Windows Server 2003 means that:

You can still contact CSS for support questions and issues
We will still provide security fixes for all
But for non-security hotfixes, you must purchase an Extended Hotfix Support Agreement which like the CSA is available to Premier customers through your Technical Account Manager

You may wonder how the end of support for Windows Server 2000 may affect you as SQL Server user? The biggest thing to consider is in order to be supported you need to upgrade to a new version of Windows. You could move to Windows Server 2003 but as stated that version is moving into Extended Support, so that may not be a good choice. If you still want standard hotfix support your choices are Windows Server 2008 or Windows Server 2008 R2.

The possible problem here you may encounter is if you are running SQL Server 2000 SP4 (the only official supported version of SQL Server 2000 which itself is in Extended Support). SQL Server 2000 SP4 is not supported on Windows Server 2008 or Windows Server 2008 R2. Therefore, if you make this jump to the new OS, you must also upgrade SQL Server to either SQL Server 2005 SP3 or SQL Server 2008 SP1.

One last question you may have. What happens if I stay on Windows Server 2000 SP4 after July 13, 2010 and I try to call in a case to CSS?. The answer depends on what type of problem you have:

CSS Platforms Support (which supports Windows) will not take a support case for Windows Server 2000 SP4 after July 13, 2010 unless you are trying to upgrade to a supported Windows OS (or have a CSA)
But let’s say you are running SQL Server 2000 SP4 which is still supported and have a SQL Server issue. CSS SQL Support would still take your case for an issue directly related to SQL Server. However, if when working the case, CSS determines the problem is specific to Windows Server 2000 SP4 and not a SQL Server issue (for example some type of disk I/O issue), then the support of the issue would stop as there is no support for that version of Windows after July 13, 2010.

Summary

Here is a summary of actions you should consider:

If you are running SQL Server 2005 SP2, you should upgrade immediately to SQL Server 2005 SP3 or SQL Server 2008 SP1 (Next week January 12, 2010 is the cutoff).
If you are running SQL Server 2008 RTM, you should upgrade to SQL Server 2008 SP1 very soon (before April 2010)
If you are running Windows Server 2000 SP4, you should make plans now to upgrade to Windows Server 2003, Windows Server 2008, or Windows Server 2008 R2 (July 13, 2010 is the cutoff). Your Windows upgrade choice may also require the need to upgrade SQL Server.

Bob Ward
Microsoft

Replication: Access Permission Error when generating Dynamic Snaphot

clsilva — Thu, 07 Jan 2010 12:15:00 GMT

Problem:

My snapshot agent fails to create a dynamic snapshot for a subscriber in my Merge replication environment. Each subscriber is supposed to receive the snapshot based on their login account. Each account is explicitly added to a group which is defined in the PAL .However the agent fails with the below error.

The login 'Domain\CLSILVA.Claudia' does not have access permission on publication 'TestPublication' because it is not in the publication access list.

If I explicitly add this account as a SQL login and give it sys admin permissions, the snapshot agent works.

Cause/Workaround:

This issue occurs when the login account was given permission via group membership0
Sp_MSreplcheck_pull tries access a system table in which contains records for each login account and does not have any information for the account accessed via group membership.

Workaround was to explicitly add the login on the publisher.

Replication: Transactional Replication Over WAN

clsilva — Mon, 04 Jan 2010 13:17:00 GMT

A lot of users have wondered about the performance latency transactional replication suffers over WAN.

After analyzing / discussing this within the teams, here are the main points one should take into consideration when deciding / testing this type of implementation:

Should you notice performance degradation when testing your transactional replication over WAN, please verify the following:

1. Where is the latency occurring? Could it be an issue on the publisher / subscriber (example:blocking)

2. Verify that the @status of the articles published have the value of 16 or 24. Please see previous post in regards to this in order to understand the reason for performance benefit.

3. Disable offload TCP Checksum

    http://msdn.microsoft.com/en-us/library/aa938424.aspx

4. Disable TCP Chimney

    http://blogs.msdn.com/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

5. Use a PULL subscription instead of a PUSH

6. Should the above 5 options not help in the performance, I would highly recommend the following:

    Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008

    http://technet.microsoft.com/en-us/library/dd263442.aspx

Happy New Year Readers

clsilva — Mon, 04 Jan 2010 09:26:00 GMT

Happy New Year Readers!

May the best moments of 2009, be the worst of 2010 are my wishes to you all :)