what’s the role of a “Master” database in a SQL Azure world?

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:

paras doshi blog on facebookparas doshi twitterparas doshi google plus

Review your SQL Azure security issues and Attack surface, Check out “SQL Azure security services”

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.

This is the home page of “SQL Azure security services”: http://www.microsoft.com/en-us/sqlazurelabs/labs/sqlazuresecurityservices.aspx .To get started, you can watch a video tutorial here but if you prefer to read, let’s gets started:

First go to https://labs.web.sqlsecurity.azure.com/scanner.html ; This is the place to access SQL Azure security services. Bookmark (save) it!

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.

1

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)

2

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:

 

3

After clicking on scan, after few seconds, I got a link to access the report:

image

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:

image

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”

image

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.

7

 

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.

How to rename a SQL Azure database?

Can we rename a database in SQL Azure? Absolutely! In blog post, we will see how we can do that?

First, few things to note:

1. You need to connect to Master Database.

2. And you can rename only user databases. That means the master database cannot be renamed.

3. And the Alter database (which is the TSQL statement to rename database) should be the only statement in the batch.

Now, Here is the query:

 

alter database <old-database-name>

modify name =<new-database-name>

go
Here is the screenshot when I tried to rename the database name by login with the principal server login into the Master Database:

rename database SQL Azure

So that’s it – you can rename the database in SQL Azure.

Summary – SQL server to SQL Azure migration << Paras Doshi

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:
 
Scenario/Tool Generate script wizard SSIS BCP DAC v2.0
Migrate SQL schema YES YES   YES
Migrate Data YES YES YES YES
Migrate large Data   YES YES YES

 

Blog Posts featuring tools that help migrate SQL server database to SQL Azure:

BCP:

Migrating SQL server Data to SQL Azure using BCP << Paras Doshi

SSIS:

How to use SQL server Integration services (SSIS) to migrate data from SQL server to SQL Azure << Paras Doshi

Generate script wizard:

How to use Generate script wizard to migrate SQL server Database to SQL Azure << Paras Doshi

Dac’s v2.0 (SQL Azure Import Export CTP):

[ SQL Azure Labs ] Exploring SQL Azure Import/Export Feature (CTP)

 

Related posts:

Migrating TSQL file and SQL server 2005 / 2008 Database to SQL Azure using SQL Azure Migration wizard << Paras Doshi

“Migrating Data into Microsoft’s Data Platform – SQL Azure” << SolidQ Journal Article

 

CrossPosted From: Summary – SQL server to SQL Azure migration << Paras Doshi

 

Can you please consider posting your valuable feedback/comments/suggestion in comments section? Thanks!

I just logged an Idea on My Great Windows Azure Idea:

Being an Azure consumer, i feel the need to monitor my usage. It is more critical than ever to be able to keep a tab on the usage because the charge of the service is directly proportional to my usage. And since i have submitted my ultra secret credit information – i have to keep monitoring it so that i do not get a surprise when my credit card summary shows up! So i just logged an idea on http://www.mygreatwindowsazureidea.com that proposes that – if the service administrator wishes – he could get the billing summary daily/weekly via Email. This saves the time to log in to the billing portal (MOCP portal) and check for charges. So if you feel the same, i request you to vote for the feature at http://www.mygreatwindowsazureidea.com/forums/34192-windows-azure-feature-voting/suggestions/1990157-email-weekly-daily-billing-summary.

My Previous ideas:
1. http://www.mygreatwindowsazureidea.com/forums/34685-sql-azure-feature-voting/suggestions/1864747-ability-to-have-more-than-one-account-administrato [ PLANNED STAGE for now ]
2. http://www.mygreatwindowsazureidea.com/forums/34685-sql-azure-feature-voting/suggestions/1813869-multiple-servers-located-in-different-data-center [ COMPLETED! ]

want to learn SQL Azure – here is an "Introduction to SQL Azure" tutorial (FREE!)

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! 

LINK: https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=168190

 

As a cherry on the cake – you will be able to download a certificate after successful completion of course:

 

sqlazurecertificate

 

Do not wish to take a course? You just wished you could sit back, relax and watch a video. Don’t worry – Here’s the link:

http://media.ch9.ms/ch9/5778/84cc004d-8c1b-4ce4-ba8c-9e6801505778/WinAzureJumpStart07_ch9.wmv

SolidQ Journal Article : “Migrating Data into Microsoft’s Data Platform – 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:

1. BCP

2. SSIS

Also, Migration process using SQL Azure Migration Wizard is also discussed at the end of the article.

 

You can read the article here: http://www.solidq.com/sqj/Pages/2011-May-Issue/Migrating-Data-into-Microsofts-Data-Platform-SQL-Azure.aspx

Automating SQL Azure backup to On premise Disks using a SQL Azure management cmdlet

[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

1

A complete setup guide is available at https://www.cerebrata.com/Docs/AzureManagementCmdlets/readme.pdf

Now, once you are done with installation and post installation steps – you will be able to see the list of cmdlet’s by running

Get-Command –PSSnapin AzureManagementCmdletsSnapIn

4

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:

Backup-Database -Name “database1″ -DownloadLocation “D:\temp” -Server “mydatabaseserver” -UserName “username” -Password “password” –Verbose

here,

Backup-Database s the name of the cmdlet

-Name is the name of the database to be backed up

-DownloadLocation is the local location where the data will be backed up

-Server is the name of the server in which the database to be backed up is located

-Username is the username to login to the specified SQL Azure server

-Password is the password corresponding to the specified username used to login to the specified SQL Azure server

-Verbose is the parameter that will display detailed information about the operation performed by the cmdlet

After the back up is done, you will receive following message:

5

Now, if you wish to backup only specific table that you could do that by run the command:

Backup-Database -Name “database1″ -DownloadLocation “D:\temp” -Server “mydatabaseserver” -UserName “username” -Password “password” -TablesToInclude “<name1>,<name..n>” –Verbose

Now, one can schedule a PowerShell command – do that to automate your back up process!

If you wish to backup the database to blobs instead of local files. please read: http://parasdoshi.com/2011/07/03/sql-azure-backup-to-windows-azure-blobs-using-azure-management-cmdlet-powershell/

Just note that:

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.

 

Related Posts:

programmatically verifying that the underlying database is SQL Azure

There are two ways to programmatically verify that the underlying database is SQL Azure

1. By running the TSQL command: select @@version
if the underlying database is SQL Azure, the query will return something like:

“Microsoft SQL Azure (RTM) – 10.25.9640.0 Apr 2 2011 18:10:33 Copyright (c) Microsoft Corporation”

The returned value clearly states that the database is SQL Azure

2. By running the TSQL command: SELECT SERVERPROPERTY('EngineEdition')
if the underlying database is SQL Azure, the query will return value 5

Output of SELECT SERVERPROPERTY('EngineEdition') as value

To know more about serverproperty. go here

Extending SQL Azure with Azure worker roles << Guest Post on Pinal sir’s blog

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.

To read the article, please visit: http://blog.sqlauthority.com/2011/06/13/sql-server-extending-sql-azure-with-azure-worker-role-guest-post-by-paras-doshi/

Thank you Pinal sir for this wonderful opportunity.