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
Friday, October 31, 2014
Monday, October 27, 2014
Sunday, October 26, 2014
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.
CREATE LOGIN [Stargate\sqlprodid] FROM WINDOWS
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\sqlprodid]
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\SQLPRODID]
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Stargate\sqlprodid]
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
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));
ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo] JOIN;
-- Wait for the replica to start communicating
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)
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
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
-- exit loop when the replica is connected, or if the query cannot find the replica status
waitfor delay '00:00:10'
set @count = @count - 1
-- If the wait loop fails, do not stop execution of the alter database statement
ALTER DATABASE [AdventureWorks2014] SET HADR AVAILABILITY GROUP = [pandaAlwaysOnDemo];
Wednesday, October 22, 2014
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.
- SQL Server standalone 1: SGC4,8001
- SQL Server standalone 2: SGC5,8001
3 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.
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.
Tuesday, October 21, 2014
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.
Monday, October 20, 2014
This is the my note to install SQL Server 2014 on the Cluster.
Build windows failover cluster : Please refer here.
Install SQL Server 2014 on the node 1
select the New SQL server failover cluster installation
Sunday, October 19, 2014
Wednesday, October 15, 2014
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
Tuesday, October 14, 2014
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
Monday, October 13, 2014
0 SQL2014: In Memory OLTP (5) Compare insert operation between disk table and memory optimized table
- Use HammerDB to create TPC-H Database with Scale Factor 1 test DB.
- Create the memory optimize table
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:
- 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 store Procedure
After native store procedure, we can find the dll is being place in the disk folder .