Out-GridView cmdlet – Interactive table


One of the very interesting cmdlet which I came across today was Out-GridView.This is really powerful and it sends across powershell script output to an interactive table.

I prefer this option as I can slice and dice the data if required.

Out-Gridview requires Microsoft .Netframework 3.5 with SP1.This applies for Powershell 2.0.

Below is the Out-GridView of one of the powershell script which pulls Backup information of SQL Server databases.

 

This interactive table gives you an option to Add a criteria,and using that criteria you can filter the results.

Example – We are adding the criteria ‘Database Name’ and entering starting characters of Adventure works will filter out the data for you.

 

 

This interactive table will be really useful when you are dealing with a lot of data and you need to filer out the same.Exporting the data to a spreadsheet is one option,however for quick slice and dice Out-GridView is the cmdlet for you.

Thanks for reading.

Powershell 2.0 for Windows XP – How can I get this up and running


Recently I had to work with Powershell on a windows XP machine and I was wondering how to to set this up.I had never worked with Powershell on XP and was curious on how to configure it.

I was able to find a KB article for Windows Management Framework which included Powershell 2.0

http://support.microsoft.com/kb/968929

I was able to find the correct product and downloaded the same.

The install went smooth and a reboot was not forced.I was back running with powershell on windows XP again.

Thanks for reading.

[Update Added on 03/30/2012 – Powershell pre installed on Windows 7 and Windows Server 2008 R2]

Database Mirroring for Local HA and Log Shipping for DR – Today and Tomorrow


With the release of SQL Server 2012,we have a chance to review our existing HA + DR solution and there is always an option to test and understand how this can be replaced/enhanced in SQL 2012.

Lets look at the below example (This is just one example,we have multiple HA,DR solutions) of one of the famous and most used HA + DR situation before the release of 2012 -

Database mirroring has been really flexible and this feature has been used as a HA solution for the primary data center.In the event of a failure the mirrored database will be live and users will be connected to it.The primary data center involves a witness server too,which will facilitate automatic failover.

The DR site has been configured with a lop shipping secondary and in the event of primary DC failure,this database will be used for connections.This cannot be automated and there is always a manual intervention.

There is potentially no data loss for database mirroring situation within the primary DC,however log shipping involves data loss.

After SQL Server  2012

We have DBM still in 2012,however this feature is deprecated.This simply means that future versions of SQL Server wont come with DBM facility and we need to use Always ON Availability Groups instead.

With 2012 we will be creating Windows Server Failover cluster between primary site and DR site and we will add all the nodes to this cluster.The next part will be creating availability groups for the required set of databases.In the above example we will have synchronous data transfer for the secondary replica within the primary DC.We can have either syn or async transfer for the secondary replica at the DR site.

The beauty of this solution is we can have readable secondaries.We can have up to 4 secondaries.

With Availability Groups we have the flexibility of grouping databases which needs to be failed over during an event.We can have automatic failover using fileshare witness.


Have you tried setting up Availability Groups within your environments?  I am curious to hear from you.

Thanks for reading.

TechEd India 2012 (#TechEdIn) In a nutshell


Technology,networking,knowledge sharing under one roof and that too for 3 consecutive days !!! Wow,thats TechEd for you.

[Picture courtesy - http://yodigo-contrablog.blogspot.com]

TechEd India 2012 gave me a fabulous ride this time.Even though I couldn’t attend the event in person,I was able to utilize the LIVE streaming options and had a gala time watching key notes,sessions and extravaganza demos right from the comfort of my home with a hot coffee in my hand.

I realized the fact that if I am getting this much fun while watching the LIVE stream,then how much fun it would have been to attend the event directly from Bangalore(TechEd India 2012 was held at Bangalore,India).

Twitter was flooded with live tweets from the event location and #TechEdIn tag was used.I had a great time tweeting LIVE all 3 days.

Day 1 started with a bang when Sanket Akerkar (Managing Director, Microsoft India) gave the opening  key note.He gave a clear picture on where Microsoft is heading to and what can be expected in the coming days.

I got a chance to watch three live streaming sessions on Day 3 of TechEd,and they were really exciting.

Here is a quick review of the sessions(In the order of occurrence) -

1. Peeling SQL Server like an Onion: Internals Debunked  – Vinod Kumar(@vinodk_sql )

Vinod’s session was very much interesting and the session evolved around on 3 main focus areas,ie Storage alignment and the behavior in SQL 2012,Security changes,Checkpoint changes and Memory changes.

The session explained how FSUTIL output needs to be read.When security topic came into picture,there were some very tricky questions asked.Couple of them which I remember is

Q - “How many sysadmins will be there by default on a SQL Server 2008 R2 Instance?”

Ans - I was pretty sure about SA already being sysadmin,NT AUTHORITY\SYSTEM will also be there.I also found that the service account that is used to start the SQL Server service and SQL Server Agent service are individually granted sysadmin rights in SQL Server.

Q - “How many new permissions are added to SQL 2012″

Ans - I had no clue on this and he confirmed that its 19.

The session also covered about DB recovery changes,VLF issues/enhancements,Checkpoints and some good take away related to Memory.

Overall an excellent session and it inspired me to read through Disk Alignment Best Practices doc to understand more about the concepts.I will be reaching out Vinod to clarify couple of my doubts and if I get more info,I will share that amoung every one here.

2.Speed Up! – Parallel Processes and Unparalleled Performance  – Pinal Dave (@pinaldave)

                        [Picture courtesy - http://arstechnica.com/apple/news]

Pinal’s session was one of the very exciting and enjoyable sessions of TechEd 2012.He mixed fun from beginning till end of the session and was definitely a crowd pulling effort.

He started with a bang asking a ‘Who is he?’ question.No one had clue on what is happening and finally he came up with the answer himself.He was talking about Gene Myron Amdahl.He formulated Amdahl’s law, which states a fundamental limitation of parallel computing.

He gave multiple examples to explain the concept and he ensured that everyone understand the concept like MDOP value and Cost threshold for parallelism.

There were so many demos explaining if parallelism is good or bad.He also focused that the decision to go for parallelism for OLAP,OLTP or hybrid needs to be taken after careful analysis.

Overall I enjoyed the session a lot and expecting more interesting tips from Pinal on parallelism.

3. Lesser Known Facts of SQL Server Backup and Restore  – Amit Banerjee(@banerjeeamit )

In Amit’s session you can assure one thing. You will learn at least a new concept and can walk back happily to home. This happened with me yet again and I learned couple of new things after his session.

The session was excellent and it went with great flow. All kinds of backups were discussed which included File Group level, File level etc.He explained what is a piecemeal restore.

There was focus on Volume Shadow Copy Service and he clearly explained the behind the scene concepts.The discussion also touched VDI.

He explained about the Data Transfer Options like BufferCountSize and Maxtransfersize and showed great set of demos on the same.

In a nut shell all the 3 sessions which I attended was fun and there was definite takeaway’s from my perspective.

I missed to attend another 3 important sessions.One of them was not streaming and the remaining 2 had conflicts with other sessions.I will watch the on demand  video for these 3 sessions and will update this post.The sessions are -

T-SQL Rediscovered with SQL Server 2012   – Jacob Sebastian (@jacobsebastian)

Keeping Your Database Available – ‘AlwaysOn’  – Balmukund Lakhani(@blakhani )

Tools to manage, troubleshoot and diagnose your Windows systems  – (@vinodk_sql)

Icing on the cake

Today morning when I opened tweet deck I was surprised to see this tweet which mentioned my name

This was Microsoft’s,TecEd’s way of saying Thank You and I really appreciate it.

TechEdIn 2012 closing key note was done by Moorthy Uppaluri (General Manager, Microsoft Corporation).As always this key note was also very special and his below words should make a big impact.

“It’s all about you and its starts with you”

TechEd was indeed a great event and a memorable one for me.

Thanks for reading.

Know the dates,Product life cycle dates !


With SQL Server 2012 RTM GA on April 2012 some of you might have already started planning to migrate your database environments to 2012.

Migrating to SQL Server 2012 is a great idea from various angles and a right budget will ease your efforts too(Keeping in mind the increase in licensing costs for 2012)

Migration is one of key activities which will be triggered soon after any product launch,and one of the key information which you should be aware as a DBA or as a decision maker is to understand when the mainstream support is going to be over for the existing versions of SQL Server.

You should know the dates,and the most important part of knowing this information is that it will enable you to take the right decisions.

When is the mainstream support for SQL Server 2005 SP4 is going to be over? What is extend support phase?

You can get all these information right under your finger tips by reading the below post from CSS Engineers post

http://blogs.msdn.com/b/psssql/archive/2010/02/17/mainstream-vs-extended-support-and-sql-server-2005-sp4-can-someone-explain-all-of-this.aspx

Microsoft has provided a great portal where you can get information on product life cycle

http://support.microsoft.com/lifecycle/search/

Conclusion

Know the exact dates which will help you to drive forward with correct decisions.

Thanks for reading.

SQLServerlaunch.com – The coolest virtual product launch ever and great sessions !


March 7th 2012 ! This was the day when SQL Server 2012 was launched virtually to everyone.I was looking forward for this event with great expectations.Even though we had a initial delay for the launch,everything was in place very soon and the platform http://www.sqlserverlaunch.com was open to everyone who had registered.

Wow!!! That was the first word which came out of my mouth as soon as I opened the site.The site was truly amazing and it was the right platform to launch a product like SQL Server.

The major sections of the portal was

1. Watch Key Note

2. Sessions by Track

3.Visit Expo Hall

4. Networking Lounge

What key notes were all about

There were 2 keynotes which were quite impressive.The first one was from Ted Kummert(Corp Vice President,Business Platform Div MSFT) and second was from Quentin Clark (Corp Vice President,Database Sys Grp MSFT)

Ted Kummert officially confirmed that the Evaluation version of SQL 2012 will be available from March 7th 2012 and the General Availability of SQL 2012 RTM will be from April 1st 2012.

MSDN subscribers will get the RTM bit before the GA.

Both the keynotes were focusing on Microsoft’s road map for the future and what SQL Server 2012 is bringing to the table.

What sessions were all about

The sessions were divided mainly into 3 tracks,the tracks clearly shows what SQL Server brings to the table.The tracks are

Mission Critical Confidence

Break Through Insights

Cloud On your terms

There are over 30 sessions with some exciting demos,and they are a must watch.

My top sessions are  -

1.Fast Time to Solution: Introduction to SQL Azure and Cloud Database Solutions by Greg Leake

2.Scale on Demand: Enable On-demand Scale with SQL Azure Federation by Cihan Biyikoglu

3.Required 9s and Data Protection: Automating SQL Server AlwaysOn Management  by Eric Kang and Will Snavely

4. Required 9s and Data Protection: Introduction to SQL Server 2012 AlwaysOn, New High Availability Solution by Santosh Balasubramanian

5.  Blazing-Fast Performance: Explore FileTables, Full-Text Search and Semantic Search for Faster, More Meaningful Results Across Unstructured Data by Michael Rys

6. Required 9s and Data Protection: Protect Data with Higher Chance of Recovery – Includes Recovery Advisor by Kevin Farlee

7. Optimizing SQL Server 2012 for Private Cloud by Darmadi Komo

8. Synchronizing Data “From On-Prem to Cloud” or “Cloud to Cloud” Using SQL Azure Data Sync by Mark Scurrell

9. Big Data Analytics with Apache Hadoop and SQL Server 2012 by Saptak Sen

What Expo Halls are all about

Expo halls were the really cool part of the launch.You have the opportunity to browse through various technology giants workshops and they will have videos showcasing their capabilities around SQL 2012,cloud,infrastructre etc.

There is also a Microsoft pavilion and a PASS pavilion which I found pretty interesting.

What Networking Lounge is all about

Yet again great opportunity for technology lovers to discuss on various topics virtually.There are topic pods which you can join and start involve on any discussions.This was pretty neat.

The sqlserverlaunch site has some other interesting stuff around.There is a point system which will help you to earn points and achieve levels and virtual badges.You will also get badges after visiting a expo hall for a particular company.

Profile score after winning points and badges will look like  -

A detailed explanation is available at http://www.sqlserverlaunch.com/ww/PointSystem

Overall the launch was a great and it was an exciting event.I would request you to make use of these resources to get a feel of what SQL server 2012 is capable of and trust me,it will be exciting.

Thanks for reading.

Watch Live Data using Extended Events in SQL Server 2012


Extended events are one of most powerful feature which was exposed from SQL server 2008(On wards).Till SQL 2012 most of your actions involving extended events used to be through TSQL codes.I strongly believe that TSQL is really powerful and still the best way to work with extended events.

Starting SQL 2012 administrators have the option to create/manage and work with extended events via GUI too.

This is pretty neat and lets do a demo to create an event using GUI start watching the live data -

1. We will proceed to create an extended event session using GUI.For the purpose of this demo we will choose the event ‘blocked_process_report’ and will name the session as ‘Event_Blocking’

If you right click the session,you will get a wizard which will guide you through the process very clearly.

2. Once you select the event.You have an option to configure the same to add additional info like database_id,session_id etc.

3.The TSQL code for the event session is

CREATE EVENT SESSION [Event_Blocking] 
ON SERVER ADD EVENT sqlserver.blocked_process_report( ACTION(sqlserver.database_name,
sqlserver.session_id, sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)) 
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO

4.The wizard will guide you to start the session and watch the live data as soon as you close the window.

5. Now as we have created a blocked process report event,lets simulate some blocking.

6.We will create a database,a table and will insert data to the data.

--Create a database
CREATE DATABASE Test_Extd_Blocking
GO

--Create a Table
Use[Test_Extd_Blocking]
CREATE TABLE Test_Table(ID Int Identity,Name Varchar(20))
GO
--Insert data to the table
BEGIN TRAN
Use[Test_Extd_Blocking]
INSERT INTO Test_Table VALUES('Ha')
GO
INSERT INTO Test_Table VALUES('Pe')
GO
INSERT INTO Test_Table VALUES('Ka')
--COMMIT TRAN

You might have noticed that I have used a BEGIN TRAN,but have not commited it.

7. We will now open up another session and will run the query

--Select data from the table
SELECT ID,Name FROM Test_Table

This is good enough to see a blocking report and lets watch the LIVE Data for the extended events session.

8. You will notice that the session have captured the blocking and you can see the LIVE Data

The report will be similar to what we get normally and will be in XML format

Conclusion

SQL 2012 comes with lot of flexibility,and extended events are really powerful.

Thanks for reading.