January 12, 2016 at 6:17 am
Dear Team,
The Query is related to Transactional Replication on SQL Server 2012.
Scenario :-----
I have two SQL Servers :
"Server_Dist" will act as Distributor & Publisher.
"Server_Sub" will act as Subscriber.
A database [TestDB] exists on "Server_Dist" with following table definition
CREATE TABLE [dbo].[EmpMast]
(
[RowID] [int] IDENTITY(1,1) NOT NULL,
[Code] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[DeptCode] [int] NULL,
CONSTRAINT [PK_EmpMast] PRIMARY KEY CLUSTERED
(
[Code] ASC
) WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
My Application is connected to "Server_Dist" through a connection string.
Through the Application, I have inserted 10 records in [Server_Dist].[dbo].[EmpMast] table.
I have taken [TestDB] database backup from "Server_Dist" and restored on "Server_Sub" so as to make database exactly same on both SQL Servers.
I am using Transactional Replication.
While configuring Publisher on SQL Server "Server_Dist", after selecting Tables, I have choosen following Properties of Table Articles
1. "Copy Options & Setting to Subscriber" ==> All options set to "TRUE"
2. "Destination Objects" ==> SET "Action if name is in use" = "Truncate all data in existing Objects"
3. Rest options set with their default values.
After successfully configuring & executing Distribution, Publication & Subcription Agents, My Transactional Replication is working fine.
I added 5 records in [Server_Dist].[dbo].[EmpMast] through Application which got successfully replicated on [Server_Sub].[dbo].[EmpMast].
Queries :-----
1. If SQL Server "Server_Dist" goes DOWN, Can I make SQL Server "Server_Sub" available for Application ?
2. If "1." is valid then Can Application insert data into [Server_Sub].[dbo].[EmpMast] ?
3. If "2." is done successfully, what will be the effect on IDENTITY column "[RowID]" of [Server_Sub].[dbo].[EmpMast]?
4. If "2." is done successfully, what should be the values in IDENTITY column "[RowID]" [Server_Sub].[dbo].[EmpMast]?
5. If "1.", "2.", "3." & "4." are successfully running and meanwhile If SQL Server "Server_Dist" goes UP, then Can I make SQL Server "Server_Dist" available for Application again ?
6. If "5." is successfully done, then how it will effect on Distributor & Publisher which were already present on SQL Server "Server_Dist" ?
7. Will Transactional Replication run successfully as that was running before going "Server_Dist" DOWN ?
8. To execute Transactional Replication in above scenario (queries 1 to 7), what provision I should make with either Database level or Replication configuration level.
January 12, 2016 at 8:25 pm
Hi,
Let me try to answer you on that.
1. you can, in fact, you can even use it even Server_Dist is up. That how T-replication works 🙂
2. You can insert data to Server_Sub even if Server_Dist is up
3. When insert into Server_Sub, it will use its own seed, meaning it will start from 1 onward
4. like in 3, it will start from 1 onward and follow your seed
5. If Server_Dist is back, it will continue to replicate, given its not down over the threshold you set in replication. Otherwise the subscription might expire and need to re-init again.
6. answered in 5
7. answered in 5
8. not sure what you meant.
In general, it might not be the way you look for. You might better off set "Not For Replication" for your ID field, which then it will follow the same seed of what you set in publisher (using different part of numbers. eg, starting from 1001 onward etc). You might also notice that you can update Server_Sub even Server_Dist is up, that will be something you should look out for. Do note that T-replication with updatable sub has been removed in sql 2012, if you want to have data replicate back, you can either use merge or peer-to-peer replication.
Hope this helps.
January 15, 2016 at 12:34 am
Dear Sir,
Thanks for your reply.
As suggested, I tried these steps except M-Replication.
But the things did not executed as per expectations.
I have a DB with tables having IDENTITY column, PK, FKs, Default bindings as values & function and a huge data in it etc.
During T-Replication, when 1st DB snap gets executed with "Drop & Recreate Objects" method at Destination, all FK references and few default bindings (mostly those having functions as default binding) gets removed.
Also even though IDENTITY column is setwith NOT FOR REPLICATION at both Publisher & Subscriber end, when data gets inserted at Subscriber, IDENTITY starts from 1.
Need to do a manual DBCC to reseed IDENTITY value for every Tables that have IDENTITY column on Subscriber.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply