Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in the process of replicating my companies databases from SQL Server 2005 to SQL Server 2012 using Transactional Replication. When complete we will be running preliminary tests on the database, and if things look good, we will replicate any data that has changed since the initial replication, and then cutover to the new servers. The replication is from our production machine which has to be up 24/7.

All of the databases replicate perfectly except one. This database has a System.Core assembly, due to a dependencey from another assembly , since SQLCLR in SQL Server 2005 does not natively support System.Core.

I am getting the following error from the subscriber:

  • CREATE ASSEMBLY failed because assembly 'System.Core' is a system assembly. Consider creating a user assembly to wrap desired functionality. (Source: MSSQLServer, Error number: 6596)

I believe that this is due to the fact that System.Core has been natively supported in SQLCLR since SQL Server 2008. This error kills all of the replication onto the subscriber.

I was under the assumption that assemblies do not replicate, but apparently they do, at least for transactional replication.

Is there any way:

1) I can stop the assemblies from being published as part of the replication,

or

2) I can prevent the attempted creation of the assembly on the subscriber

or

3) I can allow the process to continue, even if the 'CREATE ASSEMBLY' fails.

Also, a stored procedure is dependent on the assemblies. If I prevented the stored procedure from being replicated, is it possible that this would prevent the attempted replication of the assemblies it is dependent on. I am loathe to try it without a definitive answer, as I do not want to use our production server to play a game of "What if?"

share|improve this question

migrated from stackoverflow.com Mar 17 '13 at 4:39

This question came from our site for professional and enthusiast programmers.

1 Answer 1

SQL Server Replication isn't a good way to handle a database upgrade. A much better approach would be to take a full backup and restore then do your testing. After completing your testing you do a full backup and restore, then perform log restores until you are ready to cut over the database. After you take the production system offline you finish the log restores then bring the database online.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.