In Memory - Memory optimized tables in SQL Server 2014 and SQL Server 2016

One of the biggest feature enhancement for SQL Server 2014 is In Memory Table.
Which is a different table structure compared to traditional 8 KB/Page structure .This is the one of the limitation of  SQL Server that cant process more requests whether there is more memory or faster disk available.


Design concerns (SQL Server 2014 ) 

Table construct
Fixed ; No ALTER TABLE
No LOB data types
No Constraints support (Only Primary Key
No Identity or calculated column or CLR
Data and Table Size
Maximum size Schema and Data :512 GB

SQL Server 2016 Enhancements on in Memory OLTP
Supports Full schema changes ADD/ALTER/DROP  for Column and constraints
MARS Support
ADD/DROP index Support
MERGE Operation
Improvements to DMVs sys.dm_db_xtp_checkpoint_stats ,sys.dm_db_xtp_checkpoint_files 
Support for Transparent Data Encryption.
Operational Analytics - Columnstore index on in memory table

Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables.

Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure. In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile memory-optimized tables

Other design enhancement in SQL Server 2016 Management studio for Memory optimized table
Memory Optimization Advisor
Native compilation advisor

In-Memory OLTP provides full durability for memory-optimized tables. When a transaction happens to an in memory table SQL Server guarantees that the changes are permanent (will survive a database restart), provided the underlying storage is available.


When to you use In Memory table ?
increase transnational throughput
You want to improve data ingestion
Reduce latency
Remove latency spike
If you want to store data for staging state

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Scenario

After update SQL Server 2012 or 2014 with new service pack then SQL Server stop working , does not start service with  following event log

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 3906, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

My Environment

SQL Server 2014 Enterprise Edition  running on Windows 2012 R2 .

Reason

In my case the SSISDB was a member of availability group database, The instance which I was updated was secondary. If you face issue the following step with help you to solve this

Start SQL Server service with Trace Flag 902:
 > Net Start MSSQL$InstanceName /T902
Remove SSISB From availability group (do this from primary )
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL12.MSSQL$Instancename \MSSQL\Install
Stop SQL Server services: 
 > Net Stop MSSQL$InstanceName
Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group

Creating AlwaysOn Availability Group - SQL Server 2016 -Step By Step

AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.

Prerequisites

• Ensure that the system is not a domain controller.
• Ensure that each computer is running Windows Server 2012 or later versions.
• Ensure that each computer is a node in a Windows Server Failover Clustering (WSFC) cluster.

My Environment 

OS - Windows 2012 R2

SQL Server - SQL Server 2016 Enterprise Edition (Eval)

Fail-over Cluster Installation

First we need to add the Windows Fail-over Cluster Feature to all the nodes running the SQL Server instances that we will configure as replicas.

Open the Server Manager  => select Add roles and features 

Click Next till Select Features dialog box.

    Select the Failover Clustering checkbox

Click Install to install the Failover Clustering feature.

Windows Failover Clustering Configuration for AlwaysOn Availability Groups

Open Server Manager and select "Fail over Cluster Manager"

Select Fail over Cluster Manager and Select the Option Create Cluster OR 

You can Right Click on the Fail over Cluster manager and select Create Cluster menu

You will Create Cluster Wizard

 Add the Nodes you want be in the Cluster and then click next

You will be getting a validation warning , Select Yes to Validate the Cluster nodes

 Select the option -  Run all tests

NOTE: The Cluster Validation Wizard is expected to return several Warning messages, especially if you will not be using shared storage. other than that if you find any Error messages, you need to fix  prior to creating the Windows Server Failover Cluster.

In the Access Point for Administering the Cluster dialog box, enter the Cluster name and virtual IP address for Windows Server Failover Cluster , then Click next.

Verify that the configuration is successful in Summary.

  Configure Cluster Quorum Settings

Quorum is that it is a configuration database for the cluster and is stored on a shared location, accessible to all of the nodes in a cluster.

In Case of Even number of nodes (but not a multi-site cluster) Node and Disk Majority Qurum configuration is recommended . If you dont have no shared storage Node and File Share Majority is recommended. Here it will be configuring a FileShare Witness quorum


It is recommended that you configure the quorum size to be 500 MB; this size is the minimum required for an efficient NTFS partition. Larger sizes are allowable but are not currently needed.

Click on Your Cluster Select Configure Cluster Quorum Setting from the More action menu

 In the Select Quorum Witness page, select the Configure a file share witness option. Click Next.

Type path of the file share that you want to use in the File Share Path: text box. Click Next.
It is recommended to have the share folder on a different node than the node participating on Cluster

 In the Confirmation page, click Next.

In the Summary page, click Finish.

Enable AlwaysOn Availability Groups Feature on  SQL Server 2016

we can now proceed with enabling the AlwaysOn Availability Groups feature in SQL Server 2016. This is possible after install and configuring the Windows Fail-over Cluster on all the nodes

Open SQL Server Configuration Manager - > SQL Server Properties - [SQL Server (SQLAG01)]

In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK.
 
Restart the SQL Server service.

Configure SQL Server 2016 AlwaysOn Availability Groups

 Go to Management Studio, right click Availability Groups and click New Availability Group Wizard.

Specify Availability Group Name .  my group name SQLAVG2016 then click Next.

Choose Database

Here you can see whether the db meets the prerequisites

• Database should be in full recovery mode. 
• You should make a full backup to add the DB into the Availability Group

Specify Replicas .
This page applies to the New Availability Group Wizard and the Add Replica to Availability Group Wizard of SQL Server 2016

If a server instance that you to use to host a secondary replica is not listed by the Availability Replicas grid, click the Add Replica button.

Add Azure Replica button to create virtual machines with secondary replicas in Windows Azure.

Adding Secondary Replica

Endpoints

Use this tab to verify any existing database mirroring endpoints and also, if this endpoint is lacking on a server instance whose service accounts use Windows Authentication, to create the endpoint automatically.

Backup Preference

Use this tab to specify your backup preference for the availability group as a whole and your backup priorities for the individual availability replicas.

Listener

An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. You point applications to the listener (which is registered with DNS) and directs traffic in the AG

Select Data Synchronization

Use the Always On Select Initial Data Synchronization page to indicate your preference for initial data synchronization of new secondary databases. This page is shared by three wizards—the New Availability Group Wizard, the Add Replica to Availability Group Wizard, and the Add Database to Availability Group Wizard.

The possible choices include Full, Join only, or Skip initial data synchronization. Before you select Full or Join only ensure that your environment meets the prerequisites.

In My I have choose Full 

For each primary database, the Full option performs several operations in one workflow: create a full and log backup of the primary database, create the corresponding secondary databases by restoring these backups on every server instance that is hosting a secondary replica, and join each secondary database to availability group.

Select this option only if your environment meets the following prerequisites for using full initial data synchronization, and you want the wizard to automatically start data synchronization

 

 

AlWaysOn Availability Group - Create New Availability Group Error - Checking for compatibility of the database file location on the Server Instance that hosts secondary replica (Microsoft.SqlServer.Management.HadrModel)

This is one of common error message While Creating a New Availability Group   Here is full error I received when I attempted to Create a New Availability Group.

Here is my environment

OS - Windows Server 2012 R2
SQL Server - SQL 2016 Enterprise Edition ( The same solution will solve for SQL 2014/2012 too)
Primary Server - SQLAG01
Secondary Server - SQLAG02


We used ‘Full’ option in Select Initial Data Synchronization Page.  The error says Primary replica is trying to search for data folder location in secondary and as a result they don’t match, and failed.

If you have choosen Full option  You have to create same file location on both the instances
e,g: Create Same Data path on both instances.

Data Path on SQLAG01 -  "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\MSSQL\DATA\"

Data Path on SQLAG02- "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\MSSQL\DATA\"

other wise choose other options for Initial Data synchronization ' Join Only' and manually restore the database on secondary

Add Databases to AlwaysOn Availability Group Error - The operating system returned the error '5(Access is denied.)

This is one of common error message While Creating a New Availability Group or Adding a Database to an availability group. Here is full error I received when I attempted to Add a database to an Availability Group

--------------------------------------------------------------------------------------------------------------------------------------------------------

Restore Database AdventureWorks fail (Microsoft.SqlServer.Management.HadrModel)

ADDITIONAL INFORMATION:

Restore failed for Server 'CGSQL02\SQLAG02'.  (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' 

while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLAG01\MSSQL\DATA\AdventureWorks.mdf'. (Microsoft.SqlServer.Smo)

--------------------------------------------------------------------------------------------------------------------------------------------------------

Solution

Here is my environment

OS - Windows Server 2012 R2
SQL Server - SQL 2016 Enterprise Edition ( The same solution will solve for SQL 2014/2012 too)
Primary Server - SQLAG01 

Secondary Server - SQLAG02

SQL Installation was done on Default Paths  

Data Path on SQLAG01 -  "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\MSSQL\DATA\"

Data Path on SQLAG02- "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG02\MSSQL\DATA\"

SQL Service is running under  domain acccount ( condoso/sql_service )

it is a problem with the Permission of the specified folder to the user account that SQL Server service is running. The Path Which you have created on Secondary instance to perform synchronization of secondary server databases using backup/restore. in my scenario ( "C:\Program Files\Microsoft SQL  Server\MSSQL13.SQLAG01\"  Which is the Path Created for Back/restore on Secondary node ) , This path should have Full Permission on SQL Service Account. condoso\sql_service

AlWays on Availability group and Fileshare witness Quorum , Windows 2012 & SQL Server 2012

AlWays on Availability group and Fileshare witness Quorum

What is AlWaysOn Availability Group

AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.

What is a Quorum in Failover Cluster

Quorum is that it is a configuration database for the cluster and is stored on a shared location, accessible to all of the nodes in a cluster.

Different Type of Quorum

Node Majority (recommended for clusters with an odd number of nodes)

Can sustain failures of half the nodes (rounding up) minus one. For example, a seven node cluster can sustain three node failures.

Node and Disk Majority (recommended for clusters with an even number of nodes)

Can sustain failures of half the nodes (rounding up) if the disk witness remains online. For example, a six node cluster in which the disk witness is online could sustain three node failures.

Can sustain failures of half the nodes (rounding up) minus one if the disk witness goes offline or fails. For example, a six node cluster with a failed disk witness could sustain two (3-1=2) node failures.


Node and File Share Majority (for clusters with special configurations)

Works in a similar way to Node and Disk Majority, but instead of a disk witness, this cluster uses a file share witness.

Note that if you use Node and File Share Majority, at least one of the available cluster nodes must contain a current copy of the cluster configuration before you can start the cluster. Otherwise, you must force the starting of the cluster through a particular node. For more information, see “Additional considerations” in Start or Stop the Cluster Service on a Cluster Node.

No Majority: Disk Only (not recommended)

Can sustain failures of all nodes except one (if the disk is online). However, this configuration is not recommended because the disk might be a single point of failure.


Windows 2012:


It is continued to improve quorum On Windows 2012.  In a majority node set, as soon as you lose a majority of your votes, the remaining nodes go offline. So for example, if you have a cluster with FIVE nodes, if you were to lose THREE nodes the remaining nodes will go offline, even though there are three nodes remaining. This might not be exactly what you want to happen. So in Windows Server 2012, Microsoft introduced Dynamic Quorum.
The quorum models are the same in Windows 2012 like Windows 2008 R2, however there are enhancements with Node Vote Assignment and there is a new concept called as “Dynamic Quorum Configuration”.


Dynamic Quorum Configuration:

Starting Windows Server 2012, there is a new concept called dynamic quorum configuration. This concept is based on node vote assignment, specifically it relies on the fact that cluster can manage node vote assignment automatically based on the state of each node.  Votes are automatically removed from nodes that leave active cluster membership, and a vote is automatically assigned when a node rejoins the cluster. By default, dynamic quorum management is enabled.

In above scenario of five node cluster When node one went offline, we have four node cluster. When node two went offline, I would then have a THREE node cluster, and so on. In reality, It continues to lose cluster nodes one by one, all the way down to a two node cluster and still remain online. And, if I had configured a witness (Disk or File Share) I could actually go all the way down to a single node and still remain online.

However, best practice in Windows Server 2012 and earlier is to only configure a witness on clusters that have an even number of nodes. Here is an example of why you wouldn’t configure a witness in cluster with an odd number of nodes. Fore example you have a three node cluster and decide to configure a witness (Disk or File Share). If you happen to lose the Witness and a single node, the remaining two nodes will go offline because they only have two out of four votes, not a majority. So adding a witness to a cluster that already has an odd number of votes actually gives you less availability than if you didn’t add a witness at all.

That leads Windows Server 2012 R2 and Dynamic Witness. In the scenario above on Windows Server 2012,  However, on Windows Server 2012 R2 has changed to ALWAYS configure a witness, regardless of the number of nodes. The cluster itself will determine whether the witness actually has a vote, depending upon how many cluster nodes are available at any given time. So in the example above with five node cluster, still configure a witness. Under normal circumstances when all FIVE nodes are running, witness would not have a vote. However, as a started losing cluster nodes, the witness vote would be turned on or off as needed to ensure that I always had an odd number of votes in my cluster.

How can I decide Which quorum is  good and What is the recommended size of the File Share/Disk Witness Quorum ?

In Case of Even number of nodes (but not a multi-site cluster) Node and Disk Majority Qurum configuration is recommended . If you dont have no shared storage Node and File Share Majority is recommended. Requires One quorum disk (or File Share)  per cluster.

It is recommended that you configure the quorum disk size to be 500 MB; this size is the minimum required for an efficient NTFS partition. Larger disk sizes are allowable but are not currently needed.


Reference
https://blogs.msdn.microsoft.com/microsoft_press/2014/04/28/from-the-mvps-understanding-the-windows-server-failover-cluster-quorum-in-windows-server-2012-r2/

SQL Server Transact SQL - TSQL - TOP WITH TIES

TOP Keyword is commonly used to get the Top Rows from a Result. SQL Server has option use it 'WITH TIES' to get similar rows together

WITH TIES
Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.

SELECT TOP (5) productid, unitprice FROM Production.Products
WHERE categoryid = 1 ORDER BY unitprice DESC;

You get the following result set.


productid                unitprice
----------- ---------------------
38                           263.50
43                             46.00
2                               19.00
1                               18.00
35                             18.00

If you want provide solutions to turn this query into a deterministic one—one solution that includes ties and another that breaks the ties. First, address the version that includes all ties by using the WITH TIES option. Add this option to the query, as follows.

SELECT TOP (5) WITH TIES productid, unitprice FROM Production.Products
WHERE categoryid = 1 ORDER BY unitprice DESC;

You get the following output, which includes ties.
productid                unitprice
----------- ---------------------
38                           263.50
43                             46.00
2                               19.00
1                               18.00
35                             18.00
76                             18.00