April 16, 2020 at 1:08 pm
Hi All,
This is sampath i am new to sqlservercentral community,i am begginer in sql related queries please help me to achieve.
I have two databases in same server assume Realtime database and Backup database these two databases contains 25+ tables.
i have done insert/delete/update in Realtime database automatically Backup database should be updated.
Is there any option other than transactional replication to acheive the above requirement?
Thanks ,
Sampath
April 16, 2020 at 2:23 pm
Service broker could handle that. Pop a trigger on each table that fires off an XML message to the backup database of what changed and a stored procedure on the backup side parses the message and updates the tables appropriately.
You would need at least 1 trigger per table that would fire on insert, update or delete.
another option, if you don't need real time data, would be to have a scheduled job and use SSIS to push the data across every hour.
Advantage of service broker is that it is realtime. Advantage of SSIS is you can control when the syncs happen and it is easier to test.
With service broker, my recommendation is to reuse conversations where it makes sense. At my workplace, we have a data push from one system to another that happens infrequently, so we use 1 conversation for the data push even though it updates multiple tables. On a different system we get approximately 30,000 messages per day, so we have one conversation per table.
Messages inside a conversation will always be processed in order. Messages in different conversations can be processed in any order. So if you have any foreign key constraints, make sure your messages flow in a way that you don't break any FK's.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 16, 2020 at 2:51 pm
Thanks for taking time to reply Gale!!
Actually i don't have knowledge in SQL related stuff,I am very much thankful if you can provide small examples in the above concept you mentioned.
Please help me if you can!!
Thanks,
Sampath
April 16, 2020 at 3:27 pm
So, SSIS is a tool that would need to be installed on the server and you'd build things up for it. Service broker is a series of scripts you would need to run. Neither are really good things to reply on a forum about as they would result in quite long posts.
For service broker, you can read up on how to set it up here:
There are other articles that you can find on google.
If you don't have knowledge in SQL stuff, I would recommend talking to your DBA to get these set up or at least asking the DBA what methods they would recommend. There may be reasons why they don't want to use one of these. For example, service broker may require you to open up more firewall ports. SSIS may require you reduce the memory allocated to SQL so you have some left over for SSIS to run.
SSIS tutorial:
another good service broker article:
https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics
If you look at any of those, you will see why it doesn't make sense to provide a small example - they are both complex systems to learn, but once you get it set up, it is mostly "setup and forget about it" unless something fails. That would be the next thing to learn- where to look when something fails.
Depending on the requirements (does the data need to be realtime or not), SSIS is probably where I'd start. It is easier to learn, test, and troubleshoot. It does have some problems though like it uses more memory on the server (it operates outside of SQL Server memory space) and can cause CPU spikes depending on processing that is required for the transfer. Service broker is easier on memory as you run it all inside the SQL Server engine, but if a message fails to process, it will take the service broker queue down while you debug what went wrong.
Both methods require good troubleshooting skills. You also want to pick a solution that both you and your DBA team can support.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 16, 2020 at 4:12 pm
Thanks for taking time to reply Gale!!
Sorry to trouble you and disturb you.
one last question i am giving an example:
In my server i have two databases Test1 and Test2
Test1 contains 1 table EmpDetails where fields are id,name,age
Test2 contains same table EmpDetails where fields are id,name,age
insert/delete/update in Test1 EmpDetails table should automatically updates in Test2 EmpDetails table
I hope you understood ..
can the above example is achieved using triggers or Synonym ,i have doubt whether this can be achieved or not.
Thanks,
Sampath
April 16, 2020 at 4:38 pm
If your two databases are on the same instance, and depending on server setup, you should be able to do that with a trigger.
If they are on different instances, a linked sever with a trigger can handle that too.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply