MS SQL replication

I never worked with the replication technique before.

I would like to transactionally replicate only table data. But if I don't switch to "True" all the table articles "Copy Object and Settings" properties the constrains,triggers and other table attributes are disappear on the subscriber server.
What is a correct approach to copy only table data content, not touching any of the table satellite objects?

Another question, I set in the publisher properties to use an FTP server, but according to the FTP server's log, nobody is trying to even connect to it. What could be wrong? Which server is actually supposed to connect to the FTP? Is it the subscriber?
LVL 20
zc2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Loganathan VSoftware EngineerCommented:
You do not use FTP for replication use TCP/IP.

Actually, Replication has three types :

Snapshot Replication
Transactional Replication
Merge Replication

Which one you require first. According to that, proceed your work.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
But if I don't switch to "True" all the table articles "Copy Object and Settings" properties the constrains,triggers and other table attributes are disappear on the subscriber server.
I'm not sure if I understood it. Can you support your statement with screenshots?
By default only data, collation, clustered indexes and constraints are replicated.

I set in the publisher properties to use an FTP server, but according to the FTP server's log, nobody is trying to even connect to it
The FTP option is to be only used for Snapshot and the subscribers needs to manually download the snapshot from the FTP site. The Snapshot agent in the Publisher side is the one that puts the file in the FTP server.
0
zc2Author Commented:
Can you support your statement with screenshots?
Here you go:
Screenshot of the "Properties for All Table Articles" BTW, this raises another question. As I mentioned in my original post, I do set all the "Copy Object and settings to subscribers" properties to "True". But after a snapshot is done, when I open up the dialog, they are all back to default values (not sure that those are the default, but as you can see on the screenshot, most of them now set to "False"). Is that an expected behaviour?
By default only data, collation, clustered indexes and constraints are replicated.
But in a case when some object is not marked for replication, is it supposed to be dropped on the subscriber? Because this is what happened in my case.
The FTP option is to be only used for Snapshot
Did you mean here the Snapshot publication type or initial snapshot uploading in Transactional publication type?
the subscribers needs to manually download the snapshot from the FTP site
Not sure what you mean. Manually, means with an user interaction, using some FTP client software? Sounds very strange.  
The Snapshot agent in the Publisher side is the one that puts the file in the FTP server.
Yes, that's true. The snapshot is created by the Snapshot agent in "ftp" subfolder instead of "unc".
I tried to switch to ftp snapshot because I hoped that FTP will be upload files faster than bcp does that from the distributor server, and then bcp will be executed on the subscriber. Is that actually supposed to be if I use the "pull" type of subscription? Currently all my subscriptions are the "push" type. I chose "push" to make all the publisher transactions instantly appear on the subscriber. And that actually works very  quickly. But I cannot find any info, how exactly does the "pull" subscribing work. Does the subscriber periodically inquires the distributor for updates, or the distributor actually notifies the subscribers about new transactions and then the subscribers do the job? Please explain.
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Vitor MontalvãoMSSQL Senior EngineerCommented:
But after a snapshot is done, when I open up the dialog, they are all back to default values (not sure that those are the default, but as you can see on the screenshot, most of them now set to "False"). Is that an expected behaviour?
I think I know what's happening. When you set to all tables, it means it will spread the new configuration values to all tables and you can only confirm that by checking table by table. If you return and try to set to all tables, it will show again the default values but the previous configuration is already set for each table in the Publication. So, yes, it's the expected behaviour.

But in a case when some object is not marked for replication, is it supposed to be dropped on the subscriber? Because this is what happened in my case.
It shouldn't. Only selected articles are recreated in the subscriber.

Did you mean here the Snapshot publication type or initial snapshot uploading in Transactional publication type?
Initial Snapshot for any type of Replication.

Not sure what you mean. Manually, means with an user interaction, using some FTP client software?
To be honest, I don't have any experience by using FTP with Replication. I just told you what's in the Replication's FTP option. I might have made some kind of confusion when read the MSDN article but here's the instructions if you didn't read it already.

Does the subscriber periodically inquires the distributor for updates, or the distributor actually notifies the subscribers about new transactions and then the subscribers do the job?
Yes, that's the Pull option. But this should be used only when you have dozen or hundreds of subscribers so you can take some load from the Publisher server.
0
zc2Author Commented:
Ok, let's set aside the FTP snapshot distribution.

When you set to all tables, it means it will spread the new configuration values to all tables and you can only confirm that by checking table by table.
You were right, the properties for individual table are all still shown as True.

My main concern is how to replicate the database data, that the subscriber's copy be in a working state, with all the index, keys and constrains correctly applied.
Currently the subscriber DB is not functioning. For example take one table 'UserLogins'. I generated the CREATE script for the table, and both on the publisher and the subscriber it looks alike:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UserLogins](
	[LoginID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[UserID] [int] NOT NULL,
	[EntryDate] [datetime] NOT NULL,
	[IP] [varchar](50) NULL,
 CONSTRAINT [PK_UserLogins] PRIMARY KEY CLUSTERED 
(
	[LoginID] 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

ALTER TABLE [dbo].[UserLogins] ADD  CONSTRAINT [DF_UserLogins_EntryDate]  DEFAULT (getdate()) FOR [EntryDate]
GO

Open in new window

But the following command
insert into UserLogins(UserID,IP) values(3,'127.0.0.1')

Open in new window

works fine on the publisher, but gives me the following error on the subscriber:
Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK_UserLogins'. Cannot insert duplicate key in object 'dbo.UserLogins'. The duplicate key value is (2).
The statement has been terminated.
Is that supposed to be? The subscriber's DB is only to be treated as read-only?

Here I put all the snapshot files for that table except .bcp ones.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you run the snapshot agent before?
The snapshot is the one that will set the initial articles to assure they are equal in both Publisher and Subscriber. Otherwise you can't guarantee that they have the same data.
NOTE: Snapshot agent will drop and recreate the articles, meaning that any existing data will be deleted and replaced with data from the Publisher.
0
zc2Author Commented:
Did you run the snapshot agent before?
I have to run the snapshot agent to recreate a new snapshot every time I make even a small change in the publisher properties.

That's fine that all the subscriber's data are deleted and then copied from the distributor all over on each snapshot redistribution.

I want the subscriber server will be fully ready to be used in a case of a failover. But as I described in my previous comment, it gives an error on a simple insert operation. Could you please explain why and how to fix that?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't have access to your system but the only thing it pass on my mind is that somehow the records are being inserted or by someone or by some other process or perhaps from a trigger. You must investigate this and find what's records to be inserted besides the snapshot.
0
zc2Author Commented:
Nothing is running on the subscriber. After the initial snapshot replication is done I just opened the SSMS and was trying manually execute an insert. On the publisher a record can be inserted, on the subscriber it fails with the error I posted. I expect the subscriber server be a fully functioning exact copy of the publisher, but since it refuses to execute even a simple insert operation, the subscriber server cannot be used in a case of failover.
Is that an expected behaviour that the subscriber is a read-only (no new records allowed to the tables with a primary key) copy of the publisher and cannot be used by a real application?
If not, I provided here all the scripts the replicator uses to re-init the subscriber table, could you please look at them and tell why the subscriber's table does not let to insert records?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can post the scripts but I'm afraid only with access to your system, a good analysis can be made.
IMHO, something is adding records in the subscriber database. I can't see any other justification for that error.
0
zc2Author Commented:
The reason I getting that error on the subscriber server is dropped autoincrementable identity value:
select MAX(LoginID) as 'Maximum value of PK column' from UserLogins;
select IDENT_CURRENT('UserLogins') as 'Current auto incrimentable identity value'

Maximum value of PK column
--------------------------
333609

(1 row affected)

Current auto incrimentable identity value
----------------------------------------
4

(1 row affected)

Open in new window

Could you please suggest how to fix that?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see. It's an issue when using Identity columns as Primary Key.
You can configure the Replication to handle these cases. Follow this MSDN article.
0
zc2Author Commented:
Thank you for the link.
If I understand that correctly, I need to set the identity range to an appropriate value before the subscriber DB is going to be used by an application. I found that it is possible to do that for all the tables at once with the following command:
EXEC sp_MSForEachTable 'IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 DBCC CHECKIDENT (''?'', RESEED)'

Open in new window

Or, if I need insert records to a table when the replication is still active, I can do something like this:
DBCC CHECKIDENT (UserLogins, RESEED, 10000000)

Open in new window

I put those to the publisher config, to be executed after the snapshot is applied. But it still need to be executed again before using the DB.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
To be honest I never faced this kind of issue in my Replication solutions and that's because I always avoid to use IDENTITY columns as PK.
For what I read the default configuration is to let the Replication to manage this automatically but it looks that didn't solve your issue. If you'll never going to add records in the Subscriber then you can configure those IDENTITY columns to be disabled in the Subscriber. Meaning that they won't be replicated as IDENTITY column so when replicating from the Publisher it won't trigger any error.
0
zc2Author Commented:
I always avoid to use IDENTITY columns as PK
You are absolutely right, such a way of handling the primary keys is a disaster.
If you'll never going to add records in the Subscriber
It's not that. The subscriber is a fail over server. So in a case the publisher goes down, the subscriber must be fully ready to accept new records be inserted.

I have another question, may be you could give a quick advice, if not, I will open an other question topic.
I turned off the procedures and function articles replication, because even if a function is obsolete and never used, a syntax error in its body or mentioning of a not existing table field completely stops whole replication to the subscriber. How can I make the replication go on even if some article failed?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should fix the error in the Publisher so it can be replicated.
Another option is to remove the problematic object from the Replication if you won't use it. This way it will never be replicated.
0
zc2Author Commented:
I posted here a solution I found on the internet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.