Setting up Azure SQL Readable Secondaries – Part 2

Tom Availability, azure, sqlazure No Comments

Previously we provisioned three Azure SQL Servers, one Primary with two Readable Secondaries.

Next we need to create a new user with db_datareader permissions in this example, perhaps for a reporting application.

First we need to add an allow rule in the firewall on each server for our IP, in this example 1.2.3.4 so we can access the databases externally.

Firewall Rules


Now we can use SQL Server Management Studio (SSMS) to access each SQL Azure database directly.


Setup our Primary.

Connect to your database using SSMS and on the connection properties, second, tab enter master in the connect to database option.

Then run the CREATE LOGIN command;
CREATE LOGIN MyUser WITH PASSWORD = ‘P@ssword1’;

CREATE LOGIN MyUser WITH PASSWORD = 'P@ssword1';

 

Now lets get the SID of our user.
SELECT [sid]  FROM [sys].[sql_logins]  WHERE name = ‘MyUser’


Copy the output from the above query, we’ll need it soon when creating the server login on each secondary.
0x0106000FF0000064000EA000000000009C71D9C12F932B47976883220239FC4E

Then change context to the database by right clicking on your database and selecting New Query and then run the following commands, which add the user to the database and setup the datareader role; 

CREATE USER MyDatabaseUser FOR LOGIN MyUser; EXEC sp_addrolemember N’db_datareader’, N’MyDatabaseUser’;
 
That’s the Primary configuration completed!

Setup each secondary

Connect to a secondary with admin credentials and again in the context of the master database, again using the connection properties tab run the CREATE LOGIN command below, note the WITH SID.
CREATE LOGIN MyUser WITH PASSWORD = ‘P@ssword1’, SID = 0x0106000FF0000064000EA000000000009C71D9C12F932B47976883220239FC4E

The logic here is that the database user MyDatabaseUser is copied over with the database synchronisation but the server level login MyUser is not. For the new MyUser server level login on the secondary to map to MyDatabaseUser the SID must match.

Repeat the process “Setup each secondary” for each secondary readable.
 
Once all your secondaries have the server login MyUser we can test our setup.
 
Testing our setup
 
Now you can connect from SSMS to each secondary readable database using the MyUser@servername and P@ssword1 password but in the context of the database, not master, again using the connection properties tab.
 
This will allow you to run queries against your readable secondaries.
 
Another option is to use Azure Active Directory to manage user access to both primary and secondary databases and therefore eliminating the need for managing the database credentials.
 
Thanks for reading!
Setting up Azure SQL Readable Secondaries – Part 2

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest