SQL 2012RC0 Database(s) Properties from 2008R2 SSMS

This is one interesting thing which I’ve Observed recently connecting to SQL Server 2012 via SSMS 2008R2.

SSMS 2008R2 is not displaying Compatibility Levels of the 2012 databases! See below Screenshot for what I’m trying to explain.

See below screenshot for the same database properties from SSMS 2012.

As you can see, It is displaying Containment properties, additional File Stream properties…etc!  I think this is not an Issue with 2008R2 SSMS, but just something to be aware of!

CNOs/VCOs(Computer Objects) and few ways to protect them…!

If you already have experience working on Clustered Environments, you might already know about CNO(Cluster Name Object) and VCO(Virtual Computer Object). For Newbies, let me explain what CNO and VCO are in a line or two…

CNO: This is the Core piece of your Windows Cluster and acts as an identity of your Windows Cluster. This is a computer Object which will be created in your AD under Computer Node(under your Domain or OU, if you have any).  It will be same name as your Cluster.

VCO: Again, these are the Objects being created in AD under Computer Node depending on the Services and Applications which you are creating inside your Cluster. Yes, CNO is responsible for creating those VCO’s.  CNO’s should not be deleted or not even touched in terms of security by any means and by any person.  Services won’t come Online if CNO permissions are modified or CNO gets dropped accidentally, which is a potential threat for your cluster.

In order to Recover from deleted CNO situation, your Domain Admin should be involved and he/she needs to restore your Active Directory Objects which is not a simple task, especially in larger enterprises. Good News is Starting Windows Server 2008R2, we’ve something called Active Directory RecycleBin which is an awesome way to recover AD Objects. Hold on Guys….there’s a GOTCHA though!

Gotcha: AD Recycle Bin is not Enabled by Default. It has to be enabled within your Domain by your Domain Admin!

What if we communicate with our Domain/OU/Server Admins to enable some settings which basically prevents any accidental deletion of Computer Objects? It would be really nice if we could prevent that deletion action in first place right, instead of recovering after a disaster. So what can be done here? Windows Server 2008/2008R2 offers a really simple way to prevent these accidental operations(mostly Human Mistakes). There’s a small checkBox which we should enable to make this happen. Once enabled, It won’t let anyone to delete that Object.

Demo:

I’m on my Domain Controller and I’ve opened AD Users and Computers from Administrative Tools. You can see WINCLUST is my CNO.

Very Imp: Now You’ve to go to View and select Advanced Features, to be able to see/perform all the available options/operations we’ve. You can see below

Now, I’m trying to protect my CNO from accidental deletion. All I’ve to do is Right click on CNO and select properties and navigate to “Object” tab and check that tiny box as shown below:)

That’s it! Is it really hard? Nope. You can check with your Domain Admins to make sure that this is checked on all your CNO’s and VCO’s.

Note:

All the New OUs are automatically set to be protected.

Any New Users/Groups are not set to be automatically protected.

Any New Computers are not set to be automatically protected.

With the protection being enabled, now let’s see what happens if we try to delete that CNO manually from my AD.

Note: Don’t even think about doing this in your Company(In the First Place, we will not be having those level of privileges). I bet, you’ll be fired the very next moment!

I got this warning Message saying, Are you really Sure about what you are trying to do here?? See below Screenshot.

Let’s say…I’m one Stupid Guy and went ahead and clicked on Yes. The below is the screenshot of what I got.

Remember I logged onto this as a Domain Administrator, even then..Windows is saying, “Uhuhhhhh….No Idiot! I’m not letting you to perform this operation unless you uncheck that tiny box which we checked earlier” 😀

Isn’t it Something Awesome Guys? I really really encourage you to check with your Server Admins on this Option being enabled if you are responsible/accountable for some Mission critical Production SQL Server Clusters.

Hope this is useful info you learnt something new! Cheers!…

sp_configure and sys.Configurations

This would be a very small blogpost, Well, actually an informational Post, no Issues and no troubleshooting shown in this post!

We all, being a DBA will be running “sp_configure” every now and then on our SQL Instances to see what are the configured settings on the Instances. okay, okay….If you don’t have this Practice, make this a habit from right now. Document your Instance settings in your Run Book and compare with them each time you run this and you can analyze what/why/when/who changed accordingly.

Okay, By default if we run “sp_configure” on our Instance, we’ll be getting only 17 Rows(as of SQL Server 2012 RC0).

So are those the only configurable Settings we’ve in SQL Server? Nope!

Actually, you can get all the configurable options from querying sys.configurations, where it returns 69 Rows as shown below.

Is_Dynamic = 1 implies, no need of running RECONFIGURE Statement after changing the setting.

Is_Advanced = 1 implies, we should run RECONFIGURE Statement after changing a setting.

Now, for sp_configure to return all the 69 Options(rows) all we’ve to do is enable “show advanced options” and run “RECONFIGURE” as shown below.

Once, done issuing “SP_Configure” will return all the options available as you can see below.

Now, you can see 69 rows being returned. Cheers!

Windows Networking Tools for SQL DBA’s – PATHPING/TRACERT/PING

Hey Folks! Hope you all had fabulous holiday season 🙂

In this Post let’s see Three fabulous tools which Windows OS offers for testing your Network between your Servers. A typical example for DBA’s would be testing Network Latency between your Prod and DR Server while initial testing of your Mirroring configuration or even looking for latency/Response times when you are copying a huge Backup File from one Server to another Server etc.

Tool 1: PING

This is one of the most used command by any IT professional, irrespective of what he/she does. PING is a very Basic test to verify IP connectivity from one machine to another.

Eg: PING MachineName ; PING IPAddress; PING -T MachineName/IPAddress(T for Continuous ping).
For More Options and Info please visit http://technet.microsoft.com/en-us/library/bb490968.aspx

Tool2: TRACERT

This is another useful tool which traces the entire route(Includes all the Routers/NIC’s involved in your Path) from Source Machine to Destination machine.  Pretty useful tool.

Eg: TRACERT Machinename
For info on how to interpret those results, please visit http://technet.microsoft.com/en-us/library/cc940128.aspx

Tool3: PATHPING ( Awesome Tool to have in your ToolKit)

This is one of my all time Favorite tool which basically mixes both PING and TRACERT Results and will provide you with a very detailed/useful information.

Eg: PATHPING MachineName. In this Example I’m doing a Pathping from my Domain Controller to one of the server in my domain. See below on how it looks for the output.

Typically, you would see many Network Interfaces in between(Of Course right…!). Try “PATHPING GOOGLE.COM” from your machine and you can see what I mean. Or Just try PATHPING on one of your Servers in your company, you’ll see what I’m trying to say.

Tip: I typically make use of “>”  appended to my command, so that I can save the Output as a report in a text file and I can review later any time or you can simply forward that text file to your Server/Network Team to look into. Please see below for what I’m trying to explain(Basically, it’s something like “SPOOLING”  in Oracle if you are familiar with ORACLE SQLPLUS).

Now it created a Text file called “Output.txt” under my C Drive and the contents are as shown below..

Trust me Guys, PATHPING is one of the most Useful tools we have for free, which many of DBA’s are not aware of, or I should say most ignored Tool by many DBA’s!

Technet has fabulous info on how to interpret results and options available etc here: http://technet.microsoft.com/en-us/library/cc958876.aspx

Hope this Info helps!…