InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 21:40:20 GMT20http://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxPersonally I would run the overnight loads independantly on each environment with Prod being the priority environment.this way your data is then pretty much in sync, with the exception of any synthetic/surrogate keys that are generated.You also get to test any changes to the load process and compare the results with a standard set of reports that can be verified on each system (eg Daily Sales Figures etc) with the Production database thus identifying any potential issues with changes before they get into UAT or Prod. Thu, 03 Jan 2013 00:58:15 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxWe decided to go with Full and Differential BackUp and Restore.Thank you all for your time and feedback.Wed, 02 Jan 2013 12:47:56 GMTwannalearnhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]Jeff Moden (12/29/2012)[/b][hr]Wouldn't truncating a table in QA violate the idea of keeping all 3 environments in sync? Presumably, keeping all 3 enviroments in sync really means keeping Dev and Staging in sync with production and not the other way around.[/quote]From prod to dev or staging, I would expect the sync to be a point-in-time, not maintained across time. I don't know of any dev or staging envs that never get modified, or that mods are restricted so that someone can't temporarily empty a table if they need to.Mon, 31 Dec 2012 08:47:23 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]John Mitchell-245523 (12/31/2012)[/b][hr][quote][b]ScottPletcher (12/28/2012)[/b][hr]I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.[/quote]That's how we do it. Sure, we get discrepancies from time to time, and on such occasions we go back to good old backup and restore. The problem with regular backup and restore or SAN replication is that it also wipes out any new code you are developing or testing. And the advantage of using the same load process in your dev and/or test environments is that you can also test the effect of the new code on the load process itself.John[/quote]My big concern is that the QA or staging env would accidentally load data to production. But there's also:the amount of time, resources and locking that can occur while loading data;the extra processing load put on the non-prod systems;potentially poor timing of the load/update jobs running -- QA could be in the middle of a critical demo or test.Source issues could certainly be valid. In our case, I have separate source-only backups that I can run on non-prod environments. We also have source mgmt software outside of the db itself that controls source and source versioning. You may have to take additional steps to secure your source separately prior to the restore.Mon, 31 Dec 2012 08:46:17 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxreplication solutions wont work because developers will be making changes to the dev environment, so once they change something then the databases are no longer in sync. Nightly backup and restore are the only ways to ensure the data is the same between Dev, Stag, and Prod. The only other option may be data compare by red-gate, but that would likely be a slow option. May be worth testing with a free trial. I would recommend trying to speed up the backup & restore process:- Backup to solid state drives / faster disks.- increase bandwidth between servers. 10GB cross connect- compare red-gate compression to SQL Server compression. Compare compression ratio and speed.Mon, 31 Dec 2012 06:49:08 GMTngreenehttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]John Mitchell-245523 (12/31/2012)[/b][hr][quote][b]ScottPletcher (12/28/2012)[/b][hr]I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.[/quote]That's how we do it. Sure, we get discrepancies from time to time, and on such occasions we go back to good old backup and restore. The problem with regular backup and restore or SAN replication is that it also wipes out any new code you are developing or testing. And the advantage of using the same load process in your dev and/or test environments is that you can also test the effect of the new code on the load process itself.John[/quote]I use it to my advantage... it reminds people to save their work in SVN. :-DMon, 31 Dec 2012 06:47:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]ScottPletcher (12/28/2012)[/b][hr]I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.[/quote]That's how we do it. Sure, we get discrepancies from time to time, and on such occasions we go back to good old backup and restore. The problem with regular backup and restore or SAN replication is that it also wipes out any new code you are developing or testing. And the advantage of using the same load process in your dev and/or test environments is that you can also test the effect of the new code on the load process itself.JohnMon, 31 Dec 2012 03:30:55 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxWouldn't truncating a table in QA violate the idea of keeping all 3 environments in sync? Presumably, keeping all 3 enviroments in sync really means keeping Dev and Staging in sync with production and not the other way around.Sat, 29 Dec 2012 11:17:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]Jeff Moden (12/28/2012)[/b][hr][quote][b]wannalearn (12/27/2012)[/b][hr]Thank you all for your responses. We can not use Log Shipping because Our databases do not store transactions. We load data everynight from different sources . So it is not our responsibility right now.We use replication but only for some database. My senior DBA has not kept all databases in sync for some reason. it could be hardware issues.This is exactly what we want as mentioned by @SQLFRNDZ - "his requirement is not DR and he just wants to copy a database to other lower environments daily and want to automate the process."Is there any efficient way other than back up and restore ?Thanks![/quote]Yep... like I said, use the SAN replication if available. DR isn't it's only use. It's very handy and lightning fast at keeping databases in sync.[/quote]I'm not sure if SAN replication would even be applicable in your situation, since presumably you will be modifying the rows in the qa and staging areas yourself. For example, I don't know that replication will handle a situation where you TRUNCATE the table in qa, say, but of course not in production. What happens when SAN replication then attempts to replicate the data? I don't think it will work smoothly, but I certainly can't say for sure, since I definitely admit I'm NOT a replication expert.I do know that replication is potentially complex and you will [i]require[/i] someone with expertise in it if you intend to use it in this situation.Fri, 28 Dec 2012 21:09:56 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]wannalearn (12/27/2012)[/b][hr]Thank you all for your responses. We can not use Log Shipping because Our databases do not store transactions. We load data everynight from different sources . So it is not our responsibility right now.We use replication but only for some database. My senior DBA has not kept all databases in sync for some reason. it could be hardware issues.This is exactly what we want as mentioned by @SQLFRNDZ - "his requirement is not DR and he just wants to copy a database to other lower environments daily and want to automate the process."Is there any efficient way other than back up and restore ?Thanks![/quote]Yep... like I said, use the SAN replication if available. DR isn't it's only use. It's very handy and lightning fast at keeping databases in sync.Fri, 28 Dec 2012 20:40:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxAs Scott suggested , differential backup and a periodic full backup is a lot better solution to your problem. But then I was just giving you out different solutions out of which you may choose which better suits.Fri, 28 Dec 2012 19:15:00 GMTSatishAyyarhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxI would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.One possibility is to mix differential backups and restores with the full backups and restores. This makes sure you get all the data but allows for overall faster processing.Periodically -- weekly, biweekly, whatever -- you do a full backup. Naturally this can be done when the most time is available. This provides a "base" to apply differentials to. Then do daily -- or whatever -- differential backups that you can apply to the base to bring the dev and qa dbs up to date.First, restore the "base" backup to a different, temporary "restore db name", using WITH NORECOVERY. Then apply the differential (if any). RESTORE the restore db name WITH RECOVERY. Run any required scripts (adjust permissions, resync users, etc.). For example, for "DB1", restore to "DB1_Restoring".Once the restored db is verified fully ready to go, drop the existing db and rename the new one to the main name; for example, DROP DB1, RENAME DB1_Restoring to DB1.Finally, immediately "pre-restore" the "base" backup to the "restore db name" (WITH NORECOVERY). That reduces the time needed for the next refresh, because the bulk of the data has already been restored.Note that this also allows you to quickly make the dev or qa db look like prod using the standard differential backup method if you need to immediately investigate a prod problem.It also leaves the original dev and qa dbs available until the new db restore is verified as working.The big disadvantage with this method is that it requires more disk space ... I fall back on "disk is (relatively) cheap" there :-).Fri, 28 Dec 2012 15:08:53 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]wannalearn (12/28/2012)[/b][hr]Thanks Satish! I am not sure if we can use SSIS package at this time. I am trying to understand the process in details . Will come back for some more queries.[/quote]SSIS was just an example that Satish gave. Since you say that all your data comes from outside, just use exactly the same process that you use to load data into Prod to load it into the other two environments.JohnFri, 28 Dec 2012 08:25:57 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxThanks Satish! I am not sure if we can use SSIS package at this time. I am trying to understand the process in details . Will come back for some more queries.Fri, 28 Dec 2012 08:07:51 GMTwannalearnhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxI would run the same process, that loads data into production, on the lower environments. For example if you have a SSIS package which loads data into production. Create a copy of this SSIS package and point the connections to lower environments and run the package manually or just schedule it. That way you don't have to take backup and do the restore.Thu, 27 Dec 2012 10:18:59 GMTSatishAyyarhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxThank you all for your responses. We can not use Log Shipping because Our databases do not store transactions. We load data everynight from different sources . So it is not our responsibility right now.We use replication but only for some database. My senior DBA has not kept all databases in sync for some reason. it could be hardware issues.This is exactly what we want as mentioned by @SQLFRNDZ - "his requirement is not DR and he just wants to copy a database to other lower environments daily and want to automate the process."Is there any efficient way other than back up and restore ?Thanks!Thu, 27 Dec 2012 09:06:10 GMTwannalearnhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]@SQLFRNDZ (12/26/2012)[/b][hr][quote][b]Jeff Moden (12/26/2012)[/b][hr][quote][b]@SQLFRNDZ (12/26/2012)[/b][hr]The best way is Logshipping depends on the daily load though...[/quote]Not really. "It Depends". SAN-based replication is nearly instantaneous and puts no load on the production server. It has some disadvantages but, like I said, "It Depends". Hell would have to freeze over and pigs would have to grow wings before I'd ever consider using SSIS for such a thing.[/quote]Sounds Good but SAN-Based replication supports specific to 1 dB ?My understanding that his requirement is not DR and he just wants to copy a database to other lower environments daily and want to automate the process. SAN-replication sounds to me like mirroring the environment instead just a dB.[/quote]It depends on the SAN and either the software or the hardware they built in to it. At the current company I work for, we use it to replicate the entire environment offsite for DR purposes on a continual basis. At a previous company, we used it once at day at midnight to replicate the main prod database to a reporting/analysis DB. Total "offline" time was less than 15 seconds for that.Thu, 27 Dec 2012 08:34:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]Jeff Moden (12/26/2012)[/b][hr][quote][b]@SQLFRNDZ (12/26/2012)[/b][hr]The best way is Logshipping depends on the daily load though...[/quote]Not really. "It Depends". SAN-based replication is nearly instantaneous and puts no load on the production server. It has some disadvantages but, like I said, "It Depends". Hell would have to freeze over and pigs would have to grow wings before I'd ever consider using SSIS for such a thing.[/quote]Sounds Good but SAN-Based replication supports specific to 1 dB ?My understanding that his requirement is not DR and he just wants to copy a database to other lower environments daily and want to automate the process. SAN-replication sounds to me like mirroring the environment instead just a dB.Wed, 26 Dec 2012 16:29:38 GMT@SQLFRNDZhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]@SQLFRNDZ (12/26/2012)[/b][hr]The best way is Logshipping depends on the daily load though...[/quote]Not really. "It Depends". SAN-based replication is nearly instantaneous and puts no load on the production server. It has some disadvantages but, like I said, "It Depends". Hell would have to freeze over and pigs would have to grow wings before I'd ever consider using SSIS for such a thing.Wed, 26 Dec 2012 16:04:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspx[quote][b]wannalearn (12/26/2012)[/b][hr]In our office we run jobs to keep these environment in sync. We get data from outside source and run the job overnight to load data on Prod. It's not a big environment so we have managed to do back up and restore on other environment as per need basis. I am assigned to find out better way to keep them in sync. I have googled on this topic and I am lost right now.Plz advise. [/quote]The best way is Logshipping depends on the daily load though...If it is huge load every day then go for creating a SSIS package to refresh teh dB in test and other region using Auto sql job. Wed, 26 Dec 2012 15:57:25 GMT@SQLFRNDZhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxYou can use Replication.http://www.sqlservercentral.com/stairway/72401/Wed, 26 Dec 2012 15:50:18 GMTSatishAyyarhttp://www.sqlservercentral.com/Forums/Topic1400400-391-1.aspxIn our office we run jobs to keep these environment in sync. We get data from outside source and run the job overnight to load data on Prod. It's not a big environment so we have managed to do back up and restore on other environment as per need basis. I am assigned to find out better way to keep them in sync. I have googled on this topic and I am lost right now.Plz advise. Wed, 26 Dec 2012 15:38:01 GMTwannalearn