Checking / Opinion ?

I am taking these steps in a test environment;
Publishing database
1. Changed identity columns = Not for Replication (Hilary's script)
2. Scripted all Triggers & changed to "Not for Replication" (Is there a method to do this with a script?)
3. Added Primary Keys where not present as follows;
Alter Table x
add pk_col int identity(1,1) NOT FOR REPLICATION
Alter Table x
add constraint x_tbl_repl
primary key(pk_col)
4. Backed up Publishing Database
5. Restored Publishing Database with a new name = Subscribing Database
6. Setup Transactional continuous replication from Publishing Database to Subscribing Database
The reason I did everything in the publishing database is so that it's all set in the Subscribing database.....Make sense?
I am going to have a production oltp database that replicates all transactions to my reporting database. I also want to think of my reporting database as sort of a fall back position. If anything goes wrong in the oltp publishing database, everything resides in the subscribing db.
It is my understanding that I wanted the Identity columns in the subscribing database to just be written to, not incremented, therefore, the necessity of the Not for Replication.
The same goes for triggers in the subscribing database, I don't want them firing when replication occurs, therefore, the Not for Replication.
I don't have any inserts as follows;
Insert into Table A (Table A may have a newly added pk_col )
Select * from Table B (Table B may have a newly added pk_col )
therefore, I should not have any problems with my stored procedures.
Do I have it all covered? Am I on the right track? I really want to get this implemented, and correctly, to get this monkey off my back.
Any comments will certainly be appreciated!
This is not the standard way of deploying subscribers, but it does work. I just tried it.
The problem is when you go to do a resync. When a no-sync subscription needs to be re-initialized you must drop and recreate your subscription. You have the potential of having data consistency problems with your approach.
Also, it sounds like you might be thinking you are implementing bi-directional transactional replication with your method. You aren't. Its not clear from your post if you are or not. But the "fall back position" could be interpreted as this.
If you fail over to your Subscriber you will have to manually resync your Publisher with your Subscriber when it comes back on line.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I am only doing Transactional Replication in one direction. What I want is
to have a production OLTP database that replicates to a reporting database.
The reason I set everything up in the test production database is so that
these databases mirror one another from the start, and hoping I would not
miss anything.
If the schema needs to change in the production database, I intend to make
the same change in the subscriber. We have change management, where the
scripts are sent to me and I execute them over production. I am hoping that
it will just be a matter of running the scripts to create the objects
(views, sp's) in both databases to keep them in sync object wise.
I don't understand what you mean that I may face problems when doing a
resync, what am I doing wrong, or what is wrong with my approach to setting
up a reporting database, how would you approach setting up a reporting
database that is replicated to from the production database?
Am I out in left field making this project more difficult than it needs to
be?
For fall back I was thinking that since all transactions are being
replicated from production, if something went wrong in the production db, we
could just point the users to the subscribing database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:B8A63257-FE63-4673-AD55-9494C453404B@.microsoft.com...
> This is not the standard way of deploying subscribers, but it does work. I
just tried it.
> The problem is when you go to do a resync. When a no-sync subscription
needs to be re-initialized you must drop and recreate your subscription. You
have the potential of having data consistency problems with your approach.
> Also, it sounds like you might be thinking you are implementing
bi-directional transactional replication with your method. You aren't. Its
not clear from your post if you are or not. But the "fall back position"
could be interpreted as this.
> If you fail over to your Subscriber you will have to manually resync your
Publisher with your Subscriber when it comes back on line.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>