...building hybrid clouds that can support any device from anywhere
During the course of the last few weeks and months, we’ve been publishing many different articles related to automating database deployments, templates for database workloads in Windows Azure Pack (WAP), blog posts about how to leverage the WAP APIs to work with virtual machines and/or databases, and many others. All of these relate to specific subtopics in a larger “Database as a Service” solution.
Well, now is the time to bring everything together, and summarize some ways to enable Database as a Service with the Microsoft Private Cloud stack!
This time, instead of writing a blog post series, we’re providing the content as a downloadable document, similar to an eBook format. This will also simplify offline reading when needed.
– titled Enabling Database as a Service with the Microsoft Private Cloud stack –
is available here:
The document includes:
Update : In May 2015, this paper was updated with the latest changes and enhancements in Update Rollups (UR) 3, 5 and 6 for Windows Azure Pack. There are also a few additional FAQ entries in the second part of the document. Download link remains the same, and blog post for v1.1 is here.
The full agenda follows:
- Templates for virtual machines with SQL Server
- Plan subscription
- Creating a virtual machine with SQL Server installed
- Creating a database
And, finally, here are the links leveraged throughout the document, provided as a reference for convenience:
We hope this all up document on the Database as a Service topic will be helpful. Thanks for reading and, as always, feel free to share any feedback!
In your "Enabling database as a service with the Microsoft Private Cloud Stack" document you mention the following in relation to Active directory use with the SQL resource provider in WAP - "Another prerequisite is that the SQL Server instance should have
SQL Server authentication enabled, as this is required to register the server with the SQL Server resource provider). While this requirements may sound surprising for some of our enterprise readers, remember this can also be used in service providers scenarios,
where the SQL Server fabric servers are likely to be in a hoster domain, fully decorrelated from the tenants domains." Is this still a limitation? I would prefer to be able to make use of active directory (windows) authentication as it allows for easier transition
for existing customers in the new cloud model. I believe a roll up 2 was released recently, has this been changed in the new version or is it just for web services?
In addition to the above I am just wondering if for example you didn't use AD and relied on SQL authentication only, as a service provider, how would you know who to bill? If the client is forced to use domain/username it tells you exactly who is using the
system but as for SQL names the user could just type in "Bob" or "HRSVC3030" or something.
This is not changed in UR2. Remember you can still have Windows Authentication enabled via mixed mode, it's just that you also need SQL authentication when registering the server, and for dbowner assignment for the new tenant databases. We heard the feedback
from multiple customers that having the ability to create new tenant databases with Windows Authentication instead of just SQL authentication would be a nice addition, and providing more flexibility like this is under consideration, but we cannot commit to
any timeframe yet.
Regarding your other question, the SQL Server Resource Provider in WAP keeps tracks of databases created by a tenant in each subscription ("tenant" being the account name that can be seen in the "my account" section of the tenant portal, or used to sign into
WAP) . So chargeback data can be retrieved for the actual subscription owner and databases (number of databases and space allocated).
Me again. I am busy with a POC and am exploring everything to see how I can deliver a database as a service solution to multiple customers. I am struggling to find decent documentation etc. on the DaaS specifically, particularly on how to put the whole
thing together form a SQL perspective. If you know of any links etc. I would be very grateful if you could point me in the right direction. This blog is a goldmine, I am just struggling to find SQL specific stuff. I am finding funnies for example in WAP each
database that gets created only has one SQL account tied to it (given dbo access). In our environments we typically have a number of users that would use a database. I see that in management studio you can (as a user) add other users to your database but they
have to have been created first (in master) and you have to know their login name as you can't see them at all! Also I see a user can delete a database in management studio (they have dbo rights after all) but that deletion is not communicated to the WAP portal.
You can even add extra storage (using add on’s) to a database that has been deleted! I am assuming I am going to have to make use of policies etc to make some stuff work. Is there a way for example to have database created as partially contained on creation
or instead of giving a user dbo rights have them restricted to read / write?
Apologies for all the questions!
When it comes to documentation, the document from this blog post covers mainly the management side of things, but does not go into details about how to design the SQL Server fabric, understanding that you can rely on existing SQL Server best practices for that,
which may vary based on your requirements. For example, if you will eventually be using more IaaS than PaaS in your Database as a Service approach, a document covering some guidelines can be found here :
I am in touch with the WAP team about the other items you mentioned below, and will get back to you on this when I get more details
I wanted to post a follow up to my previous comment, but was waiting for Update 3 for WAP to be released. It was released yesterday and is available/described here :
The reason I was waiting for Update 3 is that we've made a change in that update, on how databases are created by the SQL Server provider:
WAP will not give tenants dbo rights on databases created after Update 3 is applied and, if contained database authentication is enabled on the hosting server, WAP will create the database as contained by default after Update 3 is applied. While I have not
personally tested it yet, it is my understanding that creating contained databases by default should help with self-service user creation in Management Studio. Also, as a consequence of not giving tenants dbo rights, they should now not be able to delete their
databases outside of WAP.
This is item #10 in the Update 3 KB article previously mentioned.
Note that already existing databases won’t be changed, and a TechNet article is being published to explain how admins can "migrate" existing databases to the new settings.
That looks like good news. It seems ever since I started on this journey that WAP really is evolving rapidly. When somone asks about capabilities during a POC I sometimes feel like answering "lets see what next week brings!" :)
Thanks Kevin. Here is also a good place to suggest features for Windows Azure Pack, or just to pile on to something already in the list:
The WAP team actively monitors these votes and comments.