Replication Best Practice Question: Split Publications
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?
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 ????
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?
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?
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).
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.
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?
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
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.
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?
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?
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.
For Each replicationDatabase In replicationDatabases If replicationDatabase.HasPublications Then replicationDatabase.LoadProperties() Console.WriteLine(replicationDatabase.Script(ScriptOptions.Creation)) End If
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.
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?
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.
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.
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
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?
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?
When setting up databases for end users, what's the best practice regarding who's the dbo for each individual database - the user itself or a sysadmin?
Does it really have any importance at all who the owner (as defined by 'dbo') is ?
1.- a list of all the terms that start with A% 2.- a list of all the related terms … that belong to terms that start with A%
For number 1 - I am doing a select on Terms table with where term like A%.
For number 2 – I am joining both tables and then once again doing a where term like A%.
Would it be more efficient to take the first results and put them in a table variable, and then just do a join with the second table RelatedTerms.TermID = Terms .TermID
The number of records that generally comeback are between 500 to 1000 records that
What would you consider is a better approach ? or maybe there is an even better way ?
i'm a newbie for sql , but i want to learn sql on my own , is there any way that i can learn sql , do i have to download sample database from the internet, do i need to have my own server to play with. Hopefully someone show some lights on this.
Please point me to a web resource from where I can study:1) writing complex queries such as those involving HAVING, mult-levelnested queries, GROUP BY, T-SQL functions2) Joins - a lot of practice3) Stored Procedures, transactions, cursors and triggers - I need someheavy-duty practiceWhere can I get some good practice of the above? Also, please recommenda good SQL Server/T-SQL book in the light of the above requirement.
Folks - had a look around Google and no surprises, but never found what i was looking for.
I want to see a real work best practice C# Stored Procedure for Sql 2005 (express is what i am using, but don't mind the Sql edition).
Almost everything i see is a "select * from table" which to be honest was my first stored proc many years ago - everything since has been fairly detailed.
I ask as i am sceptical, after years of trying to STOP building Sql queries in code (as it's hellish!) that the CLR technique really makes any kind of a diffence.
If someone has found that it HAS i'd love to hear about it. The thought of:
SqlCommand cmd = new SqlCommand ( "My Whole Stored Proc as Text" );
... doesn't appeal, never mind the potential for debugging syntactical issues and so on.
I was excited by this, until it became something i had to do in a real situation and then i got a little worried. Should i be?
create a table and name it Salary Information. Add an Employee Name and Salary column to the table. Create a column in the Employee table and name it Salary. Create a trigger that updates the Salary table with the employees's name and salary each time u insert data into the Salary column of the Employee table.
Hi, my database is growing over 1Gb, and I only have one .mdf to keep them all. Should I use a secondary data file for my data? Can I do that now? Thanks.
Good Morning, I work for a company that has sees alot of people come and go. The one thing I have noticed is that people use their admin accounts to log into SQL and create sp, views and databases.When the user leaves I am stuck with all these objects that are owned by somone no longer working for the company. So my question to you guys is: What is the best practice to use in creating new objects? Thanks for your guru-ness!
