July 15, 2022 at 10:41 pm
Hello
I have a database copied on two servers. Same name.
I have tried a merge replication but it keeps failing on various objects. I have removed objects one by one and left with the bare minimum tables and still won't work.
Users won't mind using one database as master and do all the updates in that one. So I can backup and restore to the other server every night.
They both have the same name on the two separate servers.
How can I backup a database on one server and restore overwrite on to the other server as a scheduled job to run every night.
I have the option of making a case to upgrade the SQL to 2019 enterprise if I need to.
Any ideas would be most appreciated.
Thank you everyone
July 16, 2022 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 17, 2022 at 12:35 am
Write a stored procedure to do the restore and schedule it as a job?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2022 at 2:09 pm
>>I have tried a merge replication/Users won't mind using one database as master and do all the updates in that one.
I would have thought that transactional replication would make more sense. You could also look at log shipping with the shipped db being recovered in standby mode.
If you are still on SQL 2012 you should upgrade as it went out of extended support six days ago so there will be no more security patches.
If you have enterprise edition you could look at running reports from a read only replica using availability groups.
July 17, 2022 at 4:14 pm
p.s. There's no way that I'd take all the time to upgrade 2012 to 2019. 2019 is already 3 years old and 2022 has some fairly handy new things and maybe even some fixes for stuff that's still an issue in 2019.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2022 at 4:16 pm
What is the purpose and goal for this secondary instance? What issues are you trying to resolve with this secondary instance?
If the goal is to just have a read-only secondary for reporting/extracting data - then upgrading to SQL Server 2019 Enterprise and building out an AG may be the best option. However, that is quite the expense and would need a detailed cost benefit analysis before implementation.
A backup/restore may be a good enough option - and that can be coded in many ways. It could all be done using T-SQL or Powershell - or other scripting languages. The best way to accomplish this will depend on what you are comfortable implementing and maintaining - and can implement. For example - a lot of organizations refuse to allow xp_cmdshell and that could be a requirement if you need to copy backup files to or from the server.
There are many other options - log shipping may also be an option as well as transactional replication. There are also third-party options available for making copies of databases available.
Ultimately it all comes down to the purpose - and really how much money, time and effort you are willing to spend.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 19, 2023 at 7:19 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply