Month: July 2022

SQL Azure (Basic notes on backup operation)

If you are running things on the cloud you always want to be thinking about backups and ensure that you are really aware of what is happening with backups and how to restore from backups. You should regularly check what is happening with backups and regularly practice restoring databases.

Basic / Standard and Premium SQL Azure has two basic options for restore.

Point in Time Restore and
Long Term Retention

They perform the same task – they collect old databases that can be used to restore to a server if need be but what varies in the UI by which you restore them.

First point – Long Term Retention Policy is attached to the server and you go into it by first navigating to your server and then looking to the left hand section marked as Data management.

Select Backups and you will be presented with a list of all the long term backups of the databases on that server. The following are a couple of basic dtu databases and what can bee seen on the fictional server 01SeverName

In the above table you can see that Long Term Retention policy has NOT been set. How do you change this.

Firstly ensure that you are on the retention policies tab and then tick the particular database you would like to set the retention policy for in the lower section this will allow you to Configure a policy for that particular database.

Here are the options for a BASIC DTU database

Compare this with the options for a database in the Standard Tier

Set it up and then hit apply and you should be good to go.

MS SQL Azure – CREATE USER with read only access to 2 tables

In a lot of situations you may need a satellite website feeding off the main website with greatly reduced access.

It is a good idea to only give that user access to only what they need and limit it down to the bare minimum.
Here I have an azure database called LandRegister and I connect to it in the usual way through SSMS.

Use LandRegister

USE LandRegister
GO

CREATE user TestUserRestricted with password = 'Password01';

Next you can go to the security section after a refresh to see if the user has been added.

And then grant access to the tables you wish them to see.

USE LandRegister
GO

GRANT SELECT on dbo.T0003NLPRSites to TestUserRestricted
GRANT SELECT on dbo.T0024Authority to TestUserRestricted;

TestUserRestricted can now be used by your web application without any fear that
1) A USER cannot login to a server through SQL Server Management Studio
2) That they can see anything except the tables listed.
3) That in this case they can do anything except select statements on the stated tables. (So no write delete or updates on table)

Here I give full access to the table

USE LandRegister
GO

GRANT INSERT,SELECT,DELETE,UPDATE ON 
dbo.T039Country to TestUserRestricted;

And it works with views as well

Use LandRegister
GO

GRANT SELECT on dbo.v032 to TestUserRestricted;