August 11, 2014 at 9:05 am
Here is the scenario, our OLTP and DW servers are on SQL Server 2008 and we have log shipping from OLTP to DW servers. We bring dbs in standby mode on DW servers to perform ETL.
We want to upgrade our DW servers to SQL 2014. We know that we won't be able to bring dbs in standby mode on new DW server (2014) while OLTP is still on 2008.
So what alternate approaches we can take to provide warm copy of OLTP dbs (SQL Server 2008) on DW server (SQL Server 2014) to perform ETL?? Replication is not an option because there is too much data change on our OLTP servers and it generates a lot of traffic between OLTP and DW servers.
August 12, 2014 at 11:13 pm
How often do you run the ETL's? Replication has a few different modes so if transactional replication isn't suitable there is snapshot replication which may fit the bill.
August 13, 2014 at 12:15 pm
We run ETL daily and currently it takes about 12 hours.
August 13, 2014 at 2:01 pm
You can maintain the primary(OLTP Server) on SQL Server 2008 and upgrade the Secondary(DW) server to SQL Server 2014, the log shipping should work. More information at http://msdn.microsoft.com/en-us/library/cc645954.aspx
"The upgrade process involves upgrading the secondary server instances of a SQL Server 2005 or higher log shipping configuration to SQL Server 2014 before upgrading the primary server instance. Always upgrade the secondary server instance first. If the primary server were upgraded before a secondary server, log shipping would fail because a backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server."
August 13, 2014 at 3:53 pm
Yes, the log shipping itself works but once we try to bring the db in standby mode to run the ETL it complains that db first need to be upgraded which means no more logs can be applied to it afterwards.
August 13, 2014 at 8:03 pm
If you are only running the etl's once per day I would run Snapshot Replication synching the database just before you start the ETL's. Shouldn't take too long.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply