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
There is now a level 0.... 古人學問無遺力,少壯工夫老始成,紙上得來終覺淺,絕知此事要躬行.
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
Below is the walk through for how to add another replica to the existing high availability group.
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
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.
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.
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.
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
This is the my note to install SQL Server 2014 on the Cluster.
Build windows failover cluster : Please refer here.
select the New SQL server failover cluster installation
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.
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
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 .
After install MDS, click the SQL Server 2014 Master Data Service Configuration
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
http://www.sqlpanda.com/2014/10/sql2014-in-memory-oltp-1.html
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.
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:
Below performance testing is done compare
-- ===============================================================================================================
-- 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 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'
-- ===============================================================================================================
-- 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'
SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates