March 24, 2014 at 10:15 pm
I have a SQL Server 2012 Standard Edition instance that is going to host databases for an SSRS instance. The databases are currently on OLTP 2005 Standard Edition instances that are not going away or being upgraded. Just tonight I found out that the dev team thought they could use Log Shipping and have a the secondary on a newer instance be in Standby mode. They wanted the log shipping to happen every 4 hours. I have found that it is not possible to do Log Shipping from an older to a newer instance and have the secondary be in Standby mode.
So, I am pondering replication as a way to get the data over to the newer SQL Server instance. I was sure I had seen information on TechNet or MSDN blogs that indicated that the Distributor can't be a newer SQL instance than the Publisher. however, I have been unable to locate information about this. do I remember correctly and does anyone have other suggestions for getting the data from the 2005 instance to the 2012 instance every four hours or on some other type of frequent, daily interval?
March 24, 2014 at 10:42 pm
refer the following link for compatibility matrix along with other info, however this is for 2008:
http://msdn.microsoft.com/en-us/library/ms143241(v=sql.105).aspx
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 24, 2014 at 10:52 pm
free_mascot,
That is what I was looking for. So the distributor can't be a version of SQL Server that is older than the Publisher. Looks like I can't even have a 2012 instance be a subscriber to a 2005 instance though based on the second paragraph below from the link.
"For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)
For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers. "
So, now I don't know what my options are, but you did answer my question.
March 25, 2014 at 1:29 am
Again standby mode in Log shipping will not allow users to use database and max you can have read_only database that too SQL removes the connection while applying the logs.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 25, 2014 at 4:09 am
free_mascot (3/25/2014)
Again standby mode in Log shipping will not allow users to use database and max you can have read_only database that too SQL removes the connection while applying the logs.
Not possible in this case. DB's cannot be brought into standby on newer version of SQL. As they are read only and dont go through full recovery they cannot be upgraded.
lmarkum (3/24/2014)
So, now I don't know what my options are, but you did answer my question.
They are limited to none in this case unless you used SQL2008R2 or upgrade the source server. You could consider creating an SSIS package or some other internally developed process, but given the time and admin cost, you'd be much better off upgrading the source server.
March 25, 2014 at 5:43 am
I appreciate the replies. I knew I could not use Log Shipping and I had thought about suggesting we update the source database. However, I am certain that will not be well received as it means a lot more money in licensing and delays for testing.
I think if I have the source database write a full backup daily, at say midnight, and a differential every four hours starting at 0415, then I could restore the full backup at 0030 and restore the backups every four hours starting at 0045. I think that would keep my backups and restores from overlapping.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply