Post reply

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

  • December 26, 2012 at 3:38 pm

    #266635

    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.

  • December 26, 2012 at 3:50 pm

    #1571699

    You can use Replication.

    http://www.sqlservercentral.com/stairway/72401/

  • December 26, 2012 at 3:57 pm

    #1571701

    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.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • December 26, 2012 at 4:04 pm

    #1571703

    @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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 26, 2012 at 4:29 pm

    #1571708

    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.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • December 27, 2012 at 8:34 am

    #1571881

    @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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 27, 2012 at 9:06 am

    #1571904

    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!

  • December 27, 2012 at 10:18 am

    #1571935

    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.

  • December 28, 2012 at 8:07 am

    #1572127

    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.

  • December 28, 2012 at 8:25 am

    #1572132

    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

  • December 28, 2012 at 3:08 pm

    #1572259

    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.

    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 :-).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 28, 2012 at 7:15 pm

    #1572283

    As 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.

  • December 28, 2012 at 8:40 pm

    #1572288

    wannalearn (12/27/2012)

    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!

    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.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 28, 2012 at 9:09 pm

    #1572289

    Jeff Moden (12/28/2012)

    wannalearn (12/27/2012)

    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!

    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.

    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 require someone with expertise in it if you intend to use it in this situation.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 29, 2012 at 11:17 am

    #1572349

    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.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply