January 13, 2014 at 3:48 pm
I'm attempting to set up snapshot replication between a SQL 2008 R2 server, call it ServerProd, and SQL 2008 R2 server, call it ServerReport. The source database, call it DB_Source, is in SQL 2005 compatibility mode. It's a vendor database so I can't just change the database to be at SQL 2008 R2. The subscription database is on ServerReport in the database called DB_Report. When I try to initialize the snapshot it fails on a table that has the DATE data type, which is available in SQL 2008 but not in SQL 2005. Does anyone have any great ideas for getting around this? I will try to find out if I can change the compatibility level of the source database but that's not up to me.
Thanks for your help!
January 14, 2014 at 3:34 am
Could you post a relevant section of the error message ? The only thing I can think is that your source database has a user-defined datatype called DATE (but to be honest, replication isn't my strong point).
It would help to have a little more detail... 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
January 14, 2014 at 9:09 am
OK, I'll add some context here. Both servers are at SQL Server 2008 R2. The source database is in 2005 compatibility mode and the source database uses the System data type Date. There are no user defined data types. When I try to create the subscription it gives the error message below. I'm hoping I can just change the compatibility level of both source and subscription databases to 2008 R2, but since it's a vendor app I can't do that without their support.
Message: Script failed for Table 'dbo.inmate'.
Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
....
Source: Microsoft.SqlServer.Smo
Target Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingOptions)
Message: Column dob in object inmate contains type Date, which is not supported in the target server version, SQL Server 2005.
Stack: at Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingOptions options)
at Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes)
January 15, 2014 at 6:22 am
Looks like this is way way down in the undocumented hidden stored procs that do replication.
[font="Courier New"]sp_articlecolumn[/font] adds the column to the article for publication / subscription.
This calls [font="Courier New"]sys.sp_MSrepl_articlecolumn[/font] to do the heavy lifting (hidden), which in turn calls [font="Courier New"]sys.sp_IHarticlecolumn[/font] (also hidden).
[font="Courier New"]sys.sp_IHarticlecolumn[/font] does a validation check on the datatypes of the columns in the publication and mapping them to 'valid' datatypes. I think this is where your process is failing because your subscriber database is in compat mode 90, even though the [font="Courier New"][Date][/font] datatype is valid in the databases due to them being hosted on SQL2008 +.
This in turn begs another question about the workings of compat level :crazy:
Is there any mileage in changing the compat level of your subscription database ONLY, and see if that works ? The problem appears to be this last datatype validation when setting up the subscription - the publisher may not bethat fussy about what it publishes, but apparently the subscriber is 🙂
Perhaps it might be worth pointing out to the vendor the mismatch in compat level (90) and database structures (100).
I wish you luck.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
January 15, 2014 at 8:58 am
Thanks for your response! It is interesting how compatibility level plays out. I did try putting the subscription level at 100 compatibility but had the same issue. I think I could have gotten around it if I had used transactional replication because you can initialize a subscription with a backup, which isn't available in snapshot replication. The data only needs to be refreshed twice a day, so snapshot replication makes more sense than transactional. Interestingly, the vendor responded back yesterday and said the app was fine in 100 compatibility level.
Now I have to figure out what to test when I change the compatibility level on the test database. Since the vendor is saying it's OK in SQL 2008 I'm not terribly concerned but I'm not sure what to test. Also, I'm not sure if I'll schedule an outage when I do this in production. I've read that it's possible to have problems if users are connected when you make a change. Do you have an opinion about this? I've always changed compatibility level while users were still in the system and had no problem but now I'm thinking I should probably schedule an outage. That's problematic as this is an application for the jail.
http://msdn.microsoft.com/en-us/library/bb510680(v=sql.105).aspx
Best Practices
Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore, the problem may be compounded if the plan is placed in the plan cache and reused for subsequent queries. To avoid inaccurate query results, we recommend the following procedure to change the compatibility level of a database:
1.Set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.
2.Change the compatibility level of the database.
3.Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.
4.For more information about setting the access mode of a database, see ALTER DATABASE (Transact-SQL).
sqlmunkee (1/15/2014)
Is there any mileage in changing the compat level of your subscription database ONLY, and see if that works ? The problem appears to be this last datatype validation when setting up the subscription - the publisher may not bethat fussy about what it publishes, but apparently the subscriber is 🙂
Perhaps it might be worth pointing out to the vendor the mismatch in compat level (90) and database structures (100).
.
January 16, 2014 at 1:11 am
Given that your subscriber only needs refreshing twice a day, are you sure that replication is the right option, since it's giving you so much trouble ? 🙂
Have you considered mirroring the source DB, then snapshotting the mirror to make the data readable ? Mirroring DOES let you use a backup / restore to establish the mirror, which your snapshot replication doesn't 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
January 16, 2014 at 9:09 am
sqlmunkee (1/16/2014)
Have you considered mirroring the source DB, then snapshotting the mirror to make the data readable ? Mirroring DOES let you use a backup / restore to establish the mirror, which your snapshot replication doesn't 🙂
That's a good idea but we don't have enterprise edition, so I can't use database snapshots. I have set up transactional replication before so I'm hoping that changing the compatibility level will fix everything up. Thanks for the input!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply