SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Keeping all three environment(Dev,Stag,Prod) in sync


Keeping all three environment(Dev,Stag,Prod) in sync

Author
Message
wannalearn
wannalearn
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
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.
SatishAyyar
SatishAyyar
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 Visits: 520
You can use Replication.

http://www.sqlservercentral.com/stairway/72401/
@SQLFRNDZ
@SQLFRNDZ
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 1217
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89165 Visits: 41143
@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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
@SQLFRNDZ
@SQLFRNDZ
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 Visits: 1217
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89165 Visits: 41143
@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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
wannalearn
wannalearn
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
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!
SatishAyyar
SatishAyyar
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 Visits: 520
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.
wannalearn
wannalearn
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
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.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14642 Visits: 15980
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search