Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,037,799 hits
  • Select GETDATE()

    March 2023
    M T W T F S S

Archive for the ‘Introduction’ Category

A-Z of In-Memory OLTP : SSMS Enhancements for In-Memory

Posted by blakhani on December 10, 2013

Microsoft believes that everyone in the world is not a developer there are some DBAs as well. Be it Visual Studio, SQL Server or any other programming paradigm – Microsoft makes it easy for developers and DBA alike. In my previous post, you would have read about new T-SQL constructs added to support In-Memory OLTP objects. This blog post shows the SSMS improvements and dialogs introduced with SQL Server 2014 to work with In-Memory OLTP.


We have learned earlier that we need a new filegroup which would contain memory_optimized_data. This is the key point to understand the flow of actions below..

  • Right Click on database folder in SSMS and Choose “New database”. This is the same place where we create regular database.


  • Open “New Database” interface, go to “Filegroups” tab. Then click on “Add Filegroup” under “Memory Optimized Data” section.


  • I have given name as “SQLSeverHelp_IMO_FG” and then went back to “General” tab.


  • Here is the little explanation of numbered steps.
    1. We need to come back to “General” Tab after creating filegroup in earlier step.
    2. Provide “Database name” SQLSeverHelp_IMO
    3. Click on “Add” button and provide details in new row (third row)
    4. We have given SQLSeverHelp_IMO_dir as logical name
    5. Since it is going to contain In-Memory optimized data, we need to use “FILESTREAM Data” in “File Type”
    6. Once we choose drop down in step 5, “Filegroup” would be “SQLSeverHelp_IMO_FG” created in previous screen.
    7. Provide complete physical “Path” where the files/data needs to be stored for In-Memory tables. C:\IMO_Database\SQLSeverHelp_IMO_dir 

Follow sequence given in above image and hit OK.

Do you know that I can remember all command by heart? I am joking! If you want to script this action, use the “Script” button (shown in below image). This would give us the same script (with little more setting) which we use in previous part.


The “Script” feature is available in almost every user interface of SSMS. Another option is to use predefined template. This is one of under-utilized/less known feature of SSMS. As shown below, go to “View” Menu and click on “Template Explorer” (Ctrl+Alt+T) to access a lot of predefined scripts.


  • Once we click on “template explorer”, we would see “template browser” opened within management studio. Over there, we can see many template and one of them of our interest, as of now, is highlighted in below image.


  • Double click would open the template and we need to use “Ctrl+Shift+M” to get window called “Specify Values for Template Parameters”


  • We can make changes here and hit OK. We can edit path, name and modify the TSQL as per our requirement.

If you have an existing database then, ALTER DATABASE would be needed. This is because the filegroup for memory_optimized_data might not have been defined. We can go to database properties and add new filegroup and follow same screen with 7 steps.


Next step is to create an In-Memory Optimized table. Note that there is no “table designer” UI to create the In-Memory Optimized table. If you have followed earlier steps, template explorer might be open (else press Ctrl+Alt+T)

Option # 1
In situations where we have an existing database which is not having memory_optimize_data filrgroup defined, we can use template called “Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table” (shown below)


Option # 2
If we have memory optimized filegroup in the database then we can choose “Create Memory Optimized Table”


Option # 3
If we go with conventional way of creating table using SSMS then we can Right Click on “Tables” folder under the database SQLSeverHelp_IMO and choose New > Memory Optimized Table


All three options would open a query window with the template. (See, I told you earlier – no table designer)


In this query windows, we need to use Ctrl+Shift+M and replace the parameter as needed. We can add more columns, indexes and make modifications. As the commented section in above template says, we need to have filegroup defined for memory_optimized_data


In a nut-shell, in the absence of UI, you need to have T-SQL syntax knowledge.


Same as CREATE TABLE, there is no special interface available for natively compiled procedure. We can use either Right Click technique (#1) or template browser (#2) to reach to template.

Option # 1


Option # 2


Both of the options above are going to open template and then we can write the code there. Remember the shortcut Ctrl+Shift+M for filling template parameter.


Till now we have learned how to create objects. In next post, we would see what happens differently (than regular objects) when in-memory tables are in-memory stored procedures are created. Stay tuned!

Missed reading earlier posts? Here is the quick reference:

Stay Tuned!

Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOnPaperback, Kindle


Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, Introduction, screen shot, Screenshot, SQL Server 2014, SQL Server Management Studio, SSMS | Tagged: , , , , , , , , | 4 Comments »

Now Available @ Amazon : My Book on SQL Server 2012 AlwaysOn

Posted by blakhani on August 5, 2013

It might come as surprise to many of you. Finally, book authored by me is available for public on Amazon. (paperback and kindle). Those who follow Vinod’s (b|t) blog have seen this announcement already.

It has been close to an year of writing and multiple reviews, my book on “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” is finally available on Amazon.

Reason of this blog is to answer two common questions which would be asked by my friends – “How?” and “Why?”

“How” –

A little spark was ignited when me, Vinod (b|t). and Pinal (b|t) were sitting together after a User Group meeting and thinking – what can be done to share the knowledge. Pinal and Vinod were already a well-known blogger and author. I said in feeble voice – I may have some additional knowledge on AlwaysOn, how about a book on this topic? … all of us agreed and journey started.
Thanks to Vinod and Pinal for the spark of inspiration to set me off into looking things from different perspective. I wouldn’t have thought to become an “author” without your support. 

After take off of the project, table of content of 5 chapters were prepared and we have taken task to write everything about the feature. I got my home PC upgraded to better RAM so that I can use my home PC for Hyper-V images which were needed for the book. We targeted 100 page and during weekly checkpoint meeting we always thought “something is missing” … after reaching 500+ pages, we felt that we have covered almost everything about the feature which can bring a starter to intermediate level.  Decision was taken to increase the page size and trim down the number of pages without compromising quantity and quality of knowledge. Lots of hours spent on emails, phone calls, chat, internet research, books-online reading, blogs reading to make fine prints. Finally, after an year of hard efforts, book was ready to be published. In paperback as well as kindle version.

“Why” –

It feels good when someone meets and says – “You blog helped me in fixing a problem”. Being a part of SQL Server technical support team, I felt that there is not much “organized” content on AlwaysOn feature. I spoke to many folks and sensed a “fear” of this new feature. There are various blogs on the same topic and books online also has lots of content. By writing this book and sharing my knowledge , I feel that I am doing my part for the community which is driven by passionate people and MVPs.  I love technology, SQL Server and its features. There were many questions asked and being of a very tidy disposition, I liked to have them answered. That’s why we added a new chapter in the book for FAQs along and there were around 70+ questions.

What we have in the book?

Here is the crisp summary of the book.


18 Chapters at a Glance

Chapter 1. High Availability and Disaster Recovery Concepts
Chapter 2. Existing High Availability and Disaster Recovery Options
Chapter 3. What is AlwaysOn?
Chapter 4. Understanding Quorum Models
Chapter 5. AlwaysOn Availability Groups
Chapter 6. Availability Group (AG) Actions on the Secondary
Chapter 7. Deploying AlwaysOn Availability Groups
Chapter 8. Features of AlwaysOn Availability Groups
Chapter 9. AlwaysOn Monitoring and Troubleshooting
Chapter 10. AlwaysOn Diagnostics
Chapter 11. AlwaysOn Advanced Monitoring
Chapter 12. Deployment Variations of AlwaysOn
Chapter 13. AlwaysOn Common Issues
Chapter 14. Availability Group Failover
Chapter 15. Migrating from Previous High Availability Scenarios
Chapter 16. AlwaysOn Availability Group Maintenance Activities
Chapter 17. AlwaysOn – FAQs
Chapter 18. SQL Server 2014 – AlwaysOn Enhancements Teaser


How can you get it?

At this moment we don’t have any other channel to publish this book in India. Our publisher is in constant pursuit to look for alternatives, till then Amazon will the only channel.
Print Book: Amazon
e-Book: Kindle

You can read first two chapters for free on amazon via LOOK INSIDE feature on kindle edition.



I truly hope that it would help many DBAs in the world to get started with AlwaysOn Availability Group feature and deploy as high availability + disaster recovery in the environment.

Before I conclude this blog post, I want to specifically call out name of few people from my management team who have backed me in this adventure of learning and sharing via this book – Sri Krishna Jagannath (Team Manager – SQL Support), Rahul Jacob (Team Manager – SQL Support), Xavier S Raj (Escalation Manager – SQL Support), Ranjan Bhattacharjee (Group Manager – Developer Support). It would have been impossible to deliver this book without their unconditional support.

Last, but not the least, Special Thanks to my wife and daughter for being supportive through out this journey!


Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, Introduction, SQL Server, SQL Server 2012, Step by Step, TechEd | 11 Comments »

Step by Step – Configuring SQL Server 2012 AlwaysOn in Windows 7 Virtual Environment (Part 1)

Posted by blakhani on May 3, 2012

I have been demonstrating AlwaysOn feature to various audiences on various forums (TechEd, SQL Saturday, Virtual TechDays etc.). I have been using Windows Server 2008 R2 with Hyper-V to create multiple machine and demonstrate the feature. Someone asked me if he can do the same thing using his Windows 7 laptop? I tried looking at virtualization techniques available on Windows 7 and I found that it should be possible. I have tried to make it as descriptive as possible with step by step screenshots to configure whole setup.This topic would be a multipart series.

  1. Configure Windows 7 to use Virtualization and Configure three machines (One domain controller, two member servers). Also configure networking between them.
  2. Create Domain and configure the machines to make sure they are able to talk to each other.
  3. Install SQL Server 2012 on the servers.
  4. Configure AlwaysOn.

First download Windows Virtual PC on Windows 7 machine. Go to and choose below option. I am not going to Install Windows XP mode.


One clicked, choose your platform and language and click on download button.


This would install Windows Update to enable Virtual PC

03_Windows Installer


Restart the machine and you should be able to see below under "Start” > “Programs” > “Windows Virtual PC”

04_Windows Virual PC Shortcut

Next step would be to download the Virtual Hard Disk (a.k.a. VHD) file which I can use to configure my machine. Note that You need to use 32 bit images to make this work. Here is the link to download Windows Server 2008 images (Windows Server 2008 Enterprise Edition x86 (Full Install) VHD)

There are three parts which you need to download and then extract to user by Virtual PC. It would look like below.

05_Downloaded VHD

Double Click on the File and Extract the data into “C:\VHDs\DC” folder as shown below:


Once extraction is complete, make three copies of the vhd (Windows2008Fullx86Ent) file and keep them in separate folders as shown below. [I am showing only two, same exists for Srv2 also]


Lets add the machine to Virtual PC. Open the UI from "Start” > “Programs” > “Windows Virtual PC” > “Windows Virtual PC” and choose “Create virtual machine”

08_Create Machine 

Follow the wizard and create DC

09_Create Machine1 

10_Create Machine_Mem

11_Create Machine_Disk

One done, lets go to network setting and choose “Internal Network” This is because I don’t want to create domain controller in my office domain network.

Right Click on DC and choose “Settings”


Go to Networking option and choose “Internal Network”


Now start the first machine and make sure we are able to log in.


You need to follow few steps which are pretty self-explanatory. It would also as you to set machine name (I have given name as DC) new password for Administrator. Once you are done with that, this is what you should see 🙂


As homework, before moving to second part, you need to do the same thing with two more machines and here is what you should see in Windows Virtual PC.

16_Three Machines

See you at next part of Series!

Hope this would help someone in the world!

Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, Images, Installation, Installation Guide, Introduction, screen shot, Screenshot, SQL Server 2012, Step by Step | 4 Comments »