I've been considering implementing a P2P Transactional Replication concept using three different sites. My qustion is:
Instead of have records only available for editing by specific sql servers (partitioning), can I implement some sort of record locking? For exampel all sql nodes have r/w access to all data unless one sql node has the record locked?
How do you skip just one record in transactional replication when you have an error?
I know we can add the -skiperror flag to the distribution agent to skip all errors (for a particular error number). But if we do that, can we get detailed information on every record that was skipped?
My goal is to be able to identify every row that cannot be replicated and has to be skipped.
Thanks!
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies View RelatedI have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),
SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
Hello,
I need help in record locking. As soon as user retrieve records I want to lock those records in database. When other user try to retrieve records from the same table i want to retrieve records those are not locked by other user. How do I do this? Please help...
Thanks in a millian.
Sarika
Hello,
I have a database programmer who in his latest application has deployed manual record locking into code to release certain records to certain users of the application at specified times.
Does anyone know how manual record locking will affect the SQL 2K system? If at all? Are there some standard practices with manual record locking that should be enforced?
Thanks,
Brent.
Project spec:
If user is making Delivery Order for sales order,
the other user cannot make Delivery order for same sales order
How to lock a sales-order record, so other user cant use it, except for reporting (read only). I dont want to use a field since it has a lot of weakness. I am using VB6 and connect with ADO
Thanks
Currently I am working on asp.net Intranet system.I need some suggestion on records locking for Sqlserver & ASP.net.
I want to prevent two user to open the same record for modify.Means if some one has open one record (id=xxxxx) for modify then others should not be able to open same same record (id=xxxxx) for modify .otherwise if both user will open/update same record then information in database will not be updated correctly.
Any idea how we can do the record locking so that not two users can open same record(id=xxxxx) for modify.
Thanks in Advance
Arvind
I am writing a VB program, which needs to update record A in one table using transaction control (BeginTrans & CommitTrans). But due to the page-level locking, before the "CommitTrans", other users couldn't not read other records in the same table.
Is there any way that I can do the record/row level locking? If possible, could you provide me some VB source code? Such as how to use "DBCC ROWLOCK" or how to set for row-level locking.
Thanks for your kind help.
Hi all,
I'm working on a Planning Databse for a factory that allows jobs to be allocated to machines and to be given a priority. This info. is displayed on an Access 2000 form e.g.
MACHINE ONE MACHINE TWO
Priority Job Number Priority Job Number
1 J111 1 J654
2 J562 2 J865
3 j231 3 J821
Both the Production Manager and the Factory Manager have this form open at all times. The problem is this, if for example the Factory Manager changes the priority of a job on his PC, the change is not shown on the Production Managers PC (cos' his screen has not been refreshed with update) The Production Manager therefore has old data on his screen and could mistakenly think that the job priority had not changed at all...
I have managed to write some VBA code to get round this but I was wondering if there was any way of 'pushing ' changes to the front-end when they are made or of using record-locking etc. to achieve the same affect...
Thanks
M
I am running an Access 2000 front end and MS SQL2000 as a backend and I am having problems with record locking.
I have just found out that If you use ODBC then Access acts as if No Locks are set. In an ideal world I need the record to be locked by a user as soon as they move onto it. If not as soon as they start editing it?
Cheers.
Hi there,
Up until this point I've been quite lazy in trying to understand how record locking really works in SQL server however one of my applications now has a bug so the time has come for me to take the issue seriously.
Basically, a user clicks a button in my app and updates a field in a table in SQL Server (say at 10:34:55). At the almost exactly the same time (lets say 10:34:57) another user tries to update the same record (not necessarily the same field) but the second users update is not connected to the database.
How can I store the second users update and then commit to database once the first user is finished...is it possible or do i need to approach this from a different angle?
Thanks
I'm trying to figure out what happened.I have have two Tables in SQLServer called "Contacts", and "Jobs"Whenever someone changes a record in either table I update two fieldscalled "Modby" and "Moddt" with a trigger. The Trigger may alsocontain other things as well.Here's the wierd thing.If the Modby Moddt statement is the first statement in the trigger Idon't get a "record changed by another user" but if it comes later inthe trigger I do.I think it has something to do with the LockDelay Key but I'm not sure.Any Ideas?
View 3 Replies View RelatedIs there a way using ADO.NET to lock a record so that you can performan atomic Test and Set operation on one of its fields?I want to set an application level lock bit on a record's field (set itfrom 0 to 1) and at the same time determine that it was 0 before Ibegan and that I was the one who set it to 1, or that it was 1 before Igot to it (and thus I do not own the lock to the record).I don't see anything that explains if this can be done using ADO.NETusing MSSQL. Any help is greatly appreciated.
View 3 Replies View RelatedI found this interesting paper at http://ftp.sas.com/techsup/download/v8papers/odbcdb.pdf, comparing OLEDB to ODBC.
It says that OLEDB provides record locking where ODBC doesn't. I can't quite reconcile that statement; what does it mean? How is the access layer involved in determining type of locks used by the server in executing a statement?
.B ekiM
From SQL Server Books Online, there is a topic: Concurrency Effects, and did mention the following side effects:
- Lost updates.
- Uncommitted dependency (dirty read).
- Inconsistent analysis (nonrepeatable read).
- Phantom reads.
Can someone please tell me which type of Isolation Levels in the Database Engine to avoid the above side effects respectively.
For Record Locking issue:
Example, two editors make an electronic copy of the same document. Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. The editor who saves the changed copy last overwrites the changes made by the other editor.
This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.
For the above example, is it possible to do like this way:
Editor 1: SELECT and Lock a record
Editor 2: Before SELECT the record, check for the record whether it is locked or not. If it is not lock, then the record can be selected
Please advise.
Thanks.
I have built my own functions in the past that involve a timestamp and record lock column in each table (to support some other DB).
I am currently using SQL 2000 and was wondering if there is a better (OK, easier) way to lock records and even prevent edits from taking place when a record is open.
Any input appreciated.
I have a problem with record locking / blocking within an application.The app is quite straight forward. Written in Delphi 5 using BDE toaccess a SQL 7 database (Win2K server).Every so often the users (when they bother to tell me) find that theapplication locks up and they are unable to work. No errors areproduced (error trapping in the app is good). They 'shout round' toeach other and get someone to exit the data entry screen. This seemsto free up the locking/blocking issue.There are about 50,000 records in the table (script below) and it isaccessed through a simple query (script below). All users will accessthis in the same way. I'm assuming that a new record is being editedwhen the problem occurs, but this shouldn't cause locking/blockinguntil it gets committed (right ?).The problem is tracking down the source of this and finding thepattern which I can work back from. I've used Erland's aba_lockinfoscript (a few months back admittedly so will re-visit this), butnothing obvious is jumping out at me.No other tables should be in use at this point.Any suggestions ?ThanksRyan/* Code for query component - users navigate to the record they needto editthere could be 15,000 records showing as outstanding - perhaps thisis the area that I need to re-visit so that less records can be edited? */SELECT *FROMPostReceivedWHEREToDelete = 0 ANDCompleted <> 1ORDER BYPostID/* Table in question */if exists (select * from sysobjects where id =object_id(N'[dbo].[PostReceived]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[PostReceived]GOCREATE TABLE [dbo].[PostReceived] ([PostID] [int] IDENTITY (1, 1) NOT NULL ,[Type] [varchar] (100) NULL ,[ClientsName] [varchar] (100) NULL ,[DateReceived] [datetime] NULL ,[EnteredBy] [varchar] (100) NULL ,[AssignedTo] [varchar] (100) NULL ,[DateAssignedTo] [datetime] NULL ,[Adviser] [varchar] (100) NULL ,[TargetDate] [datetime] NULL ,[CompletionDate] [datetime] NULL ,[Completed] [bit] NULL ,[KeyAccount] [varchar] (100) NULL ,[Notes] [text] NULL ,[Specific1] [varchar] (20) NULL ,[Specific2] [varchar] (20) NULL ,[Specific3] [varchar] (20) NULL ,[Specific4] [varchar] (20) NULL ,[Specific5] [varchar] (20) NULL ,[ToDelete] [bit] NULL ,[EnterUser] [varchar] (20) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
View 3 Replies View Relatedcan someone pls show me a way to get an unique sequence at below senario:
PC1 & PC2 using their own local client progam to access to Database Server at SERVER1.
In the SERVER1, there is a table SEQUENCE in a database DATABASE1.
And the table's structure of SEQUENCE are SeqType & SeqNo.
Here is the sample data:
SeqType SeqNo
Invoice 100
DeliveryOrder 200
Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time?
Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?
pls advise. thanks.
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
We have a linked server configured on our SQL Server 2005 server that points to our iSeries. A step in a scheduled job successfully updates data in tables on the iSeries using an update query and the 4-part naming convention (job reports success and updates can be verified), but it appears to leave locked records and open connections on the iSeries. No open sessions showing in SQL Activity Monitor.
The linked server is configured using Microsoft's OLEDB Provider for DB2. Are there additional settings that can be configured on the linked server to prevent this behavior or is this an issue that need needs to be addressed on the iSeries? A timeout setting perhaps (not sure if this fixes the record locks though)?
Any help is greatly appreciated.
KS
On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.
Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.
Thanks!
Kavira
I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me
Thanks
I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -
The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'.
Access is denied.
The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the (
epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.
What am I missing?
Hello,
I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...
Thanks
Hi guys can anyone help me with this issue. if so thankyou kindly.
1. created transactional publication (for tables, stored procs & views )
2. created empty database at subscriber
3. set up pull subscription at subscriber
4. ran the initial snapshot agent at the publisher
5. checked the schema files & found out it hadn't scripted the primary keys on the tables.
Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.
? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?
? Should I break my publications into 3; say 1 for each object?
Thank you guys for taking the time to read this.
Hi
I am tring to implement transactional replication.
How do i publish tables which donot have primary keys?...because transactional replication seems to publish only the tables which has a primary key.
Thanks
Madhukar
Hello all! I am setting up my first Replicated database, and want to get this right.
We are a Newspaper, and I will be Publishing the database that is the back end of the web server. The goal is to load balance the web site as well as to have a second identical server in case something goes wrong. Both the Subscriber and the Publisher need to be kept in sync as new articles are posted, so my client will just be updating the Publisher. But I'd like to keep a single set of usage statistics, so I was thinking of using Immediate Updating so the Subscriber can update the Publisher using two-phase commit.
What will happen if the Publisher shuts down and an update is attempted on the Subscriber? Will it just hang? Let's assume the worst - what if my Publisher is kaput? How do I determine that my other database is a Subscriber; and how do I stop it from being a Subscriber?
Thanks in Advance,
Hi
I have a requirement for pushing transactional publication to various subscribers. Each subscriber will need to receive a different subset of rows from a table. So I need all rows published, but with horizontal filtering at subscriber level. Is there a way of applying filtering at the subscription level.
Thanks
Tariq
I am using transactional replication between two SQL 2005 databases. On Sunday evenings, we run a maintenance plan that rebuilds the indexes on the publisher DB. When the plan runs, the replication receives errors and cannot continue. The error appears to be at the publisher level. The publisher runs continually and the subcriber runs every 10 minutes. The error states "cannot run sp_replcmds" Other than Sunday, everything runs fine.
Thank you