Today we’ve got a treat for you – Matt, one of our amazing texperts, is going to be dropping some advice on the wonderful (and extremely confusing) world of SQL mirroring! Go on, get your learnin’ on!
Having two SQL servers is always better than having one. However, when moving from a standalone SQL instance to a High Availability (HA) mirrored SQL environment, there are certain things to be aware of. I’ll go through the main problems that we get asked about to make the transition easier.
Database mirroring is database level replication between two SQL instances, and allows failover of the databases between instances should one of them become inaccessible. However, changing to an environment like this does require some changes to be made:
Change in connection strings
In order to make use of both SQL servers, any connection string used to access the database will need to be modified so that both servers are referenced. There are many different types of connection string; below is a standard example that may be used on a standalone SQL server:
Data Source=Server1;Initial Catalog=myDataBase;Integrated Security=True;
The below gives an example of a connection string for mirroring, based on the above. If the first server is inaccessible then the second server will be used:
Data Source=Server1;Failover Partner= Server2;Initial Catalog=myDataBase;Integrated Security=True;
Referencing both SQL servers in the connection string means that if the principle server is offline, the mirror server will be used to serve content.
This next part talks about ‘users’ and ‘logins’. If you aren’t sure of the difference:
- A ‘login’ grants access to the SQL server (instance level) and can be managed within the ‘Security’ container within Management Studio.
- A ‘user’ grants access to a database within the instance and can be managed within the database container using SQL management Studio.
Although the login and user often have the same username and password (though they don’t have to), both are separate objects within SQL and are linked by the UID of the login. So if a login is manually created on both servers, the UID does not match and therefore the login does not work (known as orphaned users).
Microsoft does have a script to assist with this, which should be used when creating logins on a mirrored server; it basically creates a stored procedure to execute on the principle server. Executing the stored procedure creates a CREATE LOGIN script, which is then executed on the mirror server to create the login with the same UID.
If you have any further questions about SQL Mirroring then please contact your Account Manager, or our support team on 0800 230 0032.