Post reply

Hoppa! Two brand new SQL Server for me! Now, how do I replicate data?

  • April 21, 2011 at 6:44 am

    #238981

    Hi,

    I am a lucky DBA who has just got two new SQL Servers (both all latest OS, SQL-WebEdition). One is going to be used for production, the other for reporting.

    Small question that probably has a large answer: what's the best way to replicate data from the Production SQL to the Report SQL?

    It is, by the way, not required to have real-time data on the Report SQL. If the Report SQL holds all data "until yesterday" that's fine. But, if it is just as easy, having all data "until a few seconds ago", well, who can resist that.

    Who can show me the path to walk?

    Thx,

    Raymond

  • April 21, 2011 at 6:51 am

    #1314469

    Either by using replication; one-way transactional will provide you with real-time data on the report systems

    or by using SSIS, this will work for near-real time data or data util last night or whatever your load schedule is;

    there are of course otherways, such as snapshot replication or log-shipping

    In my opinion replication is far easier to setup and maintain than ssis packages, but it does come with some overheads as every transaction needs to be replicated across.

    sorry missed the web-edition part... i guess these arent really options anymore

  • April 21, 2011 at 6:53 am

    #1314472

    Well, Web Edition narrows your choices so it's not as big an answer as it may have been as any solution involving Replication or Mirroring is out.

    The clear solution that jumps out with those limitations is log shipping.

    Have a look here for more information:

    http://msdn.microsoft.com/en-us/library/ms187103.aspx

    It's a fairly simple concept - you have a read/write database in the FULL recovery model, you backup and restore the database to the reporting database without doing a recovery and then take transaction log backups on regular intervals to restore to the read-only copy (restoring WITH STANDBY so it's open to read-only queries between restores).

  • April 22, 2011 at 12:48 am

    #1314794

    Thanks for y'r answers... i'll be looking into it right now!

Viewing 4 posts - 1 through 3 (of 3 total)

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