Tuesday, August 25, 2015

Error 1814 - Could not Start SQL Server Services

Hey Guys,

Some of you might be very well familiar with the below error. Let's see under what circumstances I've faced this error.

Few months back while I was configuring "AlwaysON" on one of the Server, something went wrong and due to which I've to completely decommission the AlwaysON configuration along with Windows Clustering.

After doing this, I restated the server and when the server was up what I saw was the SQL Server service was disabled, tried starting the same through Configuration Manager but hard luck I was facing the below error message.

Error 1814
Error 1814
Let's have a look below into the SQL Server Error log what its was it looking like:

Error Log
Error Log
With these error logs we started troubleshooting:
  1. The error in the SQL Error Log states that due to insufficient disk space "TempDB" was not created.
  2. Now what we were wondering about the error, as it was a new Server and disk was having sufficient space. So insufficient space was out of question.
  3. Just to cross verify I open the My Computer tab and what I saw was only C: drive was reflecting and rest of the drive were not visible.
  4. While installation I have kept the TempDB in D: drive.
  5. Now here comes the twist, let's recollect the above activity of AlwaysON which I was performing. But for some reason I have to decommission it.
  6. During decommissioning I've put the disks into offline state and due to which the disk were not accessible.
  7. So at start of SQL Server if the TempDB is not created, the SQL Server Services will not start.
Brief note on System Databases:

  1. Master, Model, MSDB and TempDB are system databases.
  2. Files of the Master Database is used in start up parameter during the SQL Server instance starts. 
  3. So if the Master Database is corrupted SQL Server instance will not start. 
  4. Also as it's one of the important properties is it stores the location information for other Database. Therefore Master Database is said to be the heart of SQL Server.
  5. Model Database acts as a template for the other User Database as well as TempDB while creation.
  6. So even if Model Database is corrupted SQL Server instances will not start. As indirectly TempDB will not start and if TempDB will not start SQL Server instance will not start.  
So guys System Database plays an very important role in proper functioning of SQL Server Instance.

Here my question is: What if MSDB is corrupted, will the SQL Server Instance will start? You can comment below.

Thank You Guys.
Keep Learning and Enjoy Learning!!!

Wednesday, August 12, 2015

SQL Server 2012 Clustering - Part 1

Dear Friends,

Recently I've configured Clustering as a Disaster Recovery (DR) and High Availability (HA) solution for one of my project. You might find many blogs on this topic explaining the concept and how to configure SQL Clustering.

Here I will try to explain the same in my terms and keep it as much as simple. And yes it will be from starch i.e. We will first see the how to configure Windows Cluster and then on top of it will configure SQL Server Clustering. It will be a multi part series.

Brief on Clustering:  

Cluster can be defined as when two independent machine come into existence with each other by sharing the disks in common. This is done because, just in case of any disaster on physical machine, it will automatically failover the disks to another machine as the disks are shared between the Servers. This will recover the Database in disaster. So this solution can be used as Disaster Recovery (DR) solution along with High Availability (HA).

I. Step for configuration of Windows Clustering Using GUI:

1. To start with the Windows Clustering; it is mandatory to have the Shared Storage between the Servers (which should be setup by the SAN Administrator).
To verify these disks open Server Manager and verify if the Shared Disks are visible on both the Servers like you can see in the below snapshot.

Server Manager GUI
Server Manager GUI


 2. Now we have to add "Failover Cluster" Roles on the Server. Open "Server Manager" then click "Add Roles And Feature".

Below snapshot means:

   i. Select Roles-based option.
   ii. Select the appropriate Server name.
   iii. Click Next
   iv. Check "Failover Cluster"
    v. Click "Add Feature" then click install



3. Once the roles is added, go to Service manager and then, Tools and then, Failover Cluster Manager. Open Failover Cluster Manager GUI.


4. As shown in the below snapshot, Go to extreme Right and click Validate Configuration.


5. Validate Configuration step will basically check Storage, Network, etc. One need to check the recommended setting and validate the configuration.
The Disk will go offline and come back online; if Disk Validation is checked. (Disk validation is basically unchecked when the Cluster is in production.)

Below snapshot means:

   i. Enter the both the Server Names.
   ii. Run the recommended setting.
   iii. You can view the progress
   iv. View the error if any or click finish.


6. Once it is successfully validated, you need to mention a Windows Cluster Name and an IP Address for that Windows Cluster as you can see in the below snapshot. Once you have successfully completed click Finish.



You can cross verify by cmd command and ping the Windows Cluster name or the IP Address provided above in point 5.

II. Step for configuration of Windows Clustering Using Power Shell:

1. We need to first install the Cluster Failover Management Tools:

Following commands can be run from the Power Shell:

a. Get-WindowsFeature Failover*

b. Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools --This command will install the Windows Feature Failover tools on the local Server but if you want to install the same on remote Server as well we have to run the following command:

c. Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -ComputerName XYZ


2. Till now you have installed Failover feature on the Servers, next you have to configure Windows Cluster on these Servers:

Following commands can be run from the Power Shell:

d. New-Cluster -name SQL_Cluster -Node ABC, XYZ -StaticAddress xxx.xx.xx.xxx


where SQL_Cluster is Windows Cluster Name
ABC and XYZ are the Server Names between which Cluster needs to be installed
xxx.xx.xx.xxx  is the static IP Address for Windows Cluster.

So you have seen how simple is to build Windows Clustering through GUI as well as Poser Shell. This is not just limited as a pre- requisite to SQL Server Clustering but also for Configuring AlwaysON.

Here's the end of Part 1 of SQL Server 2012 Clustering. We have seen how to configure the Windows Server Clustering which is the first step towards configuring SQL Server Clustering.

Now next will be configuring the Quorum for the Cluster.

Click here to refer Part 2 and Part 3 of this topic.

Thank you for reading this post. So stay tuned and share your comments too.

Keep Learning and Happy Learning!!!

Thursday, August 6, 2015

New Features in SQL Server 2016 - Part 2

Good Morning Guys,

As we have seen the top 3 new features in SQL Server 2016, here is the continuation of that post. We will see few other enhancements in it.

Following are the list extending to these top 3 features:

4. Enhancements to AlwaysOn:

As everyone knows the concept of AlwaysON was introduced by Microsoft in SQL Server 2012. This was introduced due to many limitations in the previous versions of SQL Server on High Availability and Disaster Recovery AlwaysON was introduced for mission critical Enterprise Application.

It was released for only Enterprise Edition but in SQL Server 2016 it will be released in Standard Edition of course with limited functionality. AlwaysON has also seen enhancement in SQL Server 2014 as well.

There is 1 Primary replica, 8 Secondary replicas and upto 3 replicas can be synchronized compared to 2 secondary replica can be synchronized in 2014.

Distributed transaction or MSDTC was not supported between Databases on AG in 2012 and 2014; which is now supported in SQL Server 2016.

We can now perform Full, File, File group or Log backups on the secondary replica Databases, but can't perform Differential Backup.

For more detail please refer to this blog on AlwaysON.

5. Native JSON Support:

JSON stands for JavaScript Object Notation. This is mainly provided for the developers, who will use this function for organizing the data in more logical manner. It is much more similar to XML already available by SQL Server.

It format the SQL result set as JSON by adding FOR JSON clause in the From statement. It is very much similar to XML in terms of types as well:
  • RAW
  • AUTO
  • Explicit
Following is the syntax for using the JSON:

SELECT column, expression, column as alias
 FROM table1, table2, table3
 FOR JSON [AUTO | PATH]

Figure 1 shows the demo how the JSON script be written from a SQL Table:

JSON Code
Figure 1
Please refer the msdn blog here for more detail on JSON.


6. Enhancement In-Memory OLTP:

The feature of In-memory OLTP was introduced in SQL Server 2014 with the name "Hekaton" which is derived from a Greek which means 'one hundred' i.e. the goal of the project was to see 100x performance appraisal.

It has a separate query processor engine which is lock free design but at the same time it also maintain the data transnational integrity. Due to which it is different from Pin Table in SQL Server 6.5 release or putting Databases in SSD's (Solid State Disks). Refer this link to get an idea about the architecture of In-memory OLTP.

When it has done the debut it came with lots of drawback. And now from these demerits many has been converted to merit. lets see some of them below:

    i.  The max table size 256 GB which has changed to 2 TB.

    ii. TDE feature which is the greatest security feature in SQL Server was not supported in 2014 version, but now in 2016 it is supported.

    iii. Another major drawback clause such as IN, EXISTS, DISTINCT,OR,NOT,OUTER JOIN was not supported for query. Now these clauses are supported in SQL Server 2016.

    iv. LOB data type such as Varbinary(MAX) or Varchar(MAX) were not supported by the released version but from SQL Server 2016 it is supported.

    v. Similar way Foreign Key was not supported in older version which is not supported in SQL Server 2016.

There are many other new properties which has been enhanced in the new product of SQL Server 2016 for In-Memory OLTP check this msdn link for more detail.

These were the few new and some enhancement done in SQL Serve 2016. Check out this link which has some series of SQL unplugged videos for more details. So guys get ready to work with these exciting new features of SQL Server 2016 for Administrators.

Also along with features for DBA many more are waiting for BI and Developers guys as well.