SQL Denali CTP3 – MSSQLTREK

Denali Databases Status in Object Explorer

Howdyyy…Hope all is well folks! It’s been a long time since I wrote something….Was quite busy with some crazy stuff going around!! Anyways this would be a another small/Quick Blog post regarding a small enhancement msft offered in DENALI SSMS.

Okay, let’s jump into the content! I’ve created few SUSPECT databases on one of my SQL Instances and let me show you how DENALI CTP3 SSMS is showing in it’s Object Explorer and how SQL Server 20008R2 SSMS shows in it’s Object Explorer.

SQL Server 2008R2 SSMS:

SQL Server DENALI CTP3 SSMS:

As you can see, in DENALI the Database(s) are appended with (Recovery Pending) status which is more self explanatory where as in 2008R2 SSMS, the Databases are shown as nothing! Even though this is not great enhancement, it’s good to see this in our OE 🙂

SQL Server Default Backup(s) Location- Using DENALI SSMS/2008R2

Have you ever been in a situation thinking, why MSFT did not included an option to change default backups location in SSMS? Well, I was in few cases!! The good news is they’ve given that Option in DENALI SSMS….Yes Guys you heard it right!! So no more digging into Scary Registry values to change those settings 🙂 In this post let’s see how this can be achieved now running on 2008R2(This applies to SQL 2005/2008 and 2008R2) and also..let’s see how to tweak this option even on earlier Versions of your SQL Server using DENALI SSMS without getting our hands dirty touching Registry values 😉

If you recall correctly, while installing SQL Server It’ll ask for all the Data, Log, TempDB and Backup locations. Changing the TempDB files location is pretty simple, can be done just by Alter Database/Modify File and restarting your SQL Instance. Changing Default Database Files(mdf and ldf) files location is also pretty much simple, Just Right click on your Instance in SSMS and select Properties and navigate to Database Settings as shown below…

But……….where can i look and change the default Default Backup Location??? By navigating to the below registry entry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.PROD\MSSQLServer

In this case I’m looking at my “PROD” Instance. Similarly If i want to verify/change my “DR” Instance backup location, I can simply go to MSSQL10_50.DR and do the same. This might change accordingly for your SQL Instance.

That was little bit annoying right??? Well, it really depends on how comfortable are you with dealing with registries!! Okay! Now let’s see how this can be achieved using DENALI SSMS without pain.

Verifying/Changing Default Backup Location in DENALI:

Open your DENALI SSMS and connect to your DENALI Instance and RC and Properties at Instance level and navigate to Database Options, Interestingly You can see an added option below your Database Files location(s) as shown below

Let’s change the Backup location from SSMS and let’s also see how the registries are being changed behind the scenes.

Registry before Changing Backup Location:

Changed Backup Location from DENALI SSMS:

Registry After changing Backup(s) Location:

Awesome!!!!!!!!!!!!! Interestingly, you can also just connect to your SQL Server 2008R2 Instance from DENALI SSMS and change your default backup Location for your SQL Server 2008R2. Huhuuuuuuuuuu.

Let’s see how it works. I’ve connected to my PROD Instance which is SQL Server 2008R2 version from DENALI SSMS as you can see below and i changed the default location to D:\Backs.

Nowww……Let’s see how our Registry Value looks like for our 2008R2 PROD Instance.

Tadaaaaaaaaaaaaaaaaaaaa!!!!!! 🙂 🙂 🙂
For me this is one of the Most Interesting enhancements with DENALI SSMS. Can’t express how impressed am i with this little thing:)

Bottom Line : Using DENALI SSMS you can change the Default Backup Location for your SQL Server DENALI Instance as well as SQL Server 2008R2 Instance without digging into Registry Values.

SQL Server Configuration Manager/Startup Parameters 2008R2 vs DENALI

Things we should be Aware of in SQL Server DENALI Configuration Manager, A Small simple change which might make a huge difference!

Before proceeding any further, let me make it very clear….for any changes to me made on any of the SQL Server Services you’ve to make use of SQL Server Configuration Manager, not our typical windows services from administrative tools.

I’ve seen in the past(still seeing few folks doing the same even now!!) where people going to “RUN” and entering “services.msc” for changing SQL Server Service accounts, Start Up type etc….Hope you are not doing the same!

Config Manager in SQL Server 2008R2:

Okay, Let’s jump into Config Manager in SQL Server 2008R2, when you choose properties of SQL Server Engine we’ll be getting what is shown in the screenshot below.

This is the place where we’ve to change the Service accounts and pwd’s. You can change the Start Mode(Automatic/Manual/Disabled) in the Service Tab. For adding any start up parameter, you’ve to go to your advanced Tab as shown below.

Configuration Manager in DENALI:

See the screenshot below and if you see carefully, you’ll notice a new tab for startup parameters!

Basically, DENALI has 2 new tabs, one for Brand new fabulous feature “AlwaysOn HA” and the other for “Starup Parameters”. Let’s see what’s present in my AlwaysON tab…

Well, I’ve Installed this Instance of DENALI on my WINDOWS 7 x64 Client OS which is a stand alone, hence unfortunately, I can’t show you much about this setting here 😦 (I’m planning to Install DENALI on my LAB Cluster pretty soon to explore and learn this brand new HADR feature of DENALI…yayyyyyy)

Let’s move on to Starup Parameters Tab, You can see the brand new interface as shown below.

IMHO, this makes all of our(DBA’s) lives much easy dealing with those annoying semi-colons “;” and Spaces where we should be very very cautious upto SQL Server 2008R2. A single space will change the entire behavior of your startup parameter(s).

Let me show you how we can add a trace flag as a starup parameter in DENALI. Let’s try to enable Trace Flag 3226 as a start up parameter to disable all the successful backup events being logged in our error logs.( See here if you want to know how you can enable a trace flag globally instead of making it a startup parameter). All you need to do is Just typing “-T 3226” and click Add ->Apply->Ok (Service restart is required to reflect the changes) as shown in the below screenshot.

If you want to Update any parameter, all you need to do is Just click on the parameter which you want to change and make changes and click on Update Button. ( The Add button will change to Update button once you select any parameter as shown below).

In the same manner you can remove any Startup parameters which you’ve added.

This greatly reduces pain in our Butt dealing with Spaces and Semicolons( I’m sure that almost everybody agree with me in this matter, especially who’ve already faced an issue and wasted time(like me when i was a Novice in SQL) figuring out what’s wrong with the parameter provided and banging head to your desk, OMG! why this SQL Server is not starting up as expected 😦 )

Hope this post helps!

Restoring a Database-SQL Server DENALI SSMS

Today i was playing with SSMS in Denali and found few Very Very Interesting enhancements with Restore Task. Let me show you what i’m saying….

Let’s play with Adventureworks Database for demo purpose. FYI I’ve a folder on my D$ where i placed all my Full, Diff and T-Log backups for this database.
First, let’s see what SQL Server 2008R2SP1 SSMS offers us when we try to do a point in restore on the same instance.
In SSMS go to TASKS and Restore and Database as shown below.

By default it chooses from Database where it automatically picks the Backups(Full/Diff and Log) accordingly what it needs to perform a Restore to most recent possible point in time as shown below.

Perfect, this is awesome….But, what if we choose from device, where we’ve to select our Backup files manually..Let’s see what 2008R2SP1 offers to us.

Ummm…Basically your are missing an option where you can select an entire folder and It automatically picks the required individual backup files as per the LSN’s…..So, we’ve to pick all the individual backup files required manually.

Let’s see Point In Time Restores(Recovery) for SQL 2008R2 SP1:
I’m choosing from Database for ease in demo purposes. I’ve chosen 11.00 AM in the first scenario, and it basically chooses the required as shown below.

This is So Nice…..But again if you’ve to choose from device, you’ve to point to individual Back up files manually(Which is Okay Most of the times, but irritating in few cases..It all depends).

Now, Let’s see what DENALI SSMS can do for us in Restore operations
At the very first glance…..You can see, Now we can do PAGE Restores using SSMS as shown below. Upto 2008R2 we’ve to use T-SQL Commands to Restore a single page.

Note: I was creating a Maintenance Plan for Backups and I found one interesting enhancement where we can select “which account” do you want to run this Plan under( As per the below Image).

In SQL Server 2008R2, we didn’t had this option. We’ve to go to Agent Jobs and change the Job owner and Execution Context. One more interesting thing to note.

Okay..Let’s actually do the Point in time restore from SSMS in Denali. I RC’d on the database which i want to perform a Restore ->Tasks restore -> Database. Let’s see what exactly DENALI is doing for us in this process.

See the highlighted areas in the above screenshot. Greatest feature of all is…..Now DENALI is smart enough to take a Tail Log Backup automatically, which should be applied as a the very last backup in our restore sequence. Huhuuuuuuuuuuuu…..I love it..Simply Superb!!We also have a Button at the bottom right to verify our Backup Media:)

Also, observe in the above screenshot I’ve chosen the source as Database. Let’s see what it has if we choose Device.

Now we can choose a folder which has all our Backup files:) Once you click OK..actually it is automatically choosing only the required backup files and ignoring the rest, which greatly saves our time.

Let’s see what Timeline option offers to us:

Oh My God…I can’t express how impressed I’m with this little graphic which is pretty self explanatory in all terms. Kudos to Microsoft!! Great Job guys. Lot better than few Third Party tools our there which are available to us!

Try Changing the timelines and you can see the above graphic changes accordingly!

Okay! Now let’s go to Files Tab and see how it appears.( As of now, in SQL Server 2008R2 SSMS there will be no FILES tab when you are performing a Restore Operation.(Actually we’ve to define everything in OPTIONS tab)

SSMS DENALI SSMS SQL 2008R2

So, if we want to change the Physical Location of Files or change logical file names, they offered us a separate tab, basically saying that all the file operations are done under FILE tab, which makes more sense to me!

Let’s go Options tab and see what it has for us.

In the Tail log backup Section it’s giving us where it is storing our tail log backup file. You can change it by clicking ellipses button.

Real Cool thing – It has now an option to close all the existing connections before actual restore starts…yeyyyyyy!! Also, an option to Prompt you before restoring each backup file(In this case, we’ve multiple files).

This is no less than any Third Party tool out there currently in the market!!(I’ve to agree that there’s no encryption offered while backing up and no network resilience features, But still we got all the cool enhancements for Freeee…..Anyways we’ve compression feature from SQL Server 2008). Over all I’m very impressed and glad to share this with our SQL DBA community.

Happy testing Denali:)

SQL DENALI SSMS – Few Interesting Facts/improvements.

This DENALI version of SQL Server is really driving me crazy with all the cool features/enhancements and even few funny things which you can do using your Management Studio!! Am getting soooo addicted to it:)

Let me ask you something. How many times do you open your Browser while you are working on something?Are you a Facebook addict? You know what….you can browse internet in your Denali SSMS. Yes..!you heard it right…..You can open a new tab in your SSMS and access your Facebook while executing a Script against your Production Database in another Tab…..(Hope you will never do this ;p). Okay let me show you what i’m saying.

Open your DENALI SSMS and hit Ctrl+Alt+R or follow the below Screenshot.

By Default you’ll be welcomed with SQL Home page –
http://www.microsoft.com/sqlserver/en/us/default.aspx

Okay! Let’s try accessing Facebook (just for fun).

I was able to successfully login to my account and also watched few videos..huhuuuuuuuuuuu…..
Anyways, i think i’m not going to use my SSMS for browsing and I hope you won’t too!

Now let’s see if we’ve anything to tweak this settings in our Tools->OPTIONS.

You can change your Homepage and your default search page. Also, see at the bottom for internet explorer options!! I can’t believe my own eyes!

Also, you can see lot more options in DENALI SSMS to tweak and make more flexible as shown in the above image. You can see how OPTIONS look like in SQL Server 2008R2 SSMS below.
SSMS DENALI

SSMS SQL Server 2008R2

Zooming Query Pane:

One Cool feature(zoom), really helpful when you are giving a DEMO of something or Mentoring others in a live meeting session or net meeting or by any other means. Once you hit the New Query button on your Menu Bar, if you look at the Bottom Left corner on your Query Pane you’ll see something to Zoom your screen. See yourself in the image below what i’m talking about.I guess there are lot more Cool enhancements and features available for us. Go ahead and explore all the Options from your TOOLS Menu and get familiarize yourself with DENALI SSMS.
Hope this Post helps! Happy testing Denali 🙂