Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2012-12-03 : 07:10:41
|
| 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
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-04 : 06:58:53
|
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 timeAdvantage 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? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-05 : 11:03:54
|
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. |
|
|
|
|
|
|
|
|
High Availability Staging Database