Replication - Publications
Dec 8, 1999
I'm trying to set up replication between two servers that both used to have SQL Server 6.5 on them. I was previously getting a 'The ID '114' was not found in the tasks collection' message and so decided to be on the safe side and remove and re-install SQL Server on both machines. Having successfully re-installed SQL Server 6.5 on both machines I then thought I'd try and set-up replication again. BUT as soon as try to install the publishing side of it the only option I have is to uninstall publishing ???? SQL Server on both machines appeared to uninstall and install nicely so how come it's still asking if I want to uninstall publishing ????
Any ideas would make my Xmas.
thanks Paul
View 1 Replies
ADVERTISEMENT
Jun 26, 2006
Hi, I right click on the publications and hit 'properties' but can't find where the frequency of them is.
Thanks,
-Trevor
View 1 Replies
View Related
Jun 12, 2006
Hi.
I have a database with two publications.
One publication is used to replicate data among 4 SQL Servers, while the other is used for the replication with SQL Server CE clients.
The problem here is that the SQLCE receive a number of changes that it is not correct.
Before establishing the publication for SQL Servers the SQL CE clients were working great, but now i have this problem.
Is there any patch or fix I should install on the server?, or do you know how can I solve this?
Thank you.
Omar Rojas
View 6 Replies
View Related
Jan 31, 2007
Hi,
We are using transactional replication with updatable subscriptions in SQL Server 2005. In our case both the publisher and the distributor is the same server. The subscription type is the Pull Subscription. It is set to run in the continuous running mode.
Can we have more than one publication in the same Distributor?
Please refer the link below :
http://msdn2.microsoft.com/en-us/library/ms152471.aspx
This link has a recommendation of the following under the heading "Queued Updating Subscriptions"
"There is only one Queue Reader Agent for each Distributor. It is recommended that for each Distributor, you configure at most one publication that is enabled for queued updating subscriptions."
In our case we need to have 6 publications with 4 subscriptions each (totally 24 Subscriptions).
Pls provide a reply for this asap.
Thanks and Regards,
Swapna.B.
View 1 Replies
View Related
Jan 8, 2007
Hi:
Did you have any experience that all publications and their related jobs suddenly disappear?
I had a transactional pull replication between two servers, and distributor and subscriber in the same box; publisher was an AA cluster server.
The schedule of pulling was once a week.
Last Friday morning everything was working perfectly, but I found all publications and their related jobs disappear around 5pm
I checked with the sysadmin and he said nothing was changed on that period.
Did you have any idea about it?
Did you have any idea that I can prevent this happen again or any alert I can set up to monitor who drop it, when it was dropped and so on?
It is a SQL 2000 box and system table doest not take trigger.
Thanks in advance
Theresa
View 2 Replies
View Related
Nov 16, 2015
We have many transactional publications, and would like to have identical settings on each of them. Is any way to compare settings of these publications using script?
View 3 Replies
View Related
Aug 31, 2006
I am working on a replication design and getting closer to implementation. One of my major concerns is maintaining and updating stored procs and/or user defined functions.
The current design is a single publication, including tables, procs and functions (no views yet). All told there are about 686 articles in the publication. The tables are horizontally partitioned using dynamic filters based on the hostname of the subscriber. There are around 50 subscribers. Most will have small databases (< 250 MB). A few (2-3) will have much larger databases. I am mostly concerned that whenever I have to update a proc or function, I have to re-initialize the subscriptions and that pushes a TON of data out over the network and may interrupt service at the subscriber locations.
I see three options for the procs and functions:
1. Include them in the publication with the data tables
2. Place them in a separate (snapshot only) publication
3. Exclude them entirely from replication and maintain them manually
I am starting to lean towards option #2; but I am a bit concerned about maintaining a duplicate set of replication agents for each subscriber.
Any thoughts and/or comments?
Regards,
hmscott
Am I overlooking something? Is there an option that I have not considered?
View 5 Replies
View Related
Jul 20, 2005
Greetings All, I was hoping that a replication sage might be able toanswer a question for me.I want to have one subscriber subscribing to N publishers. Iessentially have a company that has a main headquarters and threesatellite offices. I want each of the satellite offices to push theirdata up to the master database. From what I have read it seems likethis should not be a problem. Some questions that come to mind are:1.)Does the master need to be read only or can it be configured to beupdateable as well?2.)Can the distribution agent on all the publishers be set tocontinuously distribute or should it be staggered so as not to cause aproblem when another distribution agent is running?3.)If the distribution is set to "delay distribution" will this causechanges on the subscriber to be pushed out to the publishers?In this database guid's are used as pk's so the issue of pk collisionsis not a problem.I hope that this question is not too vague. My experience thus farwith replication has been simple one way transactional and simplemerge replication.Regards, Louis Frolio
View 3 Replies
View Related
May 10, 2015
We have setup Transactional Replication that was originally initialized from a backup. Our subscriber recently had some hardware failures causing the publications to fail. We now have the server back up however the transactions for a few days have been missed, and data is now out of sync.
How do I go about resyncing that missing data? We have too many table to manually resync the data, and I cannot restore the database directly from a backup due to some permission issues and differences from the publisher. I've attempted to generate a new snapshot
However I get a message "[0%] A snapshot was not generated because no subscriptions need initialization." Â My current understanding that is that I should be able to take a snapshot from the publisher and apply it to the subscriber, thus syncing all the data.
View 8 Replies
View Related
Apr 20, 2007
Hi,
i'm doing POS system by using C# in visual studio 2005. Recently, i installed the microsoft sql server management studio on my pc, i plan to do merge replication through local publications between my pocket pc and my pc. So, i opened the replication file which is under Object Explorer in microsoft sql server management studio, it ONLY show me the local subscription folder under the replication file.Do i install incomplete set of microsoft sql server management studio?Local publications and local subscription, which one is more suitable for POS system?
View 11 Replies
View Related
Sep 2, 2015
We need to replicate multiple databases (publications) to one central subscriber. The schema of those articles are identical in all publications and also the primary keys in publications do not have any overlap.
Is this possible?If yes is there any specific thing that I should consider for it's implementation? Should each publication has it's own dedicated distributor or all of them can share one distributor?
View 2 Replies
View Related
Jan 30, 2007
I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
Everyone known how to fix this problem?
Thanks,
Frank
View 1 Replies
View Related
May 5, 2007
Hi
My first attempt at RMO is to create a job that will generate the SQL scripts necessary to recreate publications on a warm stanby server.
I thought I'd got a grip on the RMO object model, but whenever I call the script method, an empty string is generated.
Can anyone see what I'm doing wrong?
' Create connection to local server
serverConnection = New ServerConnection(".")
Try
' connect to publisher
serverConnection.Connect()
replicationServer = New ReplicationServer(serverConnection)
replicationDatabases = replicationServer.ReplicationDatabases
For Each replicationDatabase In replicationDatabases
If replicationDatabase.HasPublications Then
replicationDatabase.LoadProperties()
Console.WriteLine(replicationDatabase.Script(ScriptOptions.Creation))
End If
Next
Catch ex As Exception
Finally
serverConnection.Disconnect()
End Try
View 3 Replies
View Related
Apr 20, 2006
hi all,
our datbase was restore but the publications were all gone.
however replication monitor shows publication and subscription.
worst. can create the publication with the same name because
it raises an error saying that it can't drop publication becuase
there is a subscription in it.
how can i remove the publication when the are already gone in the publication
folder but exist in the replication monitor
thanks for the help
View 4 Replies
View Related
Mar 30, 2007
Hi
I created a DB named 'TestDB' and created a table called Users. This user table having 3 columns (Uname and pwd,version).
I need to create two publication for this user table.
1) Create a publication using all columns.
2) create a publication using Uname and pwd (not version column).
I am using Merge publication.
When I create first publication (any one - all 3 columns or any 2 coulmns) it create successfully.
When I create second publication it throws error. The details are below.
TITLE: New Publication Wizard
------------------------------
SQL Server Management Studio could not create article 'Users'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An article with a different subscriber_upload_options value already exists for object '[dbo].[Users]'.
Changed database context to 'TestDB'. (Microsoft SQL Server, Error: 20053)
How can i create the second publication? Is it possible? If yes, please give me the steps.
Thanks
View 3 Replies
View Related
Sep 6, 2006
Dear All,
Greetings!!
I have installed SQL server 2005 Express and SQL server Mobile, and I was reading the Books online section to find out how to maintain the two databases consistancy.
Found : replication Option.
Problem : It only shows Local Subscriptions in SQL Server and In SQL Server Mobile only Subscription. I dont get a Publication Option.
Please correct if Wrong : SQL Serv Mobile is the one should Publish and SQL Serv hould Subscribe ya?
Please help me to sort this out.
View 1 Replies
View Related
Apr 18, 2007
The setup is 3 sql 2000 servers, one with a number of publications, and two with transactional pull subscriptions.
The publications were accidentally deleted from the primary server when replication was removed from an older server that unfortunately had been cnamed to the primary server.
The subscriber servers now error "the subscription does not exist".
I had thought restoring the master and msdb on the publisher would bring the publications back but this has not happened. There are good backups of all the system and user databases on the publisher available, but not the scripts that created the publications for replication. Can anyone suggest how or what to restore to get the publications back on the primary server so we can restart replication?
Thank You!
[edit]
It also may be worth noting that the publisher is it's own distributor in this scenario.
View 5 Replies
View Related
Nov 27, 2006
I have two publications on a SQL Server 2000 database.
I am able to create two subscriptions from another SQL Server 2000 database
and synchronize both in succession.
However when I try to repeat this going from SQL Server 2000 to SQLCE 2.0
it fails. The first goes OK. The second fails.
I get error 80004005, 28521 (The SQL Server CE database is already enabled for publication.
Is it possible to do what I am trying to do on CE?
The reason I have 2 publications is because the first is non-filtered and goes very fast via bcp files when reinitialized.
The second is dynamically filtered and not as fast. Breaking them up makes reinits go much faster.
My code is included below.
Thanks
Ed Santosusso
esantosusso@omicron.com
--------------------------------------------------------
// Create and initialize the Replication object
hr = CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER, IID_ISSCEMerge, (LPVOID *)&CEMerge);
CEMerge->put_InternetURL(L"http://myiisserver/sqlce20/sscesa20.dll");
CEMerge->put_InternetLogin(L"<userid>");
CEMerge->put_InternetPassword(L"<password>");
CEMerge->put_Publisher(L"mysqlserver");
CEMerge->put_PublisherDatabase(L"MyDataBase");
CEMerge->put_Publication(L"Publication1");
CEMerge->put_PublisherSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge->put_PublisherLogin(L"sa");
CEMerge->put_PublisherPassword(L"mypassword");
CEMerge->put_PublisherNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge->put_Subscriber(L"TestReplAndMerge");
CEMerge->put_SubscriberConnectionString
(L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\mydatabase.sdf");
CEMerge->put_Distributor(L"mysqlserver");
CEMerge->put_DistributorNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge->put_DistributorSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge->put_DistributorLogin(L"sa");
CEMerge->put_DistributorPassword(L"mypassword");
CEMerge->put_HostName(L"some host name");
CEMerge->put_ExchangeType((REPL_EXCHANGE_TYPE)BIDIRECTIONAL);
CEMerge->put_Validate(NO_VALIDATION);
bool NewFile = false;
// see if we need a file
if (FileExists(L"\mydatabase.sdf") == false)
{
hr = CEMerge->AddSubscription(CREATE_DATABASE);
if (!SUCCEEDED(hr))
{
ShowMergeErrors(L"Add Subscription failed",CEMerge);
return 0;
}
NewFile = true;
}
hr = CEMerge->Initialize();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Static Merge Initialize failed",CEMerge);
return 0;
}
hr = CEMerge->Run();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Static Merge Run failed",CEMerge);
return 0;
}
// Destroy the Replication object
if (CEMerge)
CEMerge->Release();
// set up for Second Merge
// Create and initialize the Replication object
hr = CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER, IID_ISSCEMerge, (LPVOID *)&CEMerge2);
CEMerge2->put_InternetURL(L"http://myiisserver/sqlce20/sscesa20.dll");
CEMerge2->put_InternetLogin(L"<userid>");
CEMerge2->put_InternetPassword(L"<password>");
CEMerge2->put_Publisher(L"mysqlserver");
CEMerge2->put_PublisherDatabase(L"MyDataBase");
CEMerge2->put_Publication(L"Publication2");
CEMerge2->put_PublisherSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge2->put_PublisherLogin(L"sa");
CEMerge2->put_PublisherPassword(L"mypassword");
CEMerge2->put_PublisherNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge2->put_Subscriber(L"TestReplAndMerge");
CEMerge2->put_SubscriberConnectionString
(L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\mydatabase.sdf");
CEMerge2->put_Distributor(L"mysqlserver");
CEMerge2->put_DistributorNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge2->put_DistributorSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge2->put_DistributorLogin(L"sa");
CEMerge2->put_DistributorPassword(L"mypassword");
CEMerge2->put_HostName(L"some host name");
CEMerge2->put_ExchangeType((REPL_EXCHANGE_TYPE)BIDIRECTIONAL);
CEMerge2->put_Validate(NO_VALIDATION);
if (NewFile)
{
hr = CEMerge2->AddSubscription(EXISTING_DATABASE);
if (!SUCCEEDED(hr))
{
ShowMergeErrors(L"Add Subscription failed",CEMerge2);
return 0;
}
}
hr = CEMerge2->Initialize();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Tech Merge Initialize failed",CEMerge2);
return 0;
}
hr = CEMerge2->Run();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Tech Merge Run failed",CEMerge2);
return 0;
}
// Destroy the Replication object
if (CEMerge2)
CEMerge2->Release();
View 1 Replies
View Related
Mar 11, 2005
Hi All
How can I checked the publication are subscribed to all subscribers?
is there any stored procedure or method to do this?
Thanks alot
Tolga
View 3 Replies
View Related
Dec 8, 2006
In 2005 transactional replication, The following procedure worked (without dropping the subscription) when I dropped an article from a replicated database:
Drop article: On Publication Properties, uncheck the article (table, stored procedure or function).
Create a new snapshot.
Synchronize the push subscription.
DROP the article on the Publication and Subscriber databases.
Replication still works!
However, the following article says the subscription needs to be dropped and re-created when an article is dropped from publication: http://msdn2.microsoft.com/en-us/library/ms152493.aspx (Adding Articles to and Dropping Articles from Existing Publications ). For transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.
Under what conditions is dropping the subscription and recreating it absolutely necessary? I do not want to include this extra step.
Linda
View 1 Replies
View Related
Nov 21, 2006
Hi,
i have the SQL Server 2005 Express installed but i want to create publications so i´m going to install the Developer Edition.
Shoud i uninstall SQL Server Express before?
IN DE installation i have the following options:
sql server database servicesanalysis servicesreporting servicesnotification servicesintegration servicesworkstations components, books online and development toolsWhat is the minimum i have to install to create publications?
Thanks
SP
View 2 Replies
View Related
Oct 17, 2007
Hi,
I am trying to replicate some tables from an Sql Server database to an Oracle database. So the publisher is SQL Server and the Subscriber is Oracle.
Unfortunately I realised that I can't set the replicate_ddl parameter to 1 for an Oracle Subscriber. This is the error I got:
"The property "replicate_ddl" cannot be modified for publications that are enabled for non-SQL Server subscriptions."
I looked on the Internet and I found that this parameter "@replicate_ddl" (used in the add_publication stored procedure) can't be set to 1 if the @enabled_for_het_sub parameter is set to 'true'.
So I thought if I just set the replicate_ddl parameter to 1 and leave the other parameter to false and then use the sp_changepublisher stored procedure to set the enabled_for_het_sub parameter to true that will work. But it didn't. I tried this in Enterprise Manager and although I had no error I realised that the replicate_ddl parameter was reset to false.
Is there any way I can replicate the ddl statements in Oracle automatically or I should to them manually?
View 4 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
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
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
