SQL Server stored procedure to update two databases on differrent servers
The two servers have exactly the same setup (databases/tables etc). I want one of the tables (on both servers) to have the same data. So the update procedure on server A should also be able to update server B.
I want this to happen with each call to that update stored procedure with out any time delay. Any thoughts?
I did look at the other questions but this aspect of multiple servers seems to be un-answered. Any thoughts?
Edit to clarify: The Server B always should follow Server A. (Also, the stored procedure that updates A can be edited to update B… if the Dbs were on the same server… this is a different case.)
One Solution collect form web for “SQL Server stored procedure to update two databases on differrent servers”
Take a look at SQL Server High Availability solutions. Log Shipping, Failover Cluster, or Database Mirroring may fit your particular needs. It depends whether you need a failover for backup or an online DB for reporting.
Side Note: Database Mirroring will be deprecated in future SQL Server versions and should be avoided. It is being superseded by AlwaysOn Availability Groups in SQL Server 2012.