Solved

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

Posted on 2013-01-03
4
226 Views
Last Modified: 2013-01-11
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.
0
Comment
Question by:Gordon Tin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
Ashok Kumar earned 500 total points
ID: 38739303
Immediate steps are

1. Alerting concerned Support groups by any possible means, like email - alert.
2. A support ticket should be raised, immediately, so support groups can start to work on DB1.
3. A minimal (3 times ) try of automatic restarting the Primary DB. if it fails, go in for SOS.
4. SOS - Support ticket be raised / previous ticket be categorized to SOS, meaning high priority or critical.
5. if it gets restored with auto-restart, DB2, transactions should get synchronized to DB1 before DB1 resuming as application server.
6. Support ticket should be closed, containing extensive details of the issue & resolution for technical aides.
7. DB1- Restored Alert to concerned support groups must be sent.
8. Issue & Resolution should be routed to Tech-KB, for future reference.
9. if auto-restart method fails, SOS-ticket is still open, an alternative DB should be restored from most recent back-ups & same be synchronized with DB2.  When ready DB3 be tested for its stability / performance / loading etc, till its equally considerable with DB2.
10. Replace DB3 as DB2.
11. Close the Support ticket, with detailing. But for knowing the exactness of failures on DB1, try knowing the exact reason ( including power problems, hardware problems, software problems), and same be documented along with the ticket details as a postmortem.
12. Above should be added into Tech - KB,
13. If DB1 could be start running , check the system / application logs, and also sync the same with DB2, but test the system again for stability / performance / loading etc for a period of time.
14. For some time, use it as querying server, as secondary back.

Support Tickets have category (SOS - Ticket) would have highest priority, & the time taken to close should be minimal.

I assume my explanation is understandable . There can be few more steps added.
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38739351
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.
0
 

Author Comment

by:Gordon Tin
ID: 38746012
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.
0
 

Author Comment

by:Gordon Tin
ID: 38747719
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?
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question