August 15, 2017 at 7:13 am
We currently have a 2008R2 publisher/distributor which is offsite. Onsite we only have 2016 SSMS. Can we create a 2012 compatible database with 2016 SSMS and replicate with the 2008R2 publisher/distributor?
August 15, 2017 at 7:24 am
Can you clarify what you're asking and what you're trying to accomplish? In its current form, the question doesn't really make sense.
If you only have SSMS onsite, where are you trying to create the database? If you do have an instance onsite, what is the version?
Generally speaking (without knowing all the details from your question) it's best to stick with the "lowest" version for compatibility...especially with replication. That doesn't mean you cannot include a database with a "higher" (newer) version, but, again depending on your scenario, it may cause problems.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 15, 2017 at 7:39 am
We currently have 2016 SSMS with a 2016 instance on site. Just to clarify, we would like to replicate a 2008R2 replication(publisher and distributor) with the 2016 instance which has database set up as 2012 compatibility level.
August 15, 2017 at 11:45 am
Will this work what I stated above?
August 15, 2017 at 6:32 pm
I think you are asking if you can restore a copy of the vendors db on your server. The answer depends on how you want to accomplish it. You can restore full .bak backups as often as you like, differentials I have no idea and .trn transaction logs you will not be able to restore once the database is taken out of restoring mode which you will have to do if you want to interact with it.
Database compatibility level isn't what you think it is. It is the method in which databases can be made "backwards compatible" with t-SQL statements and in some cases the query optimizer itself. Mostly this is only really helpful if you have an application that depends on certain features that don't work or work differently in newer versions. It doesn't mean that you will have a database that is a different version than your server.
Hope this helps.
August 16, 2017 at 5:33 am
sdh96 - Tuesday, August 15, 2017 7:39 AMWe currently have 2016 SSMS with a 2016 instance on site. Just to clarify, we would like to replicate a 2008R2 replication(publisher and distributor) with the 2016 instance which has database set up as 2012 compatibility level.
SQL support only 2 versions backwards support , if that way SQL 2016 can support upto 2012 only but you can restore the DB but if there are any version level features disabled on higher version then the application wont work as intended.
Regards
Durai Nagarajan
August 16, 2017 at 8:11 am
I think it ends up being more of an issue of whether it's supported or not.
As Durai mentions, there are limitations on the mix of versions in regards to it being supported or not. The versions supported are listed in this document:
Upgrade Replicated Databases
The GUI will enforce the version compatibility but if you create replication with scripts, it does not enforce the same. So it becomes an issue of whether it's supported. I don't know how using a lower database compatibility level would affect that. I would suspect that it would not be supported but not certain and haven't read of anyone using a lower database compatibility to get around that limit. If you have a dev or test environment with the different versions, you could just set it up the same through the GUI and see if you get errors or not. If you get errors due to the versions, you could use scripts to create what you need but it would be an unsupported configuration.
Sue
August 25, 2017 at 5:29 am
sdh96 - Tuesday, August 15, 2017 7:13 AMWe currently have a 2008R2 publisher/distributor which is offsite. Onsite we only have 2016 SSMS. Can we create a 2012 compatible database with 2016 SSMS and replicate with the 2008R2 publisher/distributor?
The SSMS isn't responsible for determining what compatibility a database can achieve is. That means if you have SSMS2017 on your laptop and you connect remotely to a SQL Server 2008 Machine and then create a database on that machine the highest compatibility level it can achieve is SQL Server 2008.
Alternatively there is a phenomenon whereby when you connect an older version of the SSMS to a newer instance that problems can occur.
In short, the instance upon which the database is created determines what compatibility levels are available.
With regards to replication, be very careful here because I believe (not 100% though) that reliability can only be guaranteed if both ends of the replication are the same version.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply