March 6, 2008 at 9:19 am
We have a very large db and presently we load an extract file from our client every hour into this db. This is the only way data is entered, deleted, or changed from this db and it is crucial that these files are loaded as quickly as possible and this db is always contains the most recent data from the extract files.
During the file loading process production was always very slow, so we have decided to load the files on one server (that will only be used for the load) and replicate the changes to our production server.
This will be a one way push transactional replication.
My goal is to minimize downtime on the production box as much as possible.
We will have the same data in both databases and the imports will be directed to the new server and at that point I will create the replication publications and set the production database as the subscriber.
I would like to avoid the initialization as this could take a lot of time and during this time production would be severely affected.
I see in the gui options I have the option on initialize when to set it to never. Will this accomplish this?
Also, I have used both snapshot and transactional replication in the past, but never without initialization. Are there any pointers or anything I should be aware of?
This is a mission critical piece and I really appreciate any and all help on this!
April 9, 2008 at 7:41 pm
hi,
not sure if this is the answer you are looking for or you have found your own solution.
In my environment, I take backups from one server and restore it to the other, never having to do a snapshot or any type of initialzation because data and structure are the same on all servers.
use [dbname]
exec sp_addsubscription
@publication = N'pubName'
, @subscriber = N'subName' --CHANGE value here!
, @destination_db = N'destDbName'
, @subscription_type = N'Push'
, @sync_type = N'replication support only'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 0
The @sync_type = N'replication support only' can only be used via a script. you will not get that option via the GUI.
hope that helps.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 14, 2008 at 10:05 am
1. most PROD db's will be Recovery=FULL, probably with Log-Shipping to Disaster Recovery (D/R)
2. a 100% reload (500,000 x DELETE plus 500,000 INSERT) will be expensive
3. transactional repl (or FK) willl prevent you doing a TRUNCATE TABLE [would be much quicker]
4. an hourly event should not be done as transactional pub [granted you DID mention Snapshot]
here is an alternative
1. have a secondary "Staging" db with Recovery=SIMPLE to contain this data
2. do a BULK INSERT into CUST_TEMP table (500K rows)
3. do
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblCustOLD]') AND type in (N'U'))
DROP TABLE [dbo].[tblCustOLD]
EXECUTE sp_rename N'dbo.tblCustData', N'tblCustOLD', 'OBJECT'
EXECUTE sp_rename N'dbo.tblCust_TEMP', N'tblCust', 'OBJECT'
COMMIT
so do NOT use repl or buy a second expensive box
- suggest you send $$$ to me instead (I recommend MSF charity where I work as volunteer!)
down-sides of having a STAGING db alongside any other PROD db are
A. that you can't enforce referential integrity (DRI)
B. you would have to use another mechanism to get the flat-files across to DR (eg VSS or DT)
incidentally, your customer sending you the whole dataset rather than just the deltas is lame
- having a flag that is set (e.g. trigger) on change is an easy way to decide if changed/not
HTH
Dick
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply