9 How to create copy of the Windows Azure Database

To create the copy of the database in Azure, we don’t need to do backup and restore .It is new syntax with AS COPY OF

create database AzureDB3 as copy of AzureDB1

Reference

http://msdn.microsoft.com/en-us/library/dn268335.aspx

2 SQL2014:AlwaysOn High availability group-Readable secondary

With AlwaysOn availability group, we can create the readable secondary to offload the query load on the primary. This post is the walk through how to set up the readable seconday.

3 SQL2014:AlwaysOn High availability group-Add additional replica

Below is the walk through for how to add another replica to the existing high availability group.

prerequisites

Back up the primary database and log , then restore to the new replica with NORECOVERY

 

From SSMs Availability replicas –> Add Replica

Connect to the existing replica

Above steps can be replaced with below script . However, the scripts needs to be executed under SQLCMD mode.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect SGC8\SGC8SQL2014

USE [master]

GO

CREATE LOGIN [Stargate\sqlprodid] FROM WINDOWS

GO

:Connect SGC4,8001

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\sqlprodid]

GO

:Connect SGC5\SGC5SQL2014

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\SQLPRODID]

GO

:Connect SGC8\SGC8SQL2014

USE [master]

GO

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Stargate\sqlprodid]

GO

:Connect SGC8\SGC8SQL2014

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect SGC4,8001

USE [master]

GO

ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo]
ADD REPLICA ON N'SGC8\SGC8SQL2014' WITH (ENDPOINT_URL = N'TCP://SGC8.stargate.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect SGC8\SGC8SQL2014

ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo] JOIN;

GO

:Connect SGC8\SGC8SQL2014

-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes

if (serverproperty('IsHadrEnabled') = 1)
    and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
    and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'pandaAlwaysOnDemo'
    select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
    while @conn <> 1 and @count > 0
    begin
        set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
        if @conn = 1
        begin
            -- exit loop when the replica is connected, or if the query cannot find the replica status
            break
        end
        waitfor delay '00:00:10'
        set @count = @count - 1
    end
end
end try
begin catch
    -- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [AdventureWorks2014] SET HADR AVAILABILITY GROUP = [pandaAlwaysOnDemo];

GO

GO

3 SQL2014: Create AlwaysOn High availability group

Below is my note to create Always on High availability group in SQL 2014.

All SQL Server participate alwayson availability group must in the same WSFC.

Enhancement for SQL2014 AlwaysOn

• Support Azure as replica.
• The maximum number of secondary replicas is increased from 4 to 8.
• When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
• A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
• The following DMVs were enhanced and now return FCI information: sys.dm_hadr_cluster, sys.dm_hadr_cluster_members, and sys.dm_hadr_cluster_networks.

Environment

• SQL Server standalone 1: SGC4,8001
• SQL Server standalone 2: SGC5,8001

5 How to fix the “The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. “ error

During creation the Availability group in SQL 2014, I have encounter the below error when try to add the 2nd replica.  

The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) node. (Microsoft.SqlServer.Management.HadrTasks)

The local node is not part of quorum and is therefore unable to process this operation. This may be due to one of the following reasons:

•   The local node is not able to communicate with the WSFC cluster.

•   No quorum set across the WSFC cluster.

This prompt me that maybe the 2nd instance does not know itself its HADR enable. So I qery sys.dm_hadr_cluster_members and sys.dm_hadr_cluster for both nodes.

On node1, it does return correct information. On node2, the result is empty.

SELECT *

FROM sys.dm_hadr_cluster_members;

Go

select * from sys.dm_hadr_cluster

The way to fix it is disable the HADR from SQL Server configuration manager . Bounce SQL Server and SQL agent. Rentable HADR and bounce SQL server and SQL agent.

The issue was resolved after 2nd bounce. 

3 Configure Single Node SQL Cluster after SQL Server installation.

Single node cluster is not recommend HADR solution. One of the reason I do this is SQL Server AlwaysOn require the “Windows failover Cluster name” for the SQL Server. 

Below screenshot from one of the standalone installation. The host does not have failover cluster feature installed, the SQL Server instance property is complete gray out

Add Failover Clustering feature . This would not cause down time on the Windows host and SQL server instance.

1 SSMS: Multiple server query

We can configure SSMS to run the query against multiple server simultaneously.

In SSMS, View –> Registered Servers

1 SQL2014: Explore SQL Server Failover Cluster

• Explore DMV for Cluster
• Change IP address
• Configure static port number
• Add Disk to SQL Server Cluster failover instance
• Configure failover policy

Explore DMV for Cluster

SELECT @@VERSION

GO

SELECT

    SERVERPROPERTY('MachineName') AS [ServerName], 

    SERVERPROPERTY('ServerName') AS [ServerInstanceName], 

    SERVERPROPERTY('InstanceName') AS [Instance], 

    SERVERPROPERTY('Edition') AS [Edition],

    SERVERPROPERTY('ProductVersion') AS [ProductVersion], 

    SERVERPROPERTY('ProductLevel') AS [ProductLevel], 

    Left(@@Version, Charindex('-', @@version) - 2) As VersionName,

    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') As CurrentNode

GO

Select * from sys.dm_os_cluster_nodes

GO

select * from sys.dm_io_cluster_shared_drives

GO

select * from sys.dm_os_cluster_properties

GO

0 SQL2014: Create two nodes SQL Server 2014 AlwaysOn Failover Cluster Instance

This is the my note to install SQL Server 2014 on the Cluster.

prerequisites

Build windows failover cluster : Please refer here.

Install SQL Server 2014 on the node 1

select the New SQL server failover cluster installation

0 How to resolve network binding error

While install the SQLServer on the Cluster, there is a warming for Network binding order .

0 Windows 2008R2: Create Microsoft Failover Cluster

This is my note for how to create the 2 nodes failover cluster.

Enable Failover Cluster from GUI

Perform below steps on N1

0 How to solve “No Targets available for Login using Quick Connect.” in iSCSI

I got the error No Targets available for Login using Quick Connect while connect through  iSCSI.

1 RSAT on Windows server 2008R2 ?

I am  building the Windows Cluster for my own lab. One of the step I want to achieve is to manage the Cluster from Client which is a Windows 2008R2.

Since I don’t have Failover feature install on my client, I would not have Failover Cluster manager . So I am looking for RSAT (Remote Server Administration Tools Pack).

It turns out. it is easier than I thought, all I need is to enable “Remote Server Administrator Tools” on the Windows 2008R2.

1 Data Quality Services

MSFT introduce DQS from SQL 2012. The main goal is to help to clean the data easily.

After I install the DQS service from SQL Server installation media. I try to connect to DQS through the DQS client but get the error below.

This is because I didn’t configure it.

Go to start –> Microsoft SQL Server 2014 –> Data Quality Services –> SQL Server 2014 Data Quality Server Installer

This will execute the installation process

After the installation, three databases will be created.

Then we can use DQS Client to connect

Below screenshot is taken from Microsoft Virtual Lab : Exploring Master Data Services and Data Quality Services in SQL Server 2014 and Excel 2013

Reference

http://technet.microsoft.com/en-us/sqlserver/hh780961

0 Master Data Service

Microsoft introduce MDS since SQL 2008R2 . The goal for MDS is to create the Golden Copy for the data and let the Business analysis has ability to correct the value by themselves without DBA involvement .

Configure the MDS

After install MDS, click the SQL Server 2014 Master Data Service Configuration

0 Management Data Warehouse

MDW provide the new report that can help us to decide what table and store procedure that will be benefit most from migration to in-memory OLTP.

Start SQL Agent

0 SQL2014: In Memory OLTP (6): Memory optimization advisor and report

In order to help developer & DBA migrate to memory optimized table, SQL Server come with

• Table Memory Optimization advisor
• Native Compilation Advisor

Table Memory Optimization advisor

Click the data and click Memory Optimization advisor

0 T-SQL: How to check FK constraint and remove them

Here is the T-SQL to check the FK constraint and display its name and the parent table.

select name,OBJECT_NAME(parent_object_id) from sys.objects where object_id in 

(   select fk.constraint_object_id from sys.foreign_key_columns as fk

    where fk.referenced_object_id = 

        (select object_id from sys.tables where name = 'region')

)

Then we can use alter table statement  to remove it from parent table.

alter table nation drop nation_region_fk

0 SQL2014: In Memory OLTP (5) Compare insert operation between disk table and memory optimized table

prerequisites

• Use HammerDB to create TPC-H Database with Scale Factor 1 test DB.
• Create the memory optimize table

http://www.sqlpanda.com/2014/10/sql2014-in-memory-oltp-1.html 

Test

Insert 1500000 record to the disk space table takes longer than memory optimized table. Disk base table not only needs to scan the source table but also the target table.

0 SQL2014: In Memory OLTP (4) Natively Compiled Store procedure

Another enhancement for the In-Memory OLTP is that we can compile the store procedure with native assembly code so SQL server do not need to parse the query plan in the runtime. 

Native Store procedure has some limitations:

• Only can access in-memory tables
• EXECUTE AS CALLER is not supported
• 2-part identifier required ( schema.table )
• Limit T-SQL support:
• No Distinct
• No CTE
• No Ranking functions
• No Exists/in
• No sub query

Below performance testing is done compare

• Disk-based table and interpreted Transact-SQL
• Disk-based table and store procedure
• Memory-optimized table w/hash index and interpreted Transact-SQL
• Memory-optimized table w/hash index and interpreted SP
• Memory-optimized table w/hash index and Native SP

 

create the test table

-- ===============================================================================================================

-- Create disk base tables 

-- ===============================================================================================================

CREATE TABLE [product_disk]

(

[key] INT NOT NULL PRIMARY KEY,

[description] NCHAR(48) NOT NULL

)

GO

-- ===============================================================================================================

-- Create memory-optimized tables 

-- ===============================================================================================================

CREATE TABLE product_mem

(

[key] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

[description] NCHAR(48) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)

GO

Create store Procedure

--- Create sp to insert disk base table

CREATE PROCEDURE sp_insert_product_disk

       @rowcount int,

       @description nchar(48)

AS

       DECLARE @key INT = 1

       WHILE @key <= @rowcount

       BEGIN

                 INSERT INTO [dbo].product_disk VALUES (@key, @description)

                 SET @key += 1

       END

GO

--- create sp to insert mem table

CREATE PROCEDURE sp_insert_product_mem

       @rowcount int,

       @description nchar(48)

AS

       DECLARE @key INT = 1

       WHILE @key <= @rowcount

       BEGIN

                 INSERT INTO [dbo].product_mem VALUES (@key, @description)

                 SET @key += 1

       END

GO

--- create native sp to insert into mem table

CREATE PROCEDURE sp_native_insert_product_mem

       @rowcount int,

       @description nchar(48)

WITH NATIVE_COMPILATION, SCHEMABINDING

,EXECUTE AS OWNER  -- EXECUTE AS CALLER is not supported 

AS 

 BEGIN ATOMIC -- Either entire sp complete succesfully or fail

 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

       DECLARE @key INT = 1

       WHILE @key <= @rowcount

       BEGIN

                 INSERT INTO [dbo].product_mem VALUES (@key, @description)

                 SET @key += 1

       END

END

GO

After native store procedure, we can find the dll is being place in the disk folder .

SELECT name, DESCRIPTION FROM sys.dm_os_loaded_modules

WHERE name like '%xtp_p_' + CONVERT(varchar, db_id()) + '_' + CONVERT(varchar, OBJECT_ID('dbo.sp_native_insert_product_mem')) + '.dll'

Performance testing

-- ===============================================================================================================

-- Inserting data into disk-based tables using interpreted T-SQL

-- ===============================================================================================================

-- Delete data if exists

DELETE FROM [product_disk]

GO 

DECLARE @starttime DATETIME2 = SYSDATETIME(),

        @timems INT

DECLARE @key INT = 1

DECLARE @rowcount INT = 100000

DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'

BEGIN TRAN

WHILE @key <= @ROWCOUNT

BEGIN

       INSERT INTO [product_disk] VALUES (@key, @description)

       SET @key += 1

END

COMMIT

SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())

SELECT 'Disk-based table and interpreted Transact-SQL: ' + CAST(@timems AS VARCHAR(10)) + ' ms'

-- ===============================================================================================================

-- Inserting data into disk-based tables using interpreted stored procedure

-- ===============================================================================================================

DELETE FROM [product_disk]

GO 

SET STATISTICS TIME OFF

DECLARE @starttime DATETIME2 = SYSDATETIME(),

        @timems INT

DECLARE @key INT = 1

DECLARE @rowcount INT = 100000

DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'

SET @starttime = SYSDATETIME()

EXEC sp_insert_product_disk @rowcount, @description

SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())

SELECT 'Disk-based table and interpreted stored procedure: ' + CAST(@timems AS VARCHAR(10)) + ' ms'

-- ===============================================================================================================

-- Inserting data into memory-optimized table using interpreted T-SQL

-- ===============================================================================================================

--Delete data if exists

DELETE FROM [dbo].product_mem

GO

DECLARE @starttime DATETIME2 = SYSDATETIME(),

        @timems INT

DECLARE @key INT = 1

DECLARE @rowcount INT = 100000

DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'

SET @key = 1

SET @starttime = sysdatetime()

BEGIN TRAN

WHILE @key <= @rowcount

BEGIN

       INSERT INTO product_mem VALUES (@key, @description)

       SET @key += 1

END

COMMIT

SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())

SELECT 'Memory-optimized table w/hash index and interpreted Transact-SQL: ' + CAST(@timems AS VARCHAR(10)) + ' ms'

 -- ===============================================================================================================

-- Inserting data into memory-optimized table using interpreted stored procedure

-- ===============================================================================================================

 --Delete data if exists

DELETE FROM [dbo].product_mem

GO

DECLARE @starttime DATETIME2 = SYSDATETIME(),

        @timems INT

DECLARE @rowcount INT = 100000

DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'

SET @starttime = sysdatetime()

EXEC [dbo].sp_insert_product_mem @rowcount, @description

SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())

SELECT 'Memory-optimized table w/hash index and interpreted SP:' + CAST(@timems AS VARCHAR(10)) + ' ms'

 -- ===============================================================================================================

-- Inserting data into memory-optimized table using native  stored procedure

-- ===============================================================================================================

 --Delete data if exists

DELETE FROM [dbo].product_mem

GO

DECLARE @starttime DATETIME2 = SYSDATETIME(),

        @timems INT

DECLARE @rowcount INT = 100000

DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'

SET @starttime = sysdatetime()

EXEC [dbo].sp_native_insert_product_mem @rowcount, @description

SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())

SELECT 'Memory-optimized table w/hash index and interpreted SP:' + CAST(@timems AS VARCHAR(10)) + ' ms'