sreekanth bandarla

Contained Databases in DENALI

Contained Database : A Brand new interesting feature introduced by Microsoft in SQL Server Denali(2011).
let’s get straight to the point. Simple and straightforward use of contained Database(s) – Let’s say you’ve multiple applications sharing the same SQL Instance and you’ve multiple Jobs,Linked Servers, Logins for respective Databases. consider the scenario, where you are trying to migrate the database, the entire pain comes when dealing with respective Logins, Jobs,Server Objects.
With the introduction of “CONTAINED DATABASE”, you can assume that you are isolating your application(database) from rest of the applications(Databases) on this SQL Instance. So…They are independent of Instance itself in terms of Instance level dependencies!
Are they really independent in every aspect?? No….definitely not! let’s say your SQL Instance is DOWN, then of course these Database(s) will also be Down ;-p. So basically we are going to introduce some level of isolation from rest of the Databases and some level of freedom(independence) from actual SQL Server itself!

Terminology :
I’ve taken this terminology(definitions) from MSDN Library. The link is provided below FYI.
http://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx
Application boundary:The boundary between the application model and the instance. Application model functions are within the application boundary, and instance functions are outside the application boundary.
Contained:A user entity that lives entirely within the application boundary.
Uncontained:A user entity that crosses the application boundary.
Non-contained database:A database with containment set to NONE.
Fully contained database:A fully contained database does not allow any objects or functions that cross the application boundary. Fully contained databases are not currently available.
Partially contained database: A partially contained database is a contained database that allows features that cross the application boundary.
Contained user:There are two types of users for contained databases.

Contained database user with password (SQL Auth)
Contained database users with passwords are authenticated by the database.
Windows principals (Windows Auth)
Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database.

Note: Users based on logins in the master database can be granted access to a contained database, but that would create a dependency on the SQL Server instance, so creating users based on logins is not recommended.

So what exactly are we gaining from this new feature?? It stores the below mentioned thingswhich can make a DBA life much more painless…:)

It stores all the Cool info like Tables,Procs,Functions,Constraints,Views,Schemas..etc as any other regular Database. Besides this, It also stores Instance level objects like Logins, Application Agent Jobs, Error Msgs, Linked Servers etc. Hurrrray….we can escape from dealing with Orphaned Users, SID Mappings blabla…:) Tadaaaaaaaaaaaaa!!

Let’s start a Quick DEMO:

Step 1: By default “Contained Database Authentication” property is set to ‘0’-False as shown below.

We’ve to set this Option to TRUE from GUI or use the below code from T-SQL:

/*Enabled Advanced options*/

sp_configure ‘show advanced’, 1;

RECONFIGURE WITH OVERRIDE;

/*Enabled Database Containment */

sp_configure ‘contained database authentication’, 1;

RECONFIGURE WITH OVERRIDE;

Step 2: Now we can create a Contained Database as shown in below Screenshot.

Change the Containment type to “PARTIAL” from “NONE”. Default for any Database is NONE.

From T-SQL:

CREATE DATABASE [Contained_tst] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N’Contained_tst’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’Contained_tst_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\Contained_tst_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)GO

Step3: Now we’ve created a contained Database. Let’s play a bit with creating a test user and connecting via the new test user.

From TSQL:

USE [Contained_tst]GOCREATE USER [contained_usr] WITH PASSWORD=N’test’, DEFAULT_SCHEMA=[dbo]GO

Now let’s try connecting to the SQL Instance using the “contained_usr”.

Note: it might fail if you try to connect to default Master database because this particular login doesn’t exist at the Server level in the Master Database.Once connected through SSMS, It looks like the below Screenshot.

I’m not seeing any other Databases, Agent, Server objects…Literally I’m not able to see anything beyond this particular database. waiting for testing this Brand new thing in real environment!One More GOTCHA :“ALTER DATABASE” Syntax will fail for Contained Databases. We’ve to use “ALTER DATABASE CURRENT“!!Conclusion: IMHO this is a great new feature which we all(at least I was) were waiting since ages! Even though it is well publicized as a Contained Database is instance or server independent, has no external dependencies and has self contained mechanism of authenticating users, I would say no! It’s actually not Server independent. A contained Database can’t escape anything which impacts the Physical Server(Instance SHUT DOWN, SERVER REBOOTS etc).Hope this helps to give a Kick Start for exploring this new added feature of DENALI.

Restoring Master and MSDB Database(s) – System Databases

Guys, today was a very looooong day for us. We had to restore Master Database and MSDB Database on our Production Server…Yessssssssss, you heard it rightL

PS Rebuild Master is different from Restoring Master Database.

Let me tell you the story….what exactly forced us to make this dangerous move.

This weekend our Windows folks had a planned Windows Patching activity and they were doing some Net backup Upgrades on our Production Database Servers. Everything appeared to be normal, till they rebooted the machine. Once they initiated Reboot, Server was not coming up! OMG…you can imagine all kinds of screams on the call! They did all sorts of attempts to bring the server UP via HP ILO without any success:( They stepped into our Data Center and the result was the same…At last HP Engineer was Onsite, but considering the time factor (Business starts on Monday Morning), they decided to rebuild the Entire Windows Server! So… we, the SQL Server DBA’s decided to use our DR Server meanwhile. Unfortunately the Application Folks were not happy running on the DR Server for more than 48 Hours (Since they (we) identified few missing critical SSIS packages and Linked Servers. The Bad DBA who’s supporting this Server missed it for some reason. I’ll come up with a short Blog post very soon what needs to be considered besides Just the Database in your DR Strategy – Stay Tuned)

FYI we had the below-

SQL Server 2005SP3. (Pls. don’t ask me why are we still running on 2005 SP3 on this box;-p )

DR Strategy – DB Mirroring.

Well, So the Quick/Short Plan was:

1 – Windows Folks Reimage the Entire Windows Server.

2 – Storage team takes care of SAN and Drive Allocations etc.

3 – Once notified, We’ll Install SQL Server and recover the databases from our DR (the current Production) Server.

At last we got notified that Windows is all set and the Server is ready for SQL Installation. At this point interestingly one of the LUN (F$) was not visible to us. SAN Folks mi(e)ssed it. Again we pulled them and they had to do their magic. Well, it’s time to Install SQL and LiteSpeed and do the rest of Our Magic as DBA’sJ

1. We Installed SQL Server, Making sure that the DR and Prod Servers match the Build numbers.

2. Before migrating databases from DR to PROD Server, our plan was to restore MSDB and MASTER (We had >100 Agent Jobs and several linked Servers, Endpoints, tons and tons of logins). Our Poor DBA didn’t notice the importance of securing all that info in a secure place till date on prod Server (A very good lesson learnt). So we thought of Restoring Master and MSDB Databases to restore all that critical pieces of puzzle within short time and with less pain in our Butt!

Glad that We’ve a very reliable Backup/Restore Strategy. Soo….Always note that Database Backups (Healthy and restorable backups) are DBA’s Best of Best FriendsJ (I mean it).

Let’s Get Started with Restoring MSDB Database:

Let’s simulate a simple scenario here! Assume, the current Server Snapshot looks as below

Please notice tst, tst1 logins and DENALI linked Server and tst,tst1 Agent Jobs. Let’s assume the below snapshot is from a brand new Installation (you can see all the above mentioned pieces are missing here).

Before Restoring MSDB, make sure that your Agent Service is stopped for Exclusive access. Let me tell you something, Restoring MSDB and MODEL Databases is no different from restoring a simple User Database.

Syntax:
RESTORE DATABASE [msdb] FROM DISK = N’D:\msdb1.bak’
WITH FILE = 1, REPLACE, STATS = 10
GO
Results:
11 percent processed.
23 percent processed.
35 percent processed.
41 percent processed.
53 percent processed.
65 percent processed.
71 percent processed.
83 percent processed.
95 percent processed.
100 percent processed.
Processed 2136 pages for database ‘msdb’, file ‘MSDBData’ on file 1.
Processed 2 pages for database ‘msdb’, file ‘MSDBLog’ on file 1.
RESTORE DATABASE successfully processed 2138 pages in 1.407 seconds (11.871 MB/sec).

Now, let’s Start SQL Agent and look at the jobs.

Ha ha…our Jobs are Back. That’s all we need to do for Restoring MSDB!!!

Let’s Restore Master Database now.(Go and grab your coffee!)
First Step you’ve to remember to restore Master Database is you’ve to start your SQL Server in Single User Mode.
How to start SQL in Single User Mode?
Go to SQL Configuration manager and add -m in startup parameters as shown below.

Points to Remember: Semi Colon is mandatory between switches and there should be no space in between as shown above.

When SQL is started in single-user mode, first stop SQL Server Agent. Otherwise, Agent might connect first and prevent you to connect from!! 1st Sysadmin connection will be allowed to be connected at this time and rest of the connections will be thrown an error as per below screenshot

Now, open your sqlcmd (Microsoft recommends us to use SQLCMD while restoring Master Database) and please see the syntax as shown below. FYI My SQL Instance name is PROD.

Now, go to your Config manager, your SQL Server will be in Stopped State.Not a problem at all! (Restore Master Database will shut Down the SQL Server Service).
Remove the –m switch added in startup parameters and start the SQL Server and Agent Services.
Now if you see the below snapshot, Server came up exactly as it was earlier in the first Screenshot with all the logins, Jobs and the Linked Server.Tadaaaaaaaaaaaa:) 🙂 🙂

Also, Once Master has been restored successfully, our Mirroring Database went into Synchronizing/and then

into Synchronized state after a while from Disconnected State.Hurrrray………!! This happened because all the Mirroring ENDPOINTS info was secured in our Master Database Backup which knows the Mirror Database Server/Database information to communicate with:)

That’s all Folks! Hope this post helps you. Happy Tuesday!

Database ‘testDB’ is in Transition. Try the Statement later. Error:952

Have you ever been in a situation where your database is stuck in “Transition” yelling at you saying..

Database ‘Transient_Test’ is in transition. Try the statement later. (Microsoft SQL Server, Error: 952)

A Junior DBA’s first thought would be “Heard about ONLINE and OFFLINE, what is this TRANSIENT Status?? I’m having this Issue for the first time…OMG! Did i lost my Database?” Basically he starts pressing “PANIC BUTTON” on his/her Keyboard! which we never should as a DBA.(Trust me Guys, you’ll pay the penalty if you PANIC. Our Role as a DBA is to turn the PANIC Situation to a pleasant situation, not to supply oxygen to fire ;p

So..what does it really mean? Simple and straight forward thought which should strike on your mind is,
your Database is transitioning from Online State to Offline State, but for some reason it got stuck in middle. So SQL is saying that it’s trying to go Offline but it’s not able to!

Let’s simulate this scenario:
Step 1:
I’ve created a test database to play with and let’s call it as “transient_test”. I’ve created a test table to insert few records in an explicit transaction.
Table Structure:

/****** Object: Table [dbo].[test] Script Date: 07/14/2011 21:51:43 ******/
CREATE TABLE [dbo].[test](
[id] [int] NOT NULL,
[name] [char](10) NOT NULL,
[add] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
Step 2:
Begin an explicit transaction as shown below in the screenshot

Step3:
In another Session,try to take the Database offline. It won’t go Offline and it will remain there sitting “in Progress” for ever as shown below, because of the Open Transaction.(We’ve to either commit/Rollback and Close that session to take database offline successfully).

Step 4: Now let’s try to access the Database from either Object Explorer or try to Query anything in that database. It’s screaming saying as shown below

Volaaaaaa….That’s it Guys to simulate this. Pretty Simple huh!!

Now let’s assume that’s you are not aware of what happened and you got a call from Application team for immediate assistance.
Pretty Simple to investigate…Just issue “select * from sys.dm_exec_requests” to see what exactly are running currently or just legacy SP_WHO2 and investigate further.
Note: I’m showing a very old school technique using INPUTBUFFER and Ad-HOC individual DMV’s since i know there is no activity going on this server since it’s my test lab. In a real environment i suggest you to make use of combination of DMV’s at a single stretch which does all the magic for you. Search for “performance tuning SQL Server 2005/2008 Whitepaper from Microsoft”. It has all kinds of DMV Queries which will help you out.

Ha Ha…now you can see “SREE\Sreekanth”(the Bad guy) is the who is the Root cause for all this mess. Take a hammer and walk to his desk(if he’s nearby to you) Bangggggg on his head ;p (Don’t do it!!).
Once I commit/Rollback and Terminate the session, Database went Offline immediately and you can bring DB Online back. Or you can just kill that Session which is trying to ALTER the Database without taking the database Offline.

Database Status – Auto Close

Should we really care about Auto Close turned ON on your Database?
Ummm……I would say YES. It does matter to me a lot If It is turned ON for my Production Database. (I’m not saying that I’m Okay with turning ON this Setting on my Non-Prod Databases).

Before wrapping up my day in the Office i don’t know for some reason, I thought of doing a Basic scan of Databases on couple of servers which our team is supporting before leaving. Basically I was looking for “Recovery Model”, “Auto Shrink”,”Page_verify_Option”,”Auto Update Stats”. Well, i found few to fix and interestingly I saw one of the Databases showing something similar to below Screenshot as it’s current status.

hmmm….What does it mean?? Well, It’s basically saying that “Auto Close” is turned ON and since all the Connections are dropped It’ll show you the status as- “Normal/AutoClosed/Shutdown“. Once any session is opened on this database, the “Autoclosed” from above screenshot will disappear! It’s time to poke my fellow DBA why this has been enabled on his Database.

FYI If you query system catalogs for status, it simply says ONLINE even with DATABASEPROPERTYEX. what you’ve to look for is “is_auto_close_on” in sys.databases as shown below

So…Coming to the Point..Why you should concern about this is….SQL Server will be allocating and consuming few resources each and every time a database has been initialized for the first time. So if Auto Close is turned ON, your database will be Closed when the last session is terminated. When a user issues a new query or a new request hitting this Database, SQL Server has to basically Startup your Database again which leads to consuming those few resources again and this process repeats each time your DB is Auto Closed and a new connection begins….Hope i made it clear.

If you look into error logs, you’ll be seeing few instances where it says something similar to below which is an obvious and unnecessary Overhead on your SQL Server.

I hope Microsoft will remove this feature from it’s future editions! I’m still seeing it on SQL Server Denali CTP3:(

Installing SQL Sever Denali

Am all excited to let you know that I’ve downloaded SQL Denali CTP3 today and I’m installing it on my test laptop. I would like to share it with the community:)
PS this is a Standalone installation.

Workstation Config:

System Type: x64-based PC
OS Name: Microsoft Windows 7 Home Premium
Version: 6.1.7601 Service Pack 1 Build 7601
Installed Physical Memory (RAM): 4.00 GB
BIOS Version/Date: Dell Inc. A08, 11/7/2009

Okay! Let’s start Installing SQL Server. Insert your DVD(ISO) or just double click setup.exe file which you’ve downloaded. You will be welcomed with the below screen.

Select Installation Tab on your Left side and click on new SQL Server Standalone Installation as shown below.

It does a basic validation and you should pass all the tests as shown below. If any of the Requirements are not met, it will let you know what action is needed to proceed further.

Choose Evaluation Edition as shown below for testing all the Enterprise Features and Read/Accept the License terms.(As mentioned in another blog post under the same label it’s a 180 Days eval version).

After accepting License terms and Next, you are shown with the below progress screen of SQL Server Installation.

Now as you can see it has done a detailed rule check. You can see it also checked for SQL Server 2008

BIDS Installation. FYI Denali CTP1 had lot of issues with BIDS when the machine already had another Instance of SQL Server 2008. It’s good to see Microsoft included this test before proceeding further:)

Note: BIDS is a shared feature on a given Server unlike Database Engine.

Next screen is interesting as shown below – It has an option to Install All features with Defaults (Ideally, Which you don’t go for this on your Production Servers). For more realistic feel I’ve chosen Feature Installation.

Time to choose your components and features as shown below. My machine already has 2 instances of Database Engine and i believe one SSRS too…So I don’t want to hurt it any more 🙂

I’m choosing my Instance configuration as follows:

Next, time to choose your Service accounts. These will be domain accounts which your Windows Server Admin will provide you with the Password. You can leave it to default for test installations or change it accordingly. I’ve chosen my own accounts which i created for this installation purpose as shown below.

Now it’s time to choose your Authentication mode for your Denali Instance. I’ve chosen Mixed mode(this can be changed very easily even after installation). This is the place where you’ve to provide all your MDF/NDF and LDF file locations in the “Data Directories” tab. You’ve to choose atleast one account as a Sysadmin on this SQL Server. I would recommend to add Current User and SQL Server Service Accounts here as shown below.

It will do a final Config Rule check and creates a Config file depending on the features you’ve selected as shown in the below two screen shots.

It took me around 15 minutes to finish the installation and got the below screen saying “COMPLETED”:). Am all set to Play with Denali Database Engine Now.( Except HADR, which requires DENALI to be installed on a Windows Cluster)