Replication Architecture Question - Pull Subscriptions On A SQL Cluster...
Aug 30, 2007
Greetings:
I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.
I have the following situation. I have a single publication on my publisher Server. This publication is created using SQL Server Manager. Snapshot is created completly. Now I want several Pull subscriptions from several machines to work with this publication (One subscription per machine). I'm creating these subscription using RMO. I'm synchronizing data using RMO again. My code workflow is: Syncronize -> success -> do nothing -> fail -> Check if everything with Publisher and Publication is ok -> Generate Snapshot -> Create Subsscription -> Sync again. (I tried to eliminate Generate Snapshot step but couldn't because I receive error that I must rerun Snapshot Generation.)
When I tested my code per single machine it's working. Next test I tried was to run my program on 2 machines simultaneously.
The result is:
---> System.Data.SqlClient.SqlException: Another snapshot agent for the subscription(s) is running or the server is working on a previous request by the same agent.
or:
---> System.Data.SqlClient.SqlException: Unable to acquire the replication merge administrative application lock for database 'XXX'. This could be due an active snapshot running while the schema change (DDL) or the administrative proc change was attempted. Replication merge admin stored procedure 'sp_changemergepublication' failed for publication 'YYY'. This could be due an active snapshot running while the admin proc was called.
The problem is obvious but because I'm new to the replication I'm not sure if I did general mistake in what I want to achieve. Any advices how can I fix my problem will be highly appreciated.
I have set up a pull subscription and nothing seems to come through although everything seems set up correctly. Push subscriptions are working just fine. Any thoughts why push subscriptions work and pull subscriptions do not?
I've successfully carried out pull subscription using Wizard in SQL server 2000.
But NOW its the time to deploy the project & I've to do it programatically........
i tried to use the default stored procedures like "sp_addmergepullsubscription ", "sp_addmergepullsubscription_agent ", "sp_reinitmergepullsubscription ", with respective parameters.
Then using replmerge.exe i tried to execute the batch file But could not succeed......
Can anyone guide me from the Basics ??? (i knw the Replication Architecture its heirarchy....but have NO idea of performing it Programatically......!!!!!!!!)
by BASICS, i mean......how should i execute the resp. stored procedures with what parametes & the order i should follow in order to successfully pull a merge subscription in SQL server 2000.
if a Sample code is mentioned as example, would be better for me to understand....
Hi All... We're developing an asp.net based project. We plan to deploy it across multiple servers running in an NLB environment. That is NLB via hardware or software - we generally leave that decision up to our customer. Different from prior projects we've done, this application will rely on a SQL Server 2005 database. With the NLB, we essentially install our application many times across multiple servers. As hits come in from clients, they'll get directed to one server or another by whatever NLB technique is being used. The applicaiton generally doesnt care which server is hit.
But what about the database? What's a typical or best architecture to employ? Should each server have an instance of SQL Server and then somehow through replication they all keep each other updated? Or should each of the servers hit one lonely instance of SQL Server and it somehow keeps some other backup instance updated? I think the first approach seems to make more sense from a load balancing point of view, but depending on how many servers there are, it could get quite complicated.
Again, we're in the early stages so we really havent done much research on this yet. Any tips would be appreciated. Any good white papers out there?
Hi everybody, I'm quite new to SQL 2005 and I€™m trying to understand some key concepts regarding replicas. I need to develop an application with characteristics similar to the Sales Order Sample for Merge Replication, on the client side it should run with the express version of sql server and also the synchronization should only work via web. I try to run the sample but I got an exception in the CreateSubscription method on invoking publisherConn.Connect();
I have been trying to set up replication with updateable subscriptions. I first followed the tutorial on MSDN and set up basic transactional replication. This all worked fine. I then tried, using that tutorial as a basis, to set up replication with updatable subscriptions. On the Agent Security page you are asked for a Snapshot Agent, a Log Reader Agent and a Queue Reader Agent. I assigned these to the following accounts, which I created and added as logins, PUBLISHERSERVER epl_snapshot, PUBLISHERSERVER epl_logreader and PUBLISHERSERVER epl_queuereader.
I then tried to set up a subscriber on SUBSCRIBERSERVER. Under the publication I add new subscriber, select the publisher, add SUBSCRIBERSERVER as the new SQL server Subscriber. In the Distribution Agent Security page of the wizard it asks for process account, which will be run when synchronizing the subscription. I entered PUBLISHERSERVER epl_distribution here and selected the other two default options of connecting to the distributor and subscriber by impersonating the process account. I then took the default options for the next few screens and finally get to:
Login for updateable subscriptions. This offers the option of a login or using a linked server. I have tried various logins here, initially trying the €˜sa€™ login and password. This produced the error: The user is not associated with a trusted SQL Server connection. The servers are set up for mixed mode operation. I then tried PUBLISHERSERVER epl_distribution and subsequently every other account I had created on PUBLISHERSERVER. All of these failed. I tried linking the servers, but this also failed. How exactly do you set up subscriber with the ability to login to the publisher? I have spent days trying to set this up, and am as you can gather new to this technology, any help would be greatly appreciated.
I have recently deployed a sql replication system into a production environment and I€™m experiencing a few problems.
I have a server holding consolidated data (4 processor/2GB ram) and fifteen subscribers, each being a 400Mhz Celeron with 256MB ram.
Transactional replication is used to pull base data from the main server to the subscribers. This data does not change very often at the publisher, but the subscribers need an immediate update, so continuous transactional replication is configured at the subscribers. This data will never be changed at the subscriber.
The server has another three tables used for historical data. Each subscriber has been configured for merge replication on these three articles. An initial snapshot is run to setup each subscriber. Each subscriber writes up to 10 records per minute into these local tables and continuous merge replication replicates these 10 records from the subscriber to the publisher. As all subscribers are doing the same, part of the merge operation will potentially bring down 140 records to each subscriber (10 records from each subscriber merged to the publisher and back to each subscriber).
Anyway on to the problem€¦
Each subscriber system has a Microsoft.Net application writing these 10 records per minute to the local database. Refreshing a graph within the application occurs once every minute. Refreshing, queries the three local tables used for the merge replication. This application shows an error (randomly) with a sql timeout message trying to get a connection to the local database.
If I have one table in one publication in transactional replication replication between primary and replicate is broken because subscription is marked as inactive.
If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken
If I do
Reinitialize subscriptions start the snapshot agent start the distribution agent
Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.
I seem to be missing something. I'm trying to pull a subscription from SQL Server 2000. (To a desktop SQL 2000 install <msde i guess>) The general error I keep running into is When creating the subscription...
"Error 15004: Name cannot be NULL"
I cannot figure out where this problem originates. I'm running the SQL Service account and Executive under a domain account. (same one on both machines)
Does anyone know where I'm missing the Permissions for the subscription?
Please impart any Replication suggestions you have. Don't hold back....
So, Microsoft decided that they were deprecating Transactional Replication with Updatable subscriptions. In that case, you have 2 options (if I am correct): Pay for Enterprise (if you are already not) and use peer-to-peer or use bidirectional transactional replication which is basically setting up a transactional from db1 to db2 and also transactional from db2 to db1.
The issue I see in both cases is conflict resolution. With updatable subscriptions, you could specify how to handle the conflict. With either of these 2 options (from what I can tell) you cannot allow the engine to handle this for you.
Any thoughts? Seems like a slap in the face to those who have been using MS for years and a damn good reason for companies that rely on updatable subscriptions to not upgrade to 2012.
I am executing sp_dropmergesubscription, but the rows are still in dbo.msmerge_subscriptions, and are still shown in the replication monitor as expired; the last synch dates were in april (expiration is set to 10 days). The 'expired subscription clean up' job appears to be running okay. I need to remove these subscriptions from the publisher as the subscribers are mobile devices, which sometimes are lost.
hi, I want to setup transactional replication(PULL) between 2 servers . can anyone guide me with the steps involved while performaing a Pull replication from server1 to server2. Any help appreciated.
I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?
For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?
Replication Type: Transactional With Updateable Subscriptions SQL Server Version: SQL Server 2005 Enterprise Edition Publisher, Subscriber1 and Subscriber2 all on same SQL Server
Problem: I am trying to set up a transactional replication with two updateable subscriptions. All three databases are a carbon copy of each other. Every table to be replicated contains an identity key column. I've been asked to create the replication without assigning separate identity ranges for the publisher and the updateable subscriptions. In other words they want a continous ID range. Can this be done without managing identity ranges programatically outside of SQL Server itself?
Example:
Publisher inserts into table test1 in database1. This creates ID 100 for the record. ID 100 replicates to Subscriber1 (database2) and Subscriber2 (database3). Subscriber1 and 2 identity seeds are then incremented to ID 100. So far, all three databases have the same identity seed in table test1. Subscriber1 inserts into table test1 in database2. This creates ID 101 for the record. ID 101 is replicated to Subscriber2 (database3) and to the publisher (database1). Subscriber2 identity seed is then incremented to ID 101. However, the Publisher retains it's original identity seed of 100. Insert into Publisher table test1 will fail since it will try to insert the identity seed of 101 which is taken.
I have tried identity range management Automatic and Manual. Both seem to have some limitations in managing identity seeds unless different identity ranges are assigned for publisher and subscriber.
Can someone recommend a solution to this problem? Can this be done natively in SQL Server 2005 replication. Or can some recommend another approach to make this work?
I set up a sql server 2005 database as publisher for about 50 sql mobile subscriptions. Yet in the replication monitor windows, it shows in subscription columns is 26549. Does it mean I have 26549 subsriptions or it means 26549 synchronizations?
I have setup a pull subscription on the subscriber for transactional replication. The distribution job continues to run, but doesn't do anything because it says: The initial snapshot for publication 'man4' is not yet available.
How can I tell if the snapshot is running, and can I see the snapshot where it was created?
Question of such plan, to documentation to Microsoft SQL Server 2005 Express Edition it is written, that the given product supports only PUSH replication, however in his{its} structure there is an agent replmerg.exe which description says:
replication merge. Synchronization means, that the agent of merge transfers changes from the subscriber to the publisher, and then transfers changes from the publisher on the subscriber €¦
And so the question if this agent is present at this edition, whether that is possible to adjust manually his{its} call, or through Windows the agent, for realization PULL replication or it is impossible and for what reasons?
If I want to access or modify my local subscription data(not the configuration) how can I do that(from sql server 2005 or from asp.net)? Also can i update directly to the local subscriptions data or do I need another layer which will update from a table for example??
I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?
Our current application which uses SQL 2000/CE uses a single Merge publication to populate numerous "read only" tables on the mobile device, and create the schema for a "transactions" table, which will be filled by the mobile device during work, then merged at sync.
Now, the master "transactions" table at the publisher has millions of rows, and the mobile device does not need any of them... so currently we have it "filtering" by a always false clause, so that none of the data is pushed to the client.
The problem with this is that we cannot archive any of the records in the transaction table, as doing so (say, removing 200k rows) would result in the mobile devices (of which there are about 15,000 active in the field) to attempt to reconcile each of those 200k rows when it is next synced, which takes way too long.
Now, what we would like to do is upgrade to SQL 2005/CE 2005, if the new features will allow us a PULL ONLY table in some way? I cannot find how to do this via the wizard, but am sure I am missing something. At least I hope so.
SELECT P.Publication ,P.Publication_type ,S.Subscriber_ID ,S.Update_Mode FROM MSPublications P INNER JOIN MSSubscriptions S ON P.Publication_ID = S.Publication_ID
give me publication_type=0. So it is transactional replication but how do we know that is pull or push?
We have setup transactional replication between 2 databases on SQL Server 2000 SP3a (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.
From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring
Initialising Applied script 'snapshot.pre' Then it applies all the schema files .sch Then it applies all the index files .idx The it bulk copies the data in (bcp) Then it creates the Primary Keys Then it applies all the trigger files .trg Then it applies all the referential integrity files .dri
These all complete successfully but then the process kicks off again immediately after reapplying the snapshot. We are unaware of any settings that may be causing this.
Any help on what maybe causing this would be much appreciated.
I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?
It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
I am running a SQL Server 2000 cluster (active to active). I trying to replicate (Transaction) a portion of my tables from instance1 to instance2 but neither the initial SnapShot nor the Log Reader can connect to instance1 which is where the ditributor is located. I have successfully ran replication in the past but not on a clustered server. Can anyone help me out with this?
1) I have the Active/Passive cluster environment (using Microsoft cluster service). 2) SQL2000 is installed on the cluster (Virtual instance). 3) Virtual instance of SQL Server is used as Publisher in Transactional replication with updatable subscribers. 4) Subscribers are passing updates to subscriber using MSMQ 5) The problem is : after I establish replication , the Queue Agent fails with following error (“Failed to initiate search for MSMQs”)
Hi All,I would like to set up transactional replication with failover cluster.Did anybody tried this? Does it work together? Is there anything Ishould be coutios when I set up these together? or Is there anyglitches?If somebody helps me I will really appreciate itThanks
I am trying to set up replication (transactional with updates) from a 2-node Active/Passive SQL Server 2005 Cluster to a SQL Server 2005 server in a Workgroup. When configuring the Snapshot Agent, Log Reader Agent and Queue Reader Agent from the Publisher (Cluster) we are advised not to use the SQL Server Agent Service Account for security reasons. When I specify a Domain Account I get an "unknown security error" staring the snapshot agent. When I select the option to run under the service account all works well. Strange thing is when I specify the service account I still get the "unknown security error" message. Anyone got any ideas?
