One of the question that pops up in the head of first-time SQL Azure user is “What is Master Database doing in my server”. They try to delete/drop it. That does not happen. Fun fact: I tried dropping Master database when I had got my hand on SQL Azure for the first time. Any-who what we speculate is that “OMG! I paying for this SQL Azure Master database!” Wait…No, read this:
You are not charged for the SQL Azure Master Database.
Ok Good news, But why does this “read-only” master database exist?
Here is the information I have used that is available via SQL Azure Master Database:
It has SQL Azure usage metrics
It stores logins for a given SQL Azure LOGICAL server (To manage Server-Level security)
To view list of all databases via sys.databases
To create databases via TSQL. You do that by logging into master database first.
What do use SQL Azure master database for?
That’s about it for this post.
And Let’s connect! I Look forward to Interacting with you on any of these people networks:
There’s an interesting SQL Azure lab in public preview called “SQL Azure security services”. It let’s you review security issues and Attack surface of your SQL Azure database. To give you a taste of the usefulness of this service, following is information that you’ll find for specified database(s)/server:
– A List of security issues. For e.g.: A particular User Name has read permissions on ALL tables/views
– List of Usernames in a database along with database permission for each user
– List of Roles along with it’s members
– List of all user-created database objects
Useful? Interesting? continue reading to know more..
In this blog-post, first I would give a step by step tutorial on how to start using it and then walk you through a report that I generated for a sample database on my SQL Azure server.
Now here you’ll be asked to login using SQL Azure credentials. I am going to login as service administrator. you’ll need to login using credentials that has access to databases in a server and select permission on tables for databases that you want to inspect.
Click on next and here you would be asked to select between scanning the complete server vs scanning individual databases. I am going to scan a complete server (which has just one database)
Ideally, if you are scanning the entire server then storing the html report in an Azure storage account is the way to got but I just have one database in the server that I specified and so I know it would not take long, so I opted for HTML output to browser:
After clicking on scan, after few seconds, I got a link to access the report:
And I would open that report (it’s in HTML format) in the browser.
The first tab is “Security issues” and it would look like:
here , you can drill down a particular issue and view Description along with recommended mitigation.
So basically, what the above report is saying me is that I have a username indiawebdev (which I purposefully created for demoing) which has select permission on all tables/views in the sample database AdventureWorksLTAZ2008R2.
And it suggests me to grant minimum set of permission on various objects.
Now let’s go to the second tab “Attack Surface”
Quick note: Since I had opted to “scan complete server” earlier, I am getting the server level information like Logins, server roles, databases but if I opted for “scan individual database” then the server information would not be provided.
Any-who let’s drill-down a little bit see what information is made available to us:
For my Database AdventureWorksLTAZ2008R2, I can see a list of usernames along with login name, type, database permissions. So with the help of this information we can review all usernames and see if there are any “unwanted” users. And also it let’s us review the permission granted to each username at db level.
Also, it lists all roles along with it’s members and this information can be used to verify that a member is granted the minimum level of permission.
It also lists all user-created database objects along with schema/type, owner information.
Now from server information, we can use the information in the Logins information to find “unwanted” logins.
It also lists the server roles by Name along with it members so here we can see if members belong to desired roles.
And it also lists Databases with it’s database owner.
So that was about it on exploring the report. Check it out! Review Attack surface of your SQL Azure server and become aware of the security issues.
Thus, in this blog post, We learned about “SQL Azure security services” in SQL Azure labs. We also saw a sample report and explored few parts of it. And I hope you got an overview of what SQL Azure security services has to offer as of today.
In this blog post, I aim to summarize database migration options that I have been blogging about for past few weeks. Choosing right tool is a key decision when you decide to migrate SQL server to SQL Azure – And I hope this blog post can help you decide which is the best tool to be picked for your scenario:
Here is the summary:
Generate script wizard
Migrate SQL schema
Migrate large Data
Blog Posts featuring tools that help migrate SQL server database to SQL Azure:
On Microsoft learning, clinic 10322 named "Introduction to SQL Azure" is an excellent course that is available (freely) to anyone who wish to get started with SQL Azure. It will just take couple of hours to acquire basic understanding of SQL Azure!
The article titled “Migrating Data into Microsoft’s Data platform – SQL Azure” discusses contemporary tools that are available to migrate SQL server data into Microsoft’s cloud based database i.e SQL Azure. The article is then broadly classified into 1. Schema Migration 2. Data Migration
Following are the tools discussed for Schema Migration:
1. Generate and Publish script wizard (SQL server Management studio)
2. DAC project (Visual studio 2010 Ultimate edition)
Following are the tools discussed (along with best practices) for Data Migration:
Also, Migration process using SQL Azure Migration Wizard is also discussed at the end of the article.
[Update: When i wrote this post, the SQL Azure import and Export CTP was not available. But now it is – and it would let you export a SQL Azure database on local disks too. And since it is command driven, you’ll be able to automate the process on a local server]
In this article, I am going to discuss automating SQL Azure backup to an on premise location using a SQL Azure management cmdlet. Alternatively, you can also schedule a backup that backs up your SQL Azure data to Azure storage. But before we discuss how to go about it – couple of disclosures 1. Powershell cmdlet is to be executed from on premise machine as SQL Azure does not natively support Powershell but you can always choose SQL Azure as your target for your Cmdlet running locally 2. I am going to use a third party Azure management cmdlet from Cerebrata. You can download it from https://www.cerebrata.com/Products/AzureManagementCmdlets/Download.aspx
For automating the SQL Azure backup we are interested in the cmdlet named “Backup-Database” that let’s you back up SQL Azure to a local disk using bcp.exe utility. It also allows you to back up the database to blob storage. But remember the SQL Azure data transfer rates apply
Now, to backup a SQL Azure database to local disks run:
For now, the restore database through the Management CmdLet is not available – But basically the bigger picture of this post is to point out that – using Powershell CmdLet’s we will be able to schedule SQL Azure tasks! and that is definitely a good news for an IT pro.
My guest post on “Extending SQL Azure with Azure worker role” got published on Pinal sir’s blog. In the article, I discuss three lightweight solutions that augment the contemporary capability of SQL Azure. They are:
1. Automating SQL Azure database backup process
2. Lightweight SQL server agent for SQL Azure
3. Synchronization of databases using SYNC Framework.