Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Keeping all three environment(Dev,Stag,Prod) in sync Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 3:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 5, 2013 9:45 AM
Points: 39, Visits: 83
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.

Post #1400400
Posted Wednesday, December 26, 2012 3:50 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 21, 2014 1:22 PM
Points: 485, Visits: 446
You can use Replication.

http://www.sqlservercentral.com/stairway/72401/
Post #1400401
Posted Wednesday, December 26, 2012 3:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:52 PM
Points: 464, Visits: 1,037
wannalearn (12/26/2012)
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.



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.






--SQLFRNDZ
Post #1400403
Posted Wednesday, December 26, 2012 4:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
@SQLFRNDZ (12/26/2012)
The best way is Logshipping depends on the daily load though...


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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1400405
Posted Wednesday, December 26, 2012 4:29 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:52 PM
Points: 464, Visits: 1,037
Jeff Moden (12/26/2012)
@SQLFRNDZ (12/26/2012)
The best way is Logshipping depends on the daily load though...


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.


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.



--SQLFRNDZ
Post #1400411
Posted Thursday, December 27, 2012 8:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
@SQLFRNDZ (12/26/2012)
Jeff Moden (12/26/2012)
@SQLFRNDZ (12/26/2012)
The best way is Logshipping depends on the daily load though...


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.


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.


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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1400639
Posted Thursday, December 27, 2012 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 5, 2013 9:45 AM
Points: 39, Visits: 83
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!



Post #1400663
Posted Thursday, December 27, 2012 10:18 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 21, 2014 1:22 PM
Points: 485, Visits: 446
I 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.
Post #1400697
Posted Friday, December 28, 2012 8:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 5, 2013 9:45 AM
Points: 39, Visits: 83
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.
Post #1400928
Posted Friday, December 28, 2012 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,383, Visits: 9,951
wannalearn (12/28/2012)
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.

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.

John
Post #1400935
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse