Primary Key Violation - Transactional Replication

Hi All,
I have setup a transactional replication between 2 SQL 2005 servers.
Unfortunately, I am getting the error listed below:
Replication-Replication Distribution Subsystem: agent
JFCIS3TRM02-JFJDAT-JFJDAT_PUBLICATION-JFCIS3TRM01-13 failed.
Violation of PRIMARY KEY constraint 'ARSTRUN_KEY_0'. Cannot insert duplicate
key in object 'dbo.ARSTRUN'.
Is this error being raised because I haven't enabled automatic range
management? If so, how would I fix this error?
Regards,
JN
Hi Paul,
The type of transactional replication setup is just the plain one, not the
one with updatable subscription.
I am not sure whether it is nosync or automatic as I used the wizard and I
didn't recall being asked for those settings.
Regards,
JN
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWzOLQD2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>I really need to know what type of transactional replication setup you have
>configured - plain, updatable (immediate or queued) and nosync or
>automatic...
> Cheers,
> Paul Ibison
>
|||That could be possible since I have created an ODBC connection to the
replicated database which an end user can select from a drop down list when
they open the application.
What do you suggest I do to get the two databases synchronized again?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OjdWiMF2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> OK - in that case my suspicion is that someone has entered a row on the
> subscriber. Is that possible? In this plain transactional case the
> subscriber data is supposed to be read only and only changed via the
> distribution agent.
> HTH,
> Paul Ibison
>

Primary & Secondary Server

Hello,

I would like to know how do I carry out this two cases in efficient way:

1. I have two SQL 2005 servers running. One as primary, one as secondary. I would like to synchronize or replicate the transactions at real-time.

2. When primary goes down, I would like my secondary server to take place in needless of IP changes and application settings.

If you have a good architecture on this, please share with me. I am not sure whether my questions are clear enough or not. I also will be reading some articles about these. Thank you in advance.

Regards,

Paing

Hi,

First of all this is not an easy question. You need to ask few questions to yourself

(a) What is the level of availability you want for data

(b) what is the down time you have

(c) what is the budget you have ; can you buy new hardware for cluster

(d) what is the SQL Server Edition u have

(e) what is the disaster recovery method you want to have

From your scenarion what i understand is , u want such an architecture that do not demand any DBA intervention when primary fails.

In this case what i feel is you may go for Database mirroring or SQL Clustering. But the later one needs extra hardware and windows licensing etc. If you have that it is well and good. Otherwise you can go for Database Mirroring Highavailability and Synchronus Mode. As you said replication can be real- time(transaction replicaiton) but when the primary fail , the secondary will not up automatically. Replication Need DBA intervention.

Before going for implimention you need to read a lot. there are many article available in net. Search for "High Availablity and Disaster Recovery in SQL 2005.

I hope this could lead to your goal. Keep posting your observation....

All the best

Madhu

|||

Hi,

Thank you for your reply.

a). I would like to have as far as possible. Means, I would like to have up to the last transactions that made just before primary went down.

b). My customer will not be able to afford even a minute downtime as they're having at least 3 or 4 transactions per minute.

c). Currently have two Windows 2003 Servers running. Servers' hardware are at their standards (I don't remember the spec tho).

d). Using SQL Standard Edition 2005.

e). This is the plan for DR and am not sure which method to go. If you have any recommendation, I'd much appreciate.

I would like to know a recommendation which is well known plan and best practice. We're still at gathering information on how-to. But my customer said they do not want to go for clustering. I do not know why. I will need to talk to their DBA later on if it is recommended here. I am hoping to hear more from you experts on this topic. I will do research on my own as well. And thanks for the topic tip.

Paing

|||

For Clustering your OS should be either Windows EE or Windows Datacenter. So first you should confirm this. If you want a High availability system which has server wide failure support and better performance and you have enough budget and your Operating system is EE or Windows Datacenter the first choice should be Clustering. The problem in database mirroring is it does not support Server wide failure. It supports only the database failure. So it is a good point for debate.

Database mirroring on SQL Server 2005 Standard Edition does not have all the features in Database mirroring. Feature like Database Snapshot, Parellel Redo, safety=off are not supported in Standard Edition. It is again is a new feature in SQL 2005 . For automatated failover in database mirroring you may need 3 servers.

So , what I would say is , you read these two option in detail and need to take a dicision very carefuly according to the avialable OS and Sql Server resource.

All the best and happy new year

Madhu

Preventing access to SQL Server from other Servers

I'm using MSSQL7, NT authentication and application roles so only my
application can access the data. Also, other applications (like Excel) can
not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL
Server on the network, it is allowed to see the list of tables, SP, etc. It
is not allowed to open the table, but the Import/Export wizard is working and
will allow retrieving data from the secured tables.
If I change to MSSQL authentication, any user will be able to access the
data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where
any VPN connection with valid user name and password can actually log in to
the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so
logins must exist. I just don't want other SQL servers on the network to be
able to connect to and import/export, view table and SP, etc.
Any ideas?In your Windows Server environment, Create a Windows Group.
Put your users who will use your application which uses SQL Server
databases, into that Windows Group.
Create a Login for that specific group in SQL Server. Assign necessary
permissions to that Login.
So, only that specific User Group will be able to reach your databases, not
all users in your domain.
--
Ekrem Ã?nsoy
"ben_634" <ben634@.discussions.microsoft.com> wrote in message
news:1B8EE3F5-15C3-4009-AD42-25DFA6E37A60@.microsoft.com...
> I'm using MSSQL7, NT authentication and application roles so only my
> application can access the data. Also, other applications (like Excel) can
> not access the data and read it. So far, so good...
> Yet, I noticed that if I try to access the SQL Server from another SQL
> Server on the network, it is allowed to see the list of tables, SP, etc.
> It
> is not allowed to open the table, but the Import/Export wizard is working
> and
> will allow retrieving data from the secured tables.
> If I change to MSSQL authentication, any user will be able to access the
> data from my application and I don't want that either.
> Unless I'm missing something, this is a big problem, especially today
> where
> any VPN connection with valid user name and password can actually log in
> to
> the domain and therefore connect to the database via SQL Server.
> By the way, the server still must allow access to users via applications
> so
> logins must exist. I just don't want other SQL servers on the network to
> be
> able to connect to and import/export, view table and SP, etc.
> Any ideas?|||Hi
It sounds like you are connecting with a higher priveleged users such as an
system administrator. In which case they will have access to the tables and
data. You should make sure that you restrict access to high privileged
accounts and that other users have the minimum permissions needed to do what
is needed to do before you set the application role.
John
"ben_634" wrote:
> I'm using MSSQL7, NT authentication and application roles so only my
> application can access the data. Also, other applications (like Excel) can
> not access the data and read it. So far, so good...
> Yet, I noticed that if I try to access the SQL Server from another SQL
> Server on the network, it is allowed to see the list of tables, SP, etc. It
> is not allowed to open the table, but the Import/Export wizard is working and
> will allow retrieving data from the secured tables.
> If I change to MSSQL authentication, any user will be able to access the
> data from my application and I don't want that either.
> Unless I'm missing something, this is a big problem, especially today where
> any VPN connection with valid user name and password can actually log in to
> the domain and therefore connect to the database via SQL Server.
> By the way, the server still must allow access to users via applications so
> logins must exist. I just don't want other SQL servers on the network to be
> able to connect to and import/export, view table and SP, etc.
> Any ideas?