Avatar of Gordon Tin
Gordon Tin
 asked on

What steps do i need to do when the publisher MSSQL DB in (PtoP Transactional Replication Failure)?

Background Information.

Server1 ( DB1)       <--> Application Server
|
|SQL2008 One wayTransactional Replication (Peer to Peer)
|
Server2 (DB2)   <-> WebServer for SQL query

1. The main Application Server is using DB1.
2. Users are querying informative from DB2.

I am in the planning stage of DB Replication.
When there are failure to Server1, i will point the application Server to use Server2 (DB2) to temporately resume all the operation(s).

My question is
Whatelse do i need to do to handle the stopped DB replication processes (in detailed steps)?

 
SQL Expert Please help.
Microsoft SQL Server 2008Windows Server 2008Microsoft Server OS

Avatar of undefined
Last Comment
Gordon Tin

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ashok Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anuj

I prefer logshipping here instead of transactional replication for high availability, because of performance overhead with transactional replication. You have the option to specify the fail-over partner in your connection string. So when your primary server goes down, restore the pending jobs in secondary server and configure re logshipping and set your secondary server as your Primary server. The connections will automatically redirected as defined in the connection string to the new failover server.
Gordon Tin

ASKER
You have brought up the advantage of the log shipping to which i have never considered before.

1. For log shipping, Could i use both Primary DB & Secondary DB at the same time?
For my purpose, i need to use Pri DB (SVR1) for Application & Sec DB (SVR2) for web reporting (SQL Queries).

How much more overhead  (%) is DB transitional replication over log shipping (estimation) ?
Could you expert provide some good log shipping installation guide(s)?

Thank you in advance.
Gordon Tin

ASKER
I read a good article on replication vs log shipping.
I am sure that replication is what I need because I need the standby server for reporting.

In the article, I am not 100 % sure about schematic alteration on both publisher & subscriber.
Quote
Is the schema altered at the publisher?  

Log-shipping and snapshot replication do not alter the publisher's schema. Updating subscribers (transactional and snapshot) and merge replication will add a guid column if there isn't one there already with the rowguid property. This may cause some queries on the publisher to fail, e.g. if you had TSQL inside a stored procedure which did the following:

INSERT INTO ExistingTable
SELECT * FROM ReplicatedTable

Is the schema altered at the subscriber?  

Log-shipping makes no alterations to the schema. Snapshot and transactional replication may make subtle schema changes; standard transactional and snapshot will not transfer identity attributes - they become normal numerical columns (int, smallint, numeric...) on the subscriber. Some DBAs try to get round this by doing a nosync initialization and ensuring the table on the subscriber has the Identity 'Yes (Not for Replication)' attribute, which essentially allows the replication process to do identity inserts. However, on use of the fail-over server this methodology fails as the internal identity counter has not been incremented, and use of DBCC CHECKIDENT to reseed may not work on columns created with this attribute (see BOL). This problem is not apparent if merge or queued updating subscribers are selected.

------------
Question:
For my case transactional replication, I will be using the subscriber DB for reporting through web server (allow user access). No data update to subscriber DB.
Do I have the above concern?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes