July 16, 2015 at 12:55 pm
Hi,
We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014. We'd like to keep one 2008R2 server and one 2014 server until we have time to upgrade the applications and move everything to the new server. The problem is we have custom code in some of the 2014 databases that access tables in the 2008 databases. I know we can easily do cross server joins by using a linked server, but it would be a huge undertaking to find all that code and add a linked server name in front of every table, stored procedure, etc. So my question is, is there any way to move a database to a different server and still be able to access it without having to qualify the object names with a linked server? Is there some kind of server/database synonym that can be setup that would be recognized by all databases?
Thanks in advance for your thoughts on this!
Lina
July 16, 2015 at 1:37 pm
so you had cross database queries,and because of the migration, one of the databases is now on another server?
if i have that right, i think you can create an empty database with the same name, and place synonyms in that database that point to the linked server.
so say you had two databases on Server2008, one named PROD and the other named TheData, and PROD has a proc that referenced TheData.dbo.SomeTable.
if PROD got moved to SERVER2014, on the the original server, you want to create an empty database named TheData on SERVER2014,
and then create a linked server to Server2008, and then inside the empty TheData database, create a synonym SomeTable For [Server2008].TheData.dbo.SomeTable
that ways, i think if the proc says select * from TheData.dbo.SomeTable, it resolves the synonym and remote table without code changes.
performance might suck, with linked servers, but i think that's a workable band aid.
Lowell
July 16, 2015 at 2:34 pm
Yes, you have the setup exactly right. Interesting... so I would then need to create a synonym for every table accessed in TheData from PROD, correct?
July 16, 2015 at 5:09 pm
Yeah evey table, view, proc and function potentially, but you could script it from the metadata in sys.objects, a d i think you d be up and running.
Lowell
July 17, 2015 at 3:10 am
linaw (7/16/2015)
We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014.
Have you set the compatibility level to 120 (2014) in the databases? Maybe the applications will work if you lower the compatibility level to 100 (2008)?
July 17, 2015 at 7:53 am
Ok thanks Lowell, that's definitely a possibility.
Regarding the compatibility level, yes I'm trying that too. I'm actually in the process of restoring these databases to a test 2014 server and I'm going to test them. But right now one of my databases has CDC (Change Data Capture) setup and I am receiving an error on the restore. So I figured if I can't get that worked out or if the apps don't work for some reason, I should start working on a backup plan! Thanks!
July 17, 2015 at 9:38 am
My experience is that setting the compatabilty level usually works.
If you are not updating the 2008R2 DBs from 2014 you may also want to look at transactional replication as linked server queries can be very slow. I have never tried subscribing to a 2008R2 DB from 2014 but, as it is within two versions, I think it should have a reasonable chance of working. If it does work then:
- latency should only be a few seconds.
- all the 2014 queries can be run against 2014 DBs.
You could also look at using SSIS, Service Broker etc to move data around.
July 17, 2015 at 9:55 am
July 17, 2015 at 12:42 pm
Will check it out, thank you!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply