June 5, 2012 at 6:08 pm
Hello,
I am trying to find a way to synchronize two SQL servers which have the following attributes:
1. Every day a new database is created where the name of the db has the date embedded (for instance: db_20120506). The database has two tables with the same name and structure every time.
2. This database is only created on the primary server.
3. This database is updated every two seconds (analog value and a time primarily)
4. I'd like to create the same database with the same attributes and data on the secondary server.
5. After a configurable amount of days, the database is deleted.
6. For all intensive purposes, the process of creating, deleting, and populating the databases can only be exercised on the primary and not the secondary SQL server.
7. I am using SQL Server 2008 Standard Edition but am open to other options.
Any thoughts on how to accomplish something like this?
Thanks in advance,
Derek
June 5, 2012 at 11:31 pm
If the Database at the Primary Server gets updated every 2 seconds and you want the Db at the Secondary Server also to get updated the may be you should look at something like Transactional Replication
June 5, 2012 at 11:56 pm
I'm not entirely clear on some of the details. Is it an existing process which creates the daily database on the primary server? Or are you also asking how to accomplish that part as well?
But I will assume it's an external process or SQL Agent job that is creating a new database each day.
One possible approach:
· On the primary server, create a database for the purpose of sending messages.
· On the secondary server, create a database for the purpose of receiving messages
· Use service broker to send messages between the two servers
· The messages can be XML representing different courses of action or the messages can be SQL commands which can be executed dynamically
· You must monitor the creation of databases
· Each time a database is created, send a message to the secondary server
· The message will specify the database to be created, tables, etc.
· The databases on the primary server will need to be modified: both tables will need insert/update/delete triggers
· The triggers will send the changed data and the action in XML to the secondary server through service broker
· Have a job which monitors the age of your databases
· Each night, send messages through service broker specifying which databases have expired or the commands to drop them
· The secondary server will receive these messages and be linked to a stored procedure to execute the commands
· You should also find a way to log everything.
Alternatives:
· Use transactional replication
· You could automate the process of creating a new publisher and subscriber
· The new publisher / subscriber would be created each time a new database was created
· The publisher would use push replication
· Once a database expired, you would need to automatically dismantle your subscriber and publisher
· Not sure how you would drop the database
For more information on service broker, I recommend the following:
http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker
http://sqlserverpedia.com/blog/sql-server-2005/using-service-broker-instead-of-replication/
http://sqlbits.com/Sessions/Event9/Let_Service_Broker_Help_You_Scale_Your_Application
For more information on replication:
June 6, 2012 at 9:08 am
vinu512 (6/5/2012)
If the Database at the Primary Server gets updated every 2 seconds and you want the Db at the Secondary Server also to get updated the may be you should look at something like Transactional Replication
vinu512,
I appreciate the quick response. I was hoping to use the Transactional Replication approach however I was under the assumption that it was required to pre-define the dbs for the Publication and Subscription. Since the dbs are created periodically, that would mean the Publications/Subscription would need to be created periodically, right? I've used Transactional Replication for databases that don't change names and it looks like they will accomplish my objective (2 second replication) however I am afraid the new database names every day will break this solution. Do you know if this could be scripted somehow?
Thanks again for the response.
Derek
June 6, 2012 at 9:13 am
Jesse Reich (6/5/2012)
I'm not entirely clear on some of the details. Is it an existing process which creates the daily database on the primary server? Or are you also asking how to accomplish that part as well?But I will assume it's an external process or SQL Agent job that is creating a new database each day.
........
Jesse,
Thanks for the thorough reply. I will need some time to review all of your information but it looks like both of your solutions are reasonable. FYI, you are correct that there is an external process creating the databases and updating the tables within the databases. All that I am concerned with is moving the contents of the primary to the secondary for redundancy purposes.
I will get back to you later today when I have a chance to test a few things out.
Thanks again,
Derek
June 13, 2012 at 4:42 pm
Jesse Reich (6/5/2012)
Alternatives:· Use transactional replication
· You could automate the process of creating a new publisher and subscriber
· The new publisher / subscriber would be created each time a new database was created
· The publisher would use push replication
· Once a database expired, you would need to automatically dismantle your subscriber and publisher
· Not sure how you would drop the database
For more information on replication:
We've made a few changes and now we will be writing to both SQL Server instances at the same time from our 3rd party software. This leaves me with the problem of ensuring that the data is synchronized after one of the servers is down for maintenance.
Either way, I am still going with the Transactional replication route and was hoping you could help me out with a couple issues I am seeing. I am able to get the publisher to write to the subscriber however subscriber to publisher communications are not working properly. Here is how I have configured the system:
SVR1: Publication Server
Transactional publication with updatable subscriptions
All permissions are configured for a domain admin account which is configured for both databases.
SVR2: Subscription Server
I've tried both Push and Pull subscriptions
Updatable Subscriptions - Replicate Checked - Commit at Publisher is Simultaneously commit changes
Login for Updatable Subscriptions: Use a linked server or remote server that you have already defined
When I finish this I see the following error message regardless of push or pull subscription:
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SRV1" was unable to begin a distributed transaction.
Changed database context to 'dbname'
OLE DB provider "SQLNCLI10" for linked server "SVR1" returned message "No transaction is active" (Microsoft SQL Server, Error: 7391)
With Push subscriptions I get data transfer from the publisher to the subscriber however not from the subscriber to the publisher. With Pull subscriptions, I get no data transfer.
Any thoughts would be appreciated,
Derek
June 13, 2012 at 6:17 pm
Have you seen the knowledge base document for your error message?
http://support.microsoft.com/kb/954950
Also, it sounds like you are attempting bi-directional replication? You mention above that you are trying to replicate from subscriber to publisher? Is that part of the plan or are you just trying to get it to work?
In your earlier post you wrote that the secondary was for redundancy, so I'm a bit confused regarding the need to push from subscriber to publisher...
If you are looking for bi-directional replication, I recommend looking for Hilary Cotter's writings online. Here is a sample:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply