Author: sreekanth bandarla

What is IPv6? Why should we care about it?

In my leisure time I was going through few of the recent advancements which world is moving towards in Information Technology. I found IPv6 Interesting of all. Like me, if you are curious about knowing what IPv6 is and how it offers advantages over IPv4, this short animated video will help you.
I guess we are not far away moving all our Servers in our Data Centers to IPv6 from IPv4.

Video from Explania folks:

 

Also, an interesting and very informative Pic from Focus.

Image Source: http://www.focus.com/images/view/49504/

Hope this gives you all a good Kick Start exploring about IPv6.

DMVs with Database Compatibility Level 80??…

Let me give a brief background on why i would like to discuss regarding this interesting and most often ignored topic of Dynamic Management Views(DMVs) and their relation with Database Compatibility Mode.

Note: When i say a DMV, i actually mean DMO(Dynamic Management Object) which Includes both DMV’s and DMF’s(Dynamic Management Functions). To avoid the confusion, people generalized and started calling both DMV’s and DMF’s with a single term called DMV and i love it:)

Why am i sharing this today?? Well, we(our Team) recently started a Small Internal Project something called as “Performance Tuning Initiative”. As you all know, a DMV can help a DBA than any other free tool(Sometimes a paid tool too) out there in market!! So…we started to Pull all the Cool information with proper benchmarks and started doing trend analysis and monitoring using few fabulous DMVs which are available to all of us.
Note: We’ve few Databases with Compatibility Mode 80 because of Vendor Restrictions even running on SQL Server 2005 SP4. So…as you imagined by this time, today in the morning myself and one of the DBA’s in my team had a discussion regarding how DMV’s behave if run against a database with Compatibility Level 80. Interestingly, he was in a misconception – “Most of the DMV’s are of no use if the DB Compatibility Level is 80” and i was arguing exactly the other way.- “Most(Almost every) of the DMV’s will have no issues even the Compatibility level is 80”

Quick Demo:
I’ve a Database “AdventureWorksDW” set to Compatibility Level 80 as you can see below.


Let’s run few typical DMV’s which we’ll be using on a regular basis against this database.

In the above Screenshot you can see it had no issues with the “AdventureWorksDW” Database.

Let’s see what happens if we run our sys.dm_db_index_physical_stats against this Database.

Ummm…it is screaming that there’s a syntax error. Actually it’s not. There’s a Simple trick to crack this thing.

Crack – Execute the DMV in context of any other database which has 90/100 compatibility mode. In my case I’m running on SQL Server 2008R2 and my master database has 100 Compatibility level.
Syntax 1 where we had issues:

USE AdventureWorksDW
GO
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO

Syntax 2 where we are having no issues:

use master
GO
SELECT * 
FROM sys.dm_db_index_physical_stats(
DB_ID(‘AdventureWorksDW’)
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO
The results are as shown below in the screenshot.





















If you observe the syntax carefully, I’ve just changed the database context of execution and I’m passing my Database which is in Compatibility level 80 to the DB_ID() Function.
What’s the Reason behind this??
Very Very Important……
Because the physical structure(Version) of the database is not going to change when you change compatibility level.In other words you are not downgrading a Database at it’s underlying physical architectural level by just reducing the compatibility level to an older version. You are changing only few Queries execution behaviors! That’s all basically what you are achieving here by reducing compatibility level to 80 in this case. Once the Database is on SQL Server 2005 by any means(Create from Scratch/Restored/Detached-attached), that’s it…the Database Version is set to 611/612.
Note: 
SQL Server Denali Database Version – 700
SQL Server 2008R2 Database Version – 661
SQL Server 2008 Database Version – 655
SQL Server 2005 Database Version – 611/612
SQL Server 2000 Database Version – 539.

That’s the reason why you can’t restore a Database from SQL Server 2005(2008) onto SQL Server 2000(2005) even though you changed the compatibility level to 80(90). SQL Server won’t allow to downgrade any database version. No exceptions here!! You’ve to choose the difficult path of moving object by object and data using SSIS!!

Let’s do a Quick Demo what I’m talking about:
SQL Server Database stores the Database version information in it’s Boot Page( Page 9 of 1st File in the database). I’ve to turn on traceflag 3604 to dump the boot page to our results pane. For More Information on this please see my previous blog post http://sql-buzz.blogspot.com/2011/06/checkdb-in-errorlogs-on-startup.html




In the above screenshot, you can see the database has 80 Compatibility level but the DB version is 661(SQL Server 2008R2).

Hope this information is useful….!

Prevent logging all the Backup(Successful) Events in your Error Log

Have you ever been in a situation where you were trying to analyze a particular error details from your error log and you are annoyed by whole bunch of “Database Backed up/Log was Backed up bla bla…” messages?? Let’s say your Instance has 20+ databases and most of them are Log shipped(assume every 10 Mins log backups) to it’s DR Server (This is a very common Scenario). If you see anytime in your error log, you’ll end up with frustration digging into what exactly you are looking for. Because SQL Server has a Bad habit of logging in each and every detail(Success or Failure) of backup Operation(s) by Default. You’ve to define all kinds of crazy filters in your logs to exclude the junk and look what exactly you are looking for. Basically, You’ll be seeing Error logs something very similar to what you are seeing in the below Screenshot.

So…Is there anyway where you can get rid of all these Successful Backup events being logged from your SQL Server Error Log?? The Answer is YES 🙂
Microsoft has introduced a TraceFlag 3226 which you can enable globally, which eliminates all these Junk(In most of the Cases) getting logged into error log. Let’s do a Quick Demo.

Let’s recycle the Error Log, Just to make sure that we’ve a clean log – easy to Demo. It should come up as shown below.

Now, let’s start the actual testing.
I’m taking a Full Database Backup and an immediate Log Backup. Now let’s review our Error Log in the below Screenshot.

It logged Both the Full Backup and the Log Backup Operations.
Let’s turn ON TraceFlag 3226 now and let’s take couple of Backups and turn OFF Trace Flag and again take a backup and see what get’s logged in our Error Log for all the above operations. Just to convince you folks, I’m again Turning OFF Traceflag again and show you the Error Log all at one glance(See Below Screenshot)!!

If you carefully observe the Error Log at the left the tst5 and tst6 backup events are not logged and once we turned off the trace flag tst7 has been succesfully logged.

By this time you should be thinking what about the Failed Backup Events, We need them to be logged all the time in our Error Log. Absolutely “Yes“!!! All the Failed Backup Events are Successfully logged even with this Trace Flag Turned ON. You can see that in the below Screenshot.

I’m really impressed with this and suggest you folks; do Consider testing this trace flag on your Servers!

Custom Server Roles in SQL Server Denali :)

Custom Server Roles! Yes, this is a brand new enhancement Microsoft has provided in SQL Denali.
Upto SQL Server 2008R2, We only had flexibility for creating Custom Database Roles. But the Server roles were fixed, which comes along with your installation.

So, Let’s peak on how to create a custom Server Role and assign Server Scoped Permissions in Denali!!
Pretty Simple Using GUI:
Scenario: Let’s assume your team hired a Level2 DBA who needs supervision for few weeks/months. You don’t want to grant him sysadmin privileges where he can do what ever he wants. You really don’t want a Jr DBA connecting to your SQL Server and stop/Start/Restart SQL Services. So let’s assume your plan is just to limit his permissions to Create/Alter any Database, Alter Trace – for Running Traces, Alter Logins/Connections/Linked Servers. What can be done with less pain is Just create a Server role with desired permissions and add him/her as its member.( you can use your own imagination for Server Scope Permissions, for example…if you want him/her to control DB Mirroring, consider granting perms on Mirroring ENDPOINTS and so on….). You can Just add all your newly joined DBA’s to this Role which is Just a matter if Single Button click 🙂
In my Example, I’m creating a Server Role called as “Lev2DBA” and choosing Server as a SECURABLE and granting permissions as per my requirement.
Snap1:



Snap 2:

Now, go to Members Tab and Add that Level2 DBA(login “test” in my case) to this Role as shown below.





Note: You can also directly add this to any existing Fixed Server Role directly from “Memberships” tab as shown below.

If you’ve  observed thoroughly,by this time you might’ve noticed that in the above Screenshot there is no “SYSADMIN” role. I believe MSFT is not allowing us to add these members to SYSADMIN role. ( Not sure, why they are allowing ServerAdmin in that case!!!)

It’s also pretty Simple from T-SQL: You can follow the below Syntax for achieving the same.

USE [master]
GO
CREATE SERVER ROLE [Lev2DBA] AUTHORIZATION [sa]
GO
ALTER SERVER ROLE [Lev2DBA] ADD MEMBER [test]
GO
GRANT ALTER ANY CONNECTION TO [Lev2DBA]
GO
GRANT ALTER ANY DATABASE TO [Lev2DBA]
GO
GRANT ALTER ANY LINKED SERVER TO [Lev2DBA]
GO
GRANT ALTER ANY LOGIN TO [Lev2DBA]
GO
GRANT ALTER TRACE TO [Lev2DBA]
GO
GRANT CREATE ANY DATABASE TO [Lev2DBA]
GO

That’s it Guys! Very Very Simple huh?….
Happy Testing Denali!