Database Redundancy
Looking for some recommendations on database redundancy and replication. Basically we are wanting to ensure we have site redundancy in our deployment. I'm not sure if RDS has any built-in capability for redundant RDS servers, but at minimum we would need some type of database replication between two physical sites.
If we did just SQL database replication, is there any way for RDS to handle failing between the two? Would it just be going into RDS and pointing the connection to the new database?
Any examples on how other people have accomplished this? We're just trying to nail down how we should approach this....
Thanks!
All Comments (11)
Hi
Its built in the .net SQL Provider, you simply have to specify a failover partner. The setting is in the advanced settings of the advanced tab
It's well described here http://msdn.microsoft.com/en-us/library/5h52hef8(v=vs.110).aspx
Maurice
When setting up a failover SQL server in my Remote Desktop Manager Server Console settings I receive an error
Use of key 'failover partner' requires the key 'inital catalog' to be present.
when are you getting this error? Test Server, Test database or when you run?
Maurice
When I run Test Server is when the error occurs.
Hi,
What version do you use? If I remember correctly, this is fixed in the latest version. The initial catalog is the property name for the database name in the connection string.
David Hervieux
I am using version 9.1.4.0 of the Remote Desktop and RDMS 2.2.10.0.
ArgumentException - Use of key 'failover partner' requires the key 'initial catalog' to be present.
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Devolutions.RemoteDesktopManager.Managers.RDMSManager.TestConnection(String connectionString)
at Devolutions.RemoteDesktopManager.Frames.FreRDMSDatabaseWizard.butTestConnection_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
My connection info
Thank you for those details. The fix was in the SQL Server database. I will port it in the RDMS console.
David Hervieux
Hi
Just to clarify, the issue is only for the Test Server feature. You can still put the failover partner value and it will work as expected at run time.
Best regards,
Maurice
I tested failing over my SQL mirror to the secondary node and my RDMS would no longer connect to the database correctly. I failed it back and it returned to good working order. Does your statement apply to only the database connection or the RDMS as well?
It's only for the database and not for RDMS
David Hervieux
This is now fixed internally.
Thank you
David Hervieux