Setup LAB: 01 Generalize the Operating System Installation

This was the topic of discussion during last week, while a colleague of mine said that he faced some challenges while setting of his lab. After few additional queries, I figured out that he didn’t configure the domain controller correctly on his lab. As I am setting up a lab to learn about SQL Server 2012/2014, let me share the steps I follow to setup this lab.

Download Evaluation Software

I always prefer to use virtual machine to setup my own lab. Some of the available virtualization software, you may want to use, are:

For this lab, I am going to use Windows Server 2012 evaluation edition. You can download an evaluation copy of Windows Server 2012 from here. You can also download an evaluation copy of SQL Server 2012 from here.

Installing The Operating System

I created a VM with 1 CPU & 2GB of RAM, along with 40GB of Hard disk drive(HDD). As part of the HDD configuration, I choose to dynamically expand the disk, instead of allocating all the space at the beginning. I didn’t add any Network Adapter to the VM as part of the initial setup. Mount the Windows Server 2012 ISO image to the VM and powered it on, to start the Operating System installation. Restart the VM when prompted to complete the Operating System installation.


Configuring The Operating System

To simply this lab setup, I have disabled the Firewall Setting & Enabled Remote Management on this server. Next, I will add Dot Net Framework 3.5.

  • Open Server Manager, click on Manage & select Add Roles and Features to start adding new feature to the server.


  • Select Role-based or feature-based installation, as the type of installation


  • Select the name of the server from the available server pools, where I want to install the Dot Net Framework 3.5
  • Select .Net Framework 3.5 features, from the list of available features


  • We need to specify the alternate location of the installation source files, as prompted below.


  • Since Dot Net Framework 3.5 files were not installed by default, please specify the location to the SxS folder on the Windows server 2012 evaluation iso file, as mentioned in the below screenshot.


  • Click on the Install button to start the Dot Net Framework 3.5 Installation.


Once the Dot Net Framework 3.5 installation is completed, I will run sysperp utility on this server to generalize the VHD. Start the sysprep utility from C:\Windows\System32\Sysprep, & shutdown the server upon completion. Copy the VHD file, & reuse it to build the other servers required for this lab.


I am going to create four virtual machine from this VHD file. Out of this four VMs, one will be used as a domain controller & the rest three will be used for AlwaysOn setup. More on this later.

Happy Learning :) !!!

Posted in How To, Setup LAB, SQL Server, SQL2012, TestLab | Tagged , , , | 3 Comments

Checklist for Database File Movement

Below are the steps, which I usually follow as part of any database file movement activity.

  • Check the last full database backup completed successfully. If your change windows allows, prefer to take a latest full backup of the database. If the database is not in simple recovery model, initiate a transaction log backup as well.
  • Make sure there is no connections to the database. If there is an existing connection to the database from the Application, reach out the Application team to stop there application. Since these activities are mostly performed during a planned change window, you should ask your application’s contact to stop the application. I don’t prefer to kill the connections by myself, I rather asked to stop the application.
  • Take a note of the database files, which you are planning to move. For example, to know the details about the data file & transaction log file for the database Northwind, execute the below command:


  • Once you have the details about the database files location, take the database offline by executing the below command:


  • I had faced few challenges while moving the databases hosted on a SQL Server 2005 instance. As a standard process, once the database is offline, I grant full access to the windows local administrators group on the data & transaction log file for the database.
  • On the newer drive, make sure we have the required folder structures in place. The SQL Server Service account should have read & write permissions on the folders, where the data & transaction log file should reside. The SQL Agent service account should have read permission on the files.
  • Modify the location of the data & transaction log file in the system catalog of the SQL Server, by executing the below command:


  • Now that we have modified the file location in the system catalog, time to move the actual physical files. I prefer to use ROBOCOPY.EXE to move the files from one drive/mount point to another. The command to copy one file from one location to another is:


  • Once all the files are copied to the new location, time to bring the database online, by running the below command:


  • Congratulations!! The database is online from the new location. Now time to remove the additional permission, which we have added in step-5. Remove the permission granted to the windows local administrator earlier from the physical database files.
  • Inform your Application user to test their application. Once they confirm that the application is working as expected, initiate a full database backup.

This complete the steps requires to move a database file. Added the script used in this post.

 -- to check location of the data file
use [master];
exec sp_helpdb 'Northwind';

-- to take the database offline
use [master];
alter database [Northwind]
	set offline;

-- to modify the location of data and tlog file in the system catalog
use [master];
alter database [Northwind]
	modify file(name = 'Northwind', filename = 'C:\UserDB\SQL2012\Data\Northwnd.mdf');
alter database [Northwind]
	Modify file(name = 'Northwind', filename = 'C:\UserDB\SQL2012\TLog\Northwnd.ldf');

-- Once the files are copied using Robocopy, use the below command to bring the database online
use [master];
alter database [Northwind]
	set online;

Hope, this will help someone, Happy Learning :)

Posted in How To, RoboCopy, SQL Server | Tagged , , | 2 Comments

Running SQLDiag as Agent Job

This was the discussion of last evening, which was started after we receive a similar request from a specific team. The team wanted to start this trace by themselves without involving the DBAs. One of the possibility was to run SQLDiag as a Windows service; however the requirement in hand is to run it as a SQL Agent  job.

In case you wanted to read about this utility & it’s additional parameters, please refer the below page from SQL Server Books Online. The following parameter helps in the initial setup of SQLDiag.

SQLDiag.exe /E +00:20:00 /N 2 /Q /O E:\temp\SQLDiag_Output

As I tested the above script, SQLDiag utility was started & collected data for 20 minutes before stopping. Since the script was running successfully, the next step was to run it as a SQL Agent job.

I created a job on my lab instance to run SQLDiag & provided Read & Write permission to the SQL Server Agent account on the SQLDiag output folder. On the Job Step properties page, select Operating system (CmdExec) as the type & add the below script. In case of my lab environment, the SQLDiag output will be saved in E:\temp\SQLDiag_Output folder.


The default traces, system configuration files are available in the output folder. There will be an additional folder named internal present inside the output folder. The internal folder contains all the output & log files generated by SQLDiag.


Hope, this will suffice the need. Happy Learning !!

Posted in Automation in SQL Server, How To, Just Learned, SQL Server Tools | Tagged , , | 1 Comment

PowerShell Journey: with 3 Cmdlets !!!

Believe me or not, but that’s true! The time was already passed and we have to run!! I am talking about Learning PowerShell. Although, I am using PowerShell Scripts for quite sometimes, I didn’t pay a lot more attention to learn in details (probably, it was not required that time Winking smile). You probably thinking, “Wait a minute, you said, you are using PowerShell scripts, but you didn’t know PowerShell?” Correct, I learned three command-lets (cmdlet), after which I was able to understand few scripts, which I am using at work. This post, is all about learning three PowerShell cmdlet, which will help you to learn the rest of them.

Things you should do as you begin your journey with PowerShell

Although, I believe it’s our individual interest, whether we want to learn PowerShell or not, here are the few things, which can boost our learning.

  1. Stop using cmd.exe !! Yes, this is the biggest obstacle in your learning. I am sure, most of us (including me Winking smile) run this tool more than once in a day, since last few years. The good news is, most of the commands, which we run in cmd.exe (e.g. ping/ipconfig/robocopy/mstsc, etc.), is also available in PowerShell (or a better updated version with more features)
  2. List down the activities, we repeat everyday and try to automate it. As we are updating SQL Server 2008 R2 Service Pack2, we had to check all the servers to make sure, we have the enough free space available to complete the SP2 update. Believe me or not, with the help of PowerShell, we extracted the information in a few minutes !!! Last time, when we did it manually, I think, we spent nearly 12 hours to collect that information Sad smile
  3. When in doubt, ask for help! A quick way is the twitter hash tag #help #PowerShell. Apart from that,  you will find several online forums, where you can post your query and someone from the community will come forward to help you (it’s normally nice, if you tell them not only about the task you want to automate; but also the way you had already tried Smile).
  4. Build a test lab. Create a virtual machine on your personal laptop/desktop, for your learning. Since PowerShell is a very powerful tool, it’s possible, that you can unknowingly commit some mistakes, which may result in loss of data Sad smile. However, if you are using a VM, you don’t have that fear. ( I know, how it feels, if your old photos got deleted Winking smile). Please … Please.. Please, Don’t use the Production environment for your learning.

Enough of the talks !!! Let’s proceed with, the three cmdlets.

Three Cmdlets to Start

1. Get-Help

The first command, I learnt to run on cmd.exe was help. As with most other Get-Helpcommands, help is also available in PowerShell. You can type help/Get-Help/man, to start the help subsystem. If you are running PowerShell v1/v2, when you run Get-Help, you will get the complete help subsystem. However, if you are running PowerShell V3 or above, the help subsystem is not available by default, you have to install it manually. To install the help file on a system running PowerShell v3, run the following command (make sure, you have opened the PowerShell console as an administrator Winking smile)

# Update & Install the Help files for all the modules, with language as EN-US
Update-Help -Module * -UICulture "en-us" -Force

The Help subsystem is our friend & mentor in our journey with PowerShell. Spend some time with Get-Help, so that you can go well together.

2. Get-Command

In most of the cases, a PowerShell cmdlet is a combination of a verb & Noun (you missed the grammar class in school, time to learn it again Winking smile). There is a list of approved verbs (98 in PowerShell v3), which can be used in PowerShell. As you might think of, Get-command gives us a list of all the cmdlets available in PowerShell along with Aliases & Functions. To learn more about the Get-Command cmdlet, we are going to ask the mentor by executing the cmdlets:

# To Lean more about the Get-Command
Get-Help Get-Command

3. Get-Member

As we learn about the new cmdlets, we need to find out more options available with a particular command, and Get-Member, will help us to find all the properties & methods available with a command. For example, if I want to know all the methods & Properties available with the Get-Command, I will execute the following cmdlets:

# Using Get-Member
Get-Command | Get-Member

4. Set-ExecutionPolicy

Although I said three cmdlets, this one will be useful, when you execute a script. PowerShell is secure by default; it will not allow you to run any script, unless, you said it to do so. The default execution policy (or script execution policy) is Restricted; however, you can change it. You can set your execution policy as Remote Signed, although, later you will learn that the best possible option should be AllSigned. To learn more about all the available option or set it to remote signed , ask your mentor, as shown below:

# To verify the current execution policy of the system

# To lean all about the Set-ExecutionPolicy
Get-Help Set-ExecutionPolicy -Full

# To Set the execution policy to RemoteSigned
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned


Now I’m more Confused Confused smile

In case I confused you (sometimes, I do confuse myselfWinking smile), here are two superb courses on PowerShell on Microsoft Virtual Academy, presented by, none other than, the inventor of PowerShell: “Jeffery Snover” (b|t), Distinguished Engineer & Lead Architect for Windows Server Division.

  1. Getting Started With PowerShell 3.0 JumpStart
  2. Advanced Tools & Scripting with PowerShell 3.0 JumpStart

A few other online resources, which you may want to visit:

1. Windows PowerShell Blog
2. Hey, Scripting Guy! Blog
3. PowerShell Magazine
4. Ravikanth’s Blog
5. Bangalore PowerShell User Group

Hope, you will start using PowerShell, Happy Learning Smile

Posted in PowerShell | Tagged | Leave a comment

SSAS 101 with SQLBangalore User Group

Another wonderful event by SQLBangalore User group, this time, it was with SQL Server Analysis Services. Yesterday, 02nd March, 2013, Bangalore SQL enthusiastic met again to lean about SQL Server Analysis Service. The event was @ Signature Building, Microsoft. My exposer to SSAS is very limited, thus, it was a great learning experience all together.

Start your journey with Multidimensional using SSAS

The first session was about Multidimensional using SSAS by Sudhir Rawat (Microsoft).  Points to note down are:

  • We need de-normalised data for Analysis services.
  • Analysis database contains fact tables and dimension tables
  • If fact table size grows, maintenance will take longer and the performance will be reduced
  • Tabular Mode in new in SQL Server 2012
  • SQL Server Data Tool is introduced in SQL Server 2012 (Previously known as Business Intelligent Development Studio)
  • Analysis database can be used with Reporting Service

Introduction to Cubes

The second session was on Introduction to Cubes by Deepthi Anantharam (Technology Evangelist, Microsoft). Few points, which I noted down during the session are:

  • Cube is a combination of dimensions and measures
  • Hierarchies are of two types:
    • Natural Hierarchies
    • Un-Natural Hierarchies
  • OLAP are of three types:
    • R-OLAP (stands for Relational OLAP)
    • M-OLAP (stands for Multi-dimensional OLAP)
    • H-OLAP (stands for Hybrid OLAP)
  • Partition contains portion of data from a fact table
  • Different partitions can have different aggregation strategies
  • Partitions can be processed separately
  • Three new Buzz words in SSAS world:
    • DAX
    • xVelocity
    • Tabular Model

Introduction to SQL Server 2012 Analysis Services – Tabular Mode

The third session was done by Karan Gulati (Microsoft). ABCD ..M was really awesome Smile

  • ABCD .. M stands for Any Body Can Develop … Model
  • Three flavours for Analysis Service
    • Multi-dimensional and data mining
    • SharePoint and Power Pivot
    • Tabular Model
  • About Tabular Model
    • In memory database or xVelocity
    • Based on Relational Methodology
    • Column Oriented databases

BI with SharePoint

The surprise speaker started his session on BI with SharePoint. Few takeaways are:

  • Difference between data and information? Processed data is information
  • To share data/documents on SharePoint Server, use any of the following tools:
    • Reports
    • Dashboards
    • Excel Workbooks
    • Analytics Values
    • Scorecard
  • Three types of Service Applications
    • Visio Service
    • Excel Service
    • PerformancePoint Service
  • Slicers are advanced filters

Additional Resources

To attend the future events, join SQLBangalore User group

Happy Learning Smile

Posted in Just Learned, SQL2012, SSAS, UG Meet | Tagged , , , | 6 Comments

Failed Job Notification To Event Log

Earlier, I had written a post about a failed job issue. A friend of mine, after reading this post, asked how should he verify the SQL jobs running in his environment without this notification option? By executing the following script, we can get the list of sql jobs, which are currently enabled, along with their Notification Status.

-- Enabled Jobs along with Notification Status
use [msdb];
select	sj.[name] as [Job_Name],
	sj.[notify_level_eventlog] as [Write_to_Application_Eventlog]
from dbo.sysjobs as sj
where sj.[enabled] = 1;

To list only the jobs, where the job status will not be written to the Windows Application event log, modify the above script as shown below:

-- List of jobs,upon failure, will not be written to the Windows Application Event Log
use [msdb];
select	sj.[name] as [Job_Name],
	sj.[notify_level_eventlog] as [Write_to_Application_Eventlog]
from dbo.sysjobs as sj
where (sj.[enabled] = 1) and (sj.[notify_level_eventlog] = 0);

If you have registered a list of SQL Server instances under the Registered Server option in SSMS, you can execute the above query to list all the jobs where the job failure event will not be written to Windows Application event log.


To read more about the various columns from sysjobs, read the following post from Books Online.

Happy Learning Smile

Posted in Automation in SQL Server, How To, Jobs, Just Learned, SQL Server, T-SQL | Tagged , , , , , | Leave a comment

Contained Database 102

In my previous post, we discussed about Contained database, how we configure the SQL Server instance, how to create a Contained database, contained user creation along with how to connect using Contained user account. In this post, we are going to look at how contained database removes the dependency on collation setting.

Collation Setting

According to Books Online, a Collation encodes the rules governing the proper use of characters for either a language or an alphabet.

A SQL Server collation defines how database engine stores and operates on characters and Unicode data.

Not only Collation setting apply to the stored data; but it also to variables names, metadata and temporary objects.

As of Today

As of today, while moving a database from one server to other, we normally verify that the collation setting of the database, source server & destination server are same. Most of time, we use the server collation setting, as default, for the database; however, the problem starts when we create a database with different collation setting or moved the database to an instance with different collation setting. Look at the following script block, which will create a database with collation setting Turkish_CI_AS, on a SQL Server instance with collation setting of SQL_Latin1_General_CP1_CI_AS.

-- Create a database with collation setting Turkish_CI_AS
use [master];
if exists (Select 1 from sys.databases where [name] = 'NonContainedDB')
		alter database [NonContainedDB]
			set single_user with rollback immediate;
		drop database [NonContainedDB];
create database [NonContainedDB]
	collate Turkish_CI_AS;

Next we are going to create a table and a temp table in the above database.

-- create a table in the NonContainedDB
use [NonContainedDB];
create table tbltest (
testname	char(10)
-- create a temp table
use [NonContainedDB];
create table #tbltest (
testname	char(10)
-- join the above two tables
use [NonContainedDB];
select *
from tbltest inner join #tbltest
on tbltest.testname = #tbltest.testname;

Joining these two tables will throw the following error.

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Turkish_CI_AS” in the equal to operation.

We can fix this issue, by using the COLLATE DATABASE_DEFAULT clause, while defining the temporary objects. If we miss it anytime, then we are back to the same problem.

Collation Setting @ Contained database

Partial contained database, introduced the concept of Catalog Collation, which will be used for system metadata and temporary objects. The new Catalog collation will have a collation setting Latin1_General_100_CI_AS_WS_KS_SC and will be used for metadata (Variables, Cursors, temp table, table name, and GoTo level). The catalog collation setting is fixed for all contained databases across all SQL Server 2012 instances.

While the catalog collation affects the metadata, the database collation will be used to store the data inside the tables & temp tables.

Continuing with the above example, if we enable the containment setting of the above database and re-run the join query, our query will complete without any error. See the script below

-- Change the Containment option to Partial
use [master];
alter database [NonContainedDB]
	set containment = Partial;
-- create a temp table, without "collate as database_default"
use [NonContainedDB];
create table #tbltest (
testchar	char(10)
-- Perform the same join which failed earlier
use [NonContainedDB];
select *
from tbltest inner join #tbltest
on tbltest.testname = #tbltest.testchar;

Benefits with Partial Containment

As a standard procedure, while moving a database from one instance to another, I always check the collation setting of the user database, source instance, and destination instance. Apart from collation setting, we also need to transfer the logins to the other instance, so that the application can connect. Every database should have at least these two dependencies. With Partial containment, these dependencies are removed.

As a databases developer, we don’t have to worry about the instance collation anymore. With Partial Contained database, now we can develop in our own environment and our database can run on a SQL Server 2012 instance, without any issue. Partial Contained database can also be used with the new high availability feature, AlwaysOn, a feature introduced with SQL Server 2012.


We can’t use Partial Contained databases in the following scenarios:

  • Replication, Change Data Capture, Change Tracking
  • Cross database queries

Read More from Books Online

SQL Server Books Online is the best place to learn about anything related to SQL Server. So as a starting point, you can read more about contained database here. You can also download the all the scripts used in these two posts from here.

Happy Learning Smile

Posted in ContainedDB, How To, Just Learned, SQL Server, SQL2012, UG Meet | Tagged , , , , , | Leave a comment