SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 High Availability Staging Database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/03/2012 :  07:10:41  Show Profile  Reply with Quote
Hi gang :)

We are building a database (on the web) for a Client Facing application which needs access to data from the [internal use only] Back-Office database (server for that is in the office).

Plan is to push relevant data from Office server database to Web server Database (maybe daily, maybe hourly).

We want Web App to be able to see transactionally complete data at all times (unlikely to be able to / want to transfer data in correct order to maintain referential integrity).

Two plans of attack:

1) Backup database on Office Server, restore to Web server. Presumably application is "down" during the restore?

2) Transfer "changed data only" from Office Server to Web Server and apply to Web Server (using UpSert).

Could do (2) within a TRANSACTION, but it may be huge (i.e. if during update interval a significant number of records have changed at Office end - e.g. due to a large Import)

The receiving database on the Web Server could be SIMPLE recovery model (it will "ask" for "all modified records since [Last Time]", so if ever restored will just re-ask for older data again)

Just looking for some thoughts / experiences on any best practice. Thanks.

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 12/03/2012 :  12:40:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
Can you use transactional replication?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/04/2012 :  06:58:53  Show Profile  Reply with Quote
Hi Tara :)

Sorry, should have mentioned that I had discounted replication.

I had assumed that Transactional Replication would be a huge load. Data changes a lot/often, but the "far end" only needs to have a "fairly recent snapshot" - once a day is probably fine.

Actually ... maybe I should rethink that and reconsider.

We UPDATE the Staging Database with highly selective update statements. From the new data that we receive (from a variety of sources) we don't just do "Delete the lot and re-insert" where, say, 90% of the rows are identical (the transaction cost of that method would be massive of course ...)

We do a full WHERE clause on all columns:

WHERE (SRC.Column1 <> DST.Column1 -- using some sort of Binary Collation for VARCHAR fields
       OR (SRC.Column1 IS NULL AND DST.Column1 IS NOT NULL)
       OR (SRC.Column1 IS NOT NULL AND DST.Column1 IS NULL))
   OR (SRC.Column2 ...

and part of the UPDATE is to set a "last modified date". Inserted rows get Today's Date. There is also a FLAG column used to indicate Inserted/Updated (and also DELETEd).

The Remote database then "pulls" any data with a newer "last modified date" than last time

Advantage of this, to my eye, is that if we RESTORE the remote database we are also restoring the "most recent got date" as well, so the next scheduled run will just pull all data changed since that date.

Conversation with clients about this type of usage tends to include "What about after a storm like 1988?". In that year we had a freak storm that came through this part of the country and felled huge numbers of trees. It took power and telecom companies days/weeks to restore everyone. With our method the amount of data we have to get to the remote end is modest - just whatever rows have changed since last time, rather than the whole of the transaction log (which we might struggle to physically store if the remote end went offline for a week?). It also needs zero special handling on our part (no resync to get "replication" going after a Restore, no issue with LDFs growing if replication fails)

I would also be a bit bothered that our Staging Database is not updated with regard to relational integrity. I suppose I could update it, locally, in a TRANSACTION - and then the replication to the remote would be the same. But some of our Updates include queries to remote databases and SQL refuses to make the update inside a Transaction (errors raised because of issues to do with distributed transactions). Solution to that would be to get everything into #TEMP tables, or somesuch, first I suppose ...

Typing that out makes me just feel that it will increase the work for me, and also the risk (replication is not something I am very familiar with, so I'll be on a learning curve of some sort)


Am I hearing that most people (everyone??) would use Replication for this?

What do folk do with a data warehouse that you want to update overnight locally, and then get a copy to a remote location? - do you use transactional replication for that too? or something else perhaps?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 12/04/2012 :  14:34:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
For systems that need an update overnight, I use backup/restore. But that means downtime. Is the database small enough that a restore will fit into a downtime window?


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 12/04/2012 :  14:35:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
You could also look into snapshot replication.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/05/2012 :  11:03:54  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Is the database small enough that a restore will fit into a downtime window?


Yes. But I think I will hedge my bets - e.g. in case Client then says "Can we freshen up the Remote database every hour" or somesuch.

I'd forgotten about Snapshot replication, thanks, I'll take a more detailed look at that and report back.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000