Changing Replication Topology
Apr 2, 2015
I have a replicated database on 2008 R2 being moved to a new instance with 2014. Application regression testing is complete and all tests are good.
The current transactional replication topology is Publishing subscriber: the distributor is local and pushes to a subscription on an intermediary server. This server then has an identical publication and local distributor which then pushes to a subscription on a customer's server. There's no business case for this setup, it's just how it was created at the time.
I want to change this to a Central Distributor model when we do the move to speed things up and remove any extra potential points of failure.
The customer has a requirement that during this process, their subscription database remain online, all tables are fully populated and replication pick up exactly where it was left off. This is a completely new scenario for me and I was hoping to just be able to rebuild the subscription from scratch.
Is there a way to do this move and topology change and meet the customer's requirement?
View 2 Replies
ADVERTISEMENT
Apr 12, 2006
Folks,
Help me figure this out once and for all!
We have three geographically dispersed offices, each with their own local SQL Server. We are creating a DB application (SQL Server 2005) for our primary business (insurance) and expect to have one database with replicas on the other servers. (There is no "primary" or home server -- each of the local replicas/servers are peers.) Users in each office will update records in the local replica of the database for clients principally served out of that office. All data, however, must appear on all replicas for business intelligence purposes. In addition, there will be some (though limited) amount of record upating in one office for clients principally served out of another office. The nature of the business is such that it is unlikely users in two different offices would be working on the same record at any one time. It is not unreasonable, however, that during the course of the day, two users in different offices might edit the same record. Therefore, data inserted/updated in one server should be replicated to the other two servers within no more than an hour or so.
Given the above, how should I approach replication? The way I read the MS documentation, I should be looking at peer-to-peer merge replication. As an alternative, I might also use a peer-to-peer topology with a transactional replication. In the latter case, however, I should be looking at updating subscribers. Some here have suggested that neither approach is necessary and that I can use plain vanilla peer-to-peer replication with straight transactional updating (or I have completely misunderstood their posts). Because there is no central server, creating "non-peer" publishers on each of the local servers doesn't seem to be an option. As a final twist, so far as I have been able to figure out, MS SQL Server Studio 2005 will not allow me to create peer-to-peer replication with anything other than straight, non-updatable, updating -- With transactional plus updating subscribers the option to enable peer-to-peer is greyed out and the menu entry to configure peer-to-peer topology does not exist. With merge replication, the option does not even exist (on the GUI) and, again, the menu entry is missing.
Any thoughts, help, comments or criticisms on this subject would be most appreciated.
Thanks.
Randy
View 6 Replies
View Related
Mar 15, 2006
Hi,
I am given with the responsibility to set up database replication in my organisation.
Before starting the replication set up, I would like to know which is the best suited replication for my application.
My application is much process oriented, and the users are working on records parallely(but with different records).
Say for ex:
There is a module for Insurance claim processing, it is handled in the following manner:
There are many employees with CSR roles, and all are working on some set of records to be processed. But No two employees should get the same record for processing. For that, we are doing locking of the cases by having one column called 'LOCKED_STATUS' and setting value as LOCKED. So that other users wont get the same case. Once the case is processed, the record is marked as unlocked.
My analysis for replication is as follows:
1. Snapshot replication: This wont be a suitable type of replication as the updations are to be immediately updated on all the subscribers, because when the user executes select query to get the next unlocked case, No 2 user should be able to get the same case.
I think, the best replication to chose from is, Transactional replication with Immediate Updation/Queued updation. But the problem is, when one of the subscriber goes offline, the updations are queued up. But in my case, say for ex. One case is updated as locked, but the subscriber is offline and the updations are queued up. At the same time, the other subscriber can fire an select query to get the next unlocked case. Since the update is on queue, there are possibilies for this subscriber to get
the same case.
Can anyone let me know, are the select statements also queued up(i.e., along with update and insert statements.)? If not please let me know how to approach for this problem.
Thanks in advance.
Prashant N M
View 1 Replies
View Related
Nov 24, 2006
Hi there,
I have one publicacion on ms sql 2005 Enterprise w.sp1/MS Windows 2003 Enterprise w.sp2 and one subscriber on MS sql 2005 Enterprise w.sp1/MS Windows 2003 w.sp2, but I have also 3 subscriber on MS SQL 2000 w.sp3/MS Windows 2000 advanced server w.sp4. They replicate the same database with a publication compatible with 80RTM, All works fine with data replication, but I can't replicate DDL to MS sql 2005 suscriber, I don't care that don't replicate DDL, but I want manually alter triggers at MS sql 2005 suscriber, but I get this error at the subscriber on when I run the ALTER TRIGGER statement :
Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertrigger, Line 67
The DDL statement cannot be performed at the Subscriber or Republisher.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.
Msg 3609, Level 16, State 2, Procedure TU_Centros_Distribucion_Articulos, Line 58
The transaction ended in the trigger. The batch has been aborted.
Any help will be appreciated,
PD. I miss sql2000 replication, where I have full control over the database logic
View 14 Replies
View Related
Jan 17, 2002
Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
View 1 Replies
View Related
Apr 11, 2006
I'm beginning to wonder if replication is worth the trouble I've had to spend on it!
Trying to create a simple peer-to-peer replication topology. I can create the initial publication. I can change the property to allow P2P. I can backup the database and I can create a snapshot. When, however, I try to configure the peer-to-peer topology, I get a 14027 error message telling me that ONE table out of 250, or so, "does not exist in the current database." That's pretty troubling given that I can run a select query against that specific table. I would appreciate any information that would help me to understand what's going on or successfully set up replication.
Thanks!
Randy
View 4 Replies
View Related
Oct 26, 2005
This is very important.
View 1 Replies
View Related
Feb 28, 2002
Changing datatype in a table used for merge replication ?
I'm trying to change the data type on a column in a table that is used for a merge-replication with another (identical) DB.
I get this error:
Cannot alter the table '[Tablename]' because it is being published for replication.
I've tried to remove that specific table from the publication (Publication properties -> Articles tab) so I can change the data type and then put the table back into the publication, but I can't (probably because it's a MERGE rep.)
Any ideas ?
/CN
View 1 Replies
View Related
Apr 17, 2008
We are currently using continuous transactional replication from an OLTP type database to a Reporting database. We recently changed the 'sa' password for the first time since replication was introduced and now the Replication Log Reader job fails to start.
This is the error from the Job Log:
Message
2008-04-14 15:18:24.894 Copyright (c) 2005 Microsoft Corporation
2008-04-14 15:18:24.894 Microsoft SQL Server Replication Agent: logread
2008-04-14 15:18:24.894
2008-04-14 15:18:24.894 The timestamps prepended to the output lines are expressed in terms of UTC time.
2008-04-14 15:18:24.894 User-specified agent parameter values:
-Publisher OASV-SQL1
-PublisherDB georgiaoas
-Distributor OASV-SQL1
-DistributorSecurityMode 1
-Continuous
-XJOBID 0xCE146F975BC43043AB1ECBF42F801C43
-XJOBNAME OASV-SQL1-georgiaoas-1
-XSTEPID 2
-XSUBSYSTEM LogReader
-XSERVER OASV-SQL1
-XCMDLINE 0
-XCancelEventHandle 00000730
-XParentProcessHandle 0000075C
2008-04-14 15:18:25.034 Status: 0, code: 20015, text: 'Login failed for user 'sa'.'.
2008-04-14 15:18:25.034 Login failed for user 'sa'.
When we change the 'sa' password back to the previous value, we can start the Log Reader without issue.
We have tried restarting the SQL Server Agent process without success. We have not restarted the SQL Server process due to the server hosting production databases.
Any ideas?
View 1 Replies
View Related
Dec 5, 2005
After i changed my computer's name(eg. the original computer name is 'SERVER-1',I has changed it to 'SERVER-2'),I can not create a new replication in SQL Server 2005.The error message :
View 5 Replies
View Related
Jul 22, 2015
We have inherited a remote distribution db where the password has been lost. I would like to confirm that if you change the password in the distributor properties-->publishers--> administrative link password that the password will be changed on all publishers without having to resnap the publications?
View 3 Replies
View Related
Nov 6, 2006
Hi
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
View 3 Replies
View Related
Sep 22, 2015
I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.
I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?
My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?
View 1 Replies
View Related
Mar 22, 2007
Where is Configure Peer-to-Peer Topology option, in transactional replication?
I have created a publication using,
http://msdn2.microsoft.com/en-us/library/ms152536.aspx#
but not able to find Peer-to-Peer Topology option...
Can any one guide here
I have refreshed all nodes in ssms ;-)
------------------------
I think, therefore I am - Rene Descartes
View 1 Replies
View Related
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
Sep 13, 2007
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 Related
Aug 22, 2007
Hello,
I have this problem on a Production database.
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
Thank you.
View 4 Replies
View Related
Mar 6, 2007
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Jul 28, 2006
Hi,
I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows
- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem
- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine
however.......
- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error
- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.
I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.
I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc
Any advice appreciated !
Thanks
Ronan
View 2 Replies
View Related
Jun 28, 2007
Hi all,
I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????
Thanks in advance
Jacx
View 3 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Sep 2, 2015
I 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.
View 6 Replies
View Related
Apr 17, 2007
Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies
View Related
Oct 9, 2007
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...
View 5 Replies
View Related
Nov 9, 2006
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
View 2 Replies
View Related
Dec 14, 2000
A college forgot to change the data root when installing a developer server. My question is can this be changed whitout reinstalling the server and restoring the developer databases.
I found sqldataroot under [HKEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServerSetu pSQLDataRoot]
If I stop the sql server and change this value and move the *.mdb and *.ldf files. Would I risk currupting the databases and do I need to change something else? For example would changeing this value change the default path for backupdevices?
View 2 Replies
View Related
Dec 5, 2000
I have a problem where our developers would like to change the passwords of the SQL Logins without knowing the users old password.
The only way they can use the below syntax is that they must be a member of the SYSAdmin Server Role.
sp_password NULL, 'newpassword',Max
I need a way to allow them to change the passwords without making a user a member of the SYSAdmin Server Role.
Any thoughts would be greatly apprecitated. Thanks in advance.
Max
View 1 Replies
View Related
Dec 20, 2000
I need to change my security settings from "Windows NT Only" to "SQL Server and Windows NT". Does anyone know of any issues with doing this? Does this subtract anything or does it only add access??
View 1 Replies
View Related
Jun 5, 2002
Is there a way to change the license key after installation of SQL7.0?
View 1 Replies
View Related
Nov 19, 2002
hi, I have created a database in sql server 2000. I need to duplicate the database under different name, how can I do that?
Also, can I rename a database name?
thanks
Ali
View 3 Replies
View Related
Dec 7, 1999
Hi all,
I need to change the NT Server computer name (and the SQL Server name). I know how to do it in NT, but how would I go about changing the SQL Server name. Can anyone help me with this? Is this an easy process?
Thanks,
LC
View 3 Replies
View Related
Mar 1, 2005
Someone is changing permissions on our production sql databases frequently which is causing big problems..any idea how to know who is changing and when? Any code that can be written to capture such activity?
thanks,
dbfan.
View 6 Replies
View Related
