Why I dream about attending SQLSkills Immersion Events!!!


Do you ever make faces like this?

I often end up doing this after reading the articles or listening to some of the webcasts by Paul, Jonathan, Kimberly, Glenn, Joe and Bob.

This expression says “Opps, Did I just assumed something!!!”

There are multiple instances where the articles, webcasts delivered by the SQLSkills experts helped me to understand a concept very clearly and erased my assumptions.

Now all I want is learn directly from the Masters. Talk to them, interact with them, learn lot of new things from them, and at the end of the day be a better and happier person like this always.

So what is this SQLSkills Immersion Event all about, and why am I so excited about it ?

Immersion Events are set of deep dive training sessions offered by some of the very best SQL Server minds on Planet Earth.Yes, I mean it!

Their experience is so vast that they will talk about their real time experiences with lot of great demos.

These events will really help me to learn about the internals of SQL Server with great precision/clarity and this obviously will help me to do my job better,and climb the ladder of success.

A detailed explanation of the modules covered under each event is given here . A quick check on the modules will help you understand why I am this excited.

If I am lucky enough to attend this year’s Immersion Event then there will be some questions (funny, of course) from me for the SQLSkills members. Some of them are -

To Paul – How many lines of code where there in DBCC CHECKDB!

To Jonathan – Is there an XEvent which you have never talked or blogged about!

To Kimberly – How much time it took to write this whitepaper, just curious!

To Glenn – Will you please come along with me to frys and help me select the Best Processor, Memory and SSD to run SQL 2012!

I am really looking forward to meet and shake hands with some of the very best, very helpful members of SQLSkills.I greatly respect them, and to learn directly from them is my  ”BIG DREAM ” !!!

Thanks for reading.

MS_DataCollectorInternalUser – A user without a login


Yesterday there was an interesting question about SQL user MS_DataCollectorInternalUser.The question was something like -

” ms_datacollectorinternaluser is showing up as a login less user.  Where did this come from? Can I remove? “

This question gave me the idea to write a blog post about -

User without a login

My initial response to the question was – No, as it’s related to Data Collector you should not remove it.

I was interested to check more about this user. I haven’t heard about this user before, however I was very sure that it’s related to Data Collector and deleting the same will be a bad idea.

I checked more to find that the user belongs to MSDB database. I knew that we can create a user without a login, however this was the first time I was observing a user without a login for a system database.

Let’s look what is a user without a login -

Starting SQL 2005 we have the ability to create users without logins.This feature was added to replace application roles.

One of a best explanation for login less users was once quoted by Michael Hotek(B/T) in one of the forums -

” When you create a user without a login, there is no path that allows a login to be mapped to that particular user in the database.  The only way to “utilize” this particular user in the database is through impersonation.  Again this is BY DESIGN.  Login less users were added in SQL Server 2005 as a replacement to application roles.  The main reasons behind this were that you did not need to embed a password into an application and you also did not suddenly have multiple people coming into your SQL Server all with the same “login”.  By utilizing login less users, the user’s of an application login to the SQL Server using THEIR login credentials (NOT a generic login), so that you can audit activity to a given login.  Once they change context to the application database, you can issue an EXECUTE AS command to change their user context thereby giving them the authority they need within the application.”

Let’s try to create a user Test_User without a login for database

--Create a user without login  
USE [AdventureWorks]  CREATE USER test_user without login; 
go 

We will now grant db_reader access to Test_User for the database Adventureworks

--Grant db_reader access to the user without a login 
USE [AdventureWorks] 
go 
EXEC Sp_addrolemember 
  N'db_datareader', 
  N'Test_User' 
go

We will now take a login Sam which doesn’t have access to Adventureworks database at all.User Sam is trying to access a table within the Adventureworks database 

--Select statement by the user who doesn't have access to the database
USE [AdventureWorks] 
SELECT * 
FROM   humanresources.employee 
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'Employee', database 'AdventureWorks', 
schema 'HumanResources'.

We knew that this error is obvious,and now we will impersonate user Test_User who already has access to the Adventureworks database to login Sam

This should help Sam to read the database tables isnt? Lets see

--Impersonate a user to a login
GRANT IMPERSONATE ON User::[Test_User] TO [Sam]

Once this is successful Sam should be able to Execute As user Test_User to read the tables.

--Read the database using Execute AS   
EXECUTE AS User='Test_User'
USE [AdventureWorks]
SELECT * FROM HumanResources.Employee
 

Now as we have seen what is a login without a user and how its useful from application perspective,lets look back what permissions MS_DataCollectorInternalUser is having.

This user without a login is a member of multiple databases roles within the MSDB database.


Deleting this user is not at all allowed[Deleting,modifying anything within system database is not a good idea for that matter] and it can even corrupt a Data Collector setup.

Members of the database role dc_admin can administer and use the data collector and members of the database role db_ssisoperator can administer and use SSIS. Members of SQLAgentUserRole have permissions on  local jobs and job schedules that they own.

Conclusion

Users without a login can help replacing application roles and it can be quite useful for application perspective goals.

We also reviewed the permissions for MS_DataCollectorInternalUser and came to a conclusion that altering anything would cause corruption for the Data Collector setup.

Thanks for reading.

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure


This post is in continuation with my earlier posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

In this post we will check out how to design a SQL Database which is on Windows Azure.

Once the database is created we have the Manage button to start working on administration and design of the database (As mentioned in the earlier posts)

Clicking on Manage button will route us to another page where in we will have to enter the credentials(Set during database creation)

Once the credentials are validated we will have a page where in we can choose the design option.

Let’s start building a table for the database MySQLDatabase which we have already created earlier.

We have the option called New table which will help us to start designing the tables and the columns.

We can specify the table name, start building the columns, set Primary key, set Default values, specify Is Identity, Is Required etc.

Once the required details have been filled in we have the option to Save the structure.

We have the option to create and manage the indexes too. The Indexes and Keys section will display the existing indexes and we have the option to create new ones too.

Creating an index is also pretty easy and we have some advanced options like Auto Update Stats and Rebuild Index Online.

Once the database and table is ready, we will need some data to do some DML operation. We can insert some data via the Data section.

The portal is pretty smart as it does validation the same time you are entering the data.

Conclusion


Designing the tables,indexes,relations etc are really easy via the portal and we saw some real time validations too.

I will write about some DML operations during the coming days and there some exciting stuff coming up too.Please stay tuned.

Thanks for reading.

Page restore – Smart utility within SSMS !


Page level restore is not something new for us, however page restore via SSMS (GUI) is something new, and it’s a feature available for SQL Server 2012.

Page level restores is a great flexible option to restore a particular page from the backup in the event of a corruption been identified for one or more pages.

Would you really like to restore a 50GB backup set when there is a requirement to replace a single corrupted page? No, you won’t and this is where page restore comes in.

You can find your corrupted pages and replace it with a restore.

Till SQL 2012 page level restore was possible via T-SQL code, and SQL 2012 provides you this option directly from the UI.

Let’s do a quick demo to understand this feature better.

Demo

1. For the purpose of this demo I will be using a database called Test_Page_Restore which is an exact replica of Adventureworks2012 database (Available in codeplex)

2. I am interested in corrupting an index page for Table HumanResources.Employee.

Let’s find an Index page for this table.

DBCC ind('Test_Page_Restore', 'HumanResources.Employee', 1)

Wait!!! Before I do some serious corruptions, let me take a quick backup of the database to the disk.

I will be taking a Full Backup directly.

BACKUP DATABASE [Test_Page_Restore] TO DISK= 
'C:\Test_Backups\Test_Page_Restore_BEFCorruption.bak' WITH stats=10

3. As I have a backup now,I can afford to do some real interesting corruptions !

Note - A backup set doesn’t mean that you are good and you have a sound disaster recovery plan. A backup is valid if and only if it can be restored. As I am doing a demo in a controlled environment I am not worried about backup validity.

I picked page ID 985 and will be corrupting it. I will also need to find the offset of the page for corrupting it and in my case it will be 985*8192(No of bytes for the page)  = 8069120

I am using hex-editor called XVI32 to corrupt the pages, and I have learned this technique from Kendra Little(B/T)

Note –  You should never ever try these steps in a production environment. It should be done only in a controlled LAB environment.

Before opening the data file using hex-editor I will need to take the database offline.

ALTER DATABASE [Test_Page_Restore] SET offline

Once the database is offline I can open the data file using the hex-editor. Using the above mentioned offset, I can go to the exact page and corrupt it.


I went to the exact page and corrupted that page by entering some letters. Once done, I saved the file directly from hex-editor and closed it.

4. Technically speaking now I have wonderfully corrupted page ID 985.

I can proceed and take the database Online Now.

ALTER DATABASE [Test_Page_Restore]  SET online

5. I am pretty sure that if I do a Select * from the table, I will get I/O errors.

Let’s test that out now.

SELECT TOP 1000 * 
FROM   [Test_Page_Restore].[HumanResources].[employee]

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: 
incorrect checksum (expected: 0x77d4e0e1; actual: 0x5cd4e0e1). 
It occurred during a read of page (1:985) in database ID 5 at offset 0x000000007b2000 
in file 'C:\Program Files\Microsoft SQL Server\
MSSQL11.SQL2012A\MSSQL\DATA\Test_Page_Restore.mdf'. 

The error message clearly says that I have a corrupted page and it matched the one which I corrupted using hex-editor.

Yay!!!

6. As mentioned earlier, starting SQL 2012 we can do a corrupt page restore directly from SSMS.For this I will need to right click the database and choose Restore option and then Page.

7. As soon as I select this option I will get the new restore window.

If you carefully look at the Pages section, it already did a DBCC CHECKDB and pulled the corrupted page from the database.

That’s one real cool option to have.

It exactly matched the page which I corrupted earlier.

The wizard will also let you know from which backup set you can restore the corrupted page back. In my case, I had already taken a backup earlier and it picked it up automatically.

8. I did a restore of the page using the wizard to bring back the corrupted page.

9. As the restore is completed. I checked the data to see if everything is fine.

SELECT TOP 1000 * 
FROM   [Test_Page_Restore].[HumanResources].[employee] 

Great we are back in action yet again.

I would like to thank Paul Randal(B/T) and Kendra Little(B/T) here as I learned to work with Pages/Corruption/Repairs by attending their sessions and reading their articles. They are just awesome.

 Conclusion

SQL Server 2012 has multiple enhancements and GUI support for Page restores are just one of them.

Thanks for reading.

Is there a way to find Historical Recovery Model changes on a database? Yes,you are lucky !


Today there was an interesting question posted on twitter, and that made me think about ways on how we can play around with SQL Server features/options to find solutions for tricky questions and requirements.

There are multiple ways to find this info, and the first and foremost thing which came on my mind was to read the error logs.

1. We can use sp_readerorrlog procedure to pull the details easily, however this details will going off when the error logs are recycled.

Sp_readerrorlog 0, 1, 'recovery'

The above method is great when you dont need huge amount of historical data and you are looking only for a recent specific time frame.

2. The second option is to use the default trace to find this info, however this is very tough as the recovery model changes are tracked under event class 164 which is the one for object_changes.There is no specific info which says the recovery model was changed.

A good post which leverage default trace + sp_readerorrlog + sp_enumerrorlogs is written by Simon Sabin(B/T) is available here.

In case you are using SQL 2008/above and would like to track this from now on then you also can leverage PBM(Policy Based Management) for this purpose.

The idea about PBM didn’t came to my mind early on, however it eventually came and I decided to test it to see if we can track historical info on recovery model changes.

A policy was created for all the databases with evaluation mode was ‘On Schedule’

The schedule was set for every 1 minute to ensure that multiple recovery model changes are evaluated and not missed.

The condition was also created as

The condition@RecoveryModel = Full is just one value for creating a condition, so that we can evaluate the policy.

Initially the database for this test case Adventureworks2012 was running Full recovery model and I changed it to Simple, Full and then Bulk Logged.

This means that I changed recovery model 3 times and currently its running on Bulk logged recovery model.

If we want to evaluate the policy manually we can do that too.if we opt to do a manual evaluation then we will get the evaluation details which will clearly says that database Adventureworks2012 is having a different recovery model than what is mentioned in the policy.


Our policy evaluation is running in the back ground to ensure that manual intervention is not required and we don’t miss any recovery model changes which might happen frequently (Multiple changes between 1 min can be missed out)

Note - Running schedules for every 1 min is not really recommended and should be used only if there is a specific requirement.

The tracking of recovery model changes comes in under the View History option available under Policy Management.

The first policy deviation was when the database was set as Simple recovery model.If we click the hyperlink under Detail section we will get the details of actual value and expected value.

The second policy evaluation success message is when the recovery model was changed to Full.

The third policy deviation was when the database was set as Bulk logged recovery model.

This way we can track the recovery model changes and we can disable/delete this policy once the requirement is completed.

The total number of days to retain this history for all policies is available too.It would have been great if we could retain history per policy,however that option is not available at this moment.

Conclusion 

As a hard and fast rule I definitely won’t allow anyone to change the database recovery model option. I can definitely enforce a policy to restrict it too, however as the requirement of the user was to track the changes I mentioned couple of ways to deploy it.

I am interested to know if you have dealt any similar situation like this. Feel free to pass on your experience as comments.

Thanks for reading.

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure


Last week I had wrote about the all new windows Azure portal and started off creating the first SQL Database on Windows Azure. Please read that here.

Today we will see how we can manage the database which was created on Azure via the portal.

Managing SQL Databases

The windows azure portal gives you the flexibility to manage your SQL Databases. Once the database is created its available under the SQL Databases section and upon selecting the respective database on the right hand pane, Manage button will be visible.

The Manage option will route you to a page which will ask for your credentials to access the database. The same credentials were mentioned when we created the database initially.

After logging in, the first screen will route you directly to the administration page where you can see the summary of the SQL Database and some pretty neat Query performance results.

One of the good things I liked for the query performance result set window is you can actually filter the results using ASC or DESC order.

If you would like to further drill down to a specific query then you can do that too! That’s pretty much good and everything out of box.

Query plans! Yes, you heard that right!

Query plans are also made available for each and every query which you would like to tune.

Conclusion

The portal gives you some really good options to manage your SQL Database,and during the coming days I will write about how we can start designing the Tables and other key objects with a SQL Database.

Thanks for reading and keep checking this space for more.

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure


The all new Windows Azure platform was announced yesterday by Scott Gu(CVP,Windows Azure Application Platform) and an online event was hosted here at http://www.meetwindowsazure.com/

You can watch the recorded keynote on demand here at http://www.meetwindowsazure.com/Conversations

There were lot of new features announced yesterday, and the whole event was packed with heavy and exciting demos.

You can refer the blog post written by ScottGu to get a feel of what Windows Azure is going to offer during the coming days.

Another good blog post written by Bob Kelly explains the new services and changes for the existing services can be read here

The new portal looks really cool. It’s all HTML 5 and metro style enabled. Let’s see what Paras Doshi(B/T) has to say about the portal here.

I decided to start playing with the portal to understand more about the offerings, and as always my first attention was towards SQL Database.

Windows Azure SQL Database is a relational database service offered on the Windows Azure platform.Its based on SQL Server 2012.

Here is my experience creating the first ever SQL Database on Azure -

Portal View

The interface is really neat with metro style layouts.

SQL Database Creation(Initial Steps)

There is a wizard to guide you to start creating the database, and there are two options Quick Create and Custom Create

I decided to go with Custom Create as that option will give me better control on what I am doing.

The wizard comes again with cool interface asking me to enter the Name, Edition, Max Size, Collation, Server details.

There are 2 edition selection options  WEB and BUSINESS.Web edition will allow max size of the database as 5 GB and Business edition allows max size of the database as 150 GB.

I am using the evaluation subscription, so I decided to choose the web edition with 1GB limit.

I also entered the New SQL Database Server option as this was the first time I am creating a database, and I had setup no server for this subscription.

The next option was to add a Login (Only SQL login option is available), password, confirmation and region where you want the server to be deployed.

Confirmation for SQL Database Availability

In a matter of seconds (3 or 5 !) the database was up and running on cloud.

Conclusion


The new Windows Azure platform is really powerful, flexible and friendly and over the next few days I will write more about my experience working with SQL Databases and much more.

Keep checking this space for more !

Thanks for reading.

Powershell 3.0 is here ! – Windows Management Framework 3.0 – RC


Couple of days back Microsoft released Windows Management Framework 3.0 Release Candidate which includes Power shell 3.0.

Download files and what’s new can be referred here.

One of the first changes I noticed with this release is feature rich ISE. The new interface looks really nice with commands add-on which will list commands for each and every module.

You can install WMF 3.0 for the below flavors of operating systems -

Windows 7 Service Pack 1 – Both 64bit and 32bit

Windows Server 2008 R2 SP1 – Only 64bit

Windows Server 2008 Service Pack 2 – Both 64bit and 32bit

I am running a Windows Server 2008 R2 SP1 box and the setup upgraded my existing power shell 2.0 environment to 3.0.

You can find Commands add-on view available on the right hand side of the ISE which is pretty useful.

IntelliSense is also turned ON for ISE, and it’s one of the best features I would always love to have as I am still learning power shell basics.

There is some customization available for the ISE under Tools section. The below screen shows customization for the scripting pane.

The Add-On tab in ISE will directly take you to the Microsoft tech net site which will provide the add-on files for the ISE.

Conclusion

Power shell 3.0 comes up with a real strong ISE which is really helpful for power shell beginners (Like me) and during the coming days I will leverage more and more options with in Power shell 3.0.

Happy scripting.

Thanks for reading.

Hiding a SQL Server Instance !


Today I accidently came across the option called ‘Hide Instance’ flag for a SQL Instance.

This property looked pretty useful for me in case I don’t want anyone to browse for my SQL Instances easily.

There are many ways by which you can browse all the SQL Instances. One way is when using SSMS to connect to an Instance.

A look up on Network Servers will show you all the servers within the same domain. In this case we will concentrate in instance SQL2012A which runs SQL Server 2012 edition.

If you set the Hide Instance option as Yes for this instance, then that will prevent the SQL Server Browser service from exposing this instance of the Database Engine to client computers.

Process 

You will need to recycle the Engine services after setting this flag as True.

Once the service is recycled this takes into effect and the instance SQL2012A will no longer will be available for browse.

Conclusion

I have never seen this flag being set as a part of security best practices; however I would really focus on this point and will check with few SQL experts if they have similar experience.

Thanks for reading.