How to create ISCSI shared disk in windows server for ESX VSphere environment.

This my first blog for ESX/VSphere where the requirement was to create ISCSI data store for HA & DRS (High Availability & Distributed Resource Scheduler).  We created storage from windows server instead of having a dedicated storage appliance/VM. The purpose of such configuration is only for learning & testing the HA/DRS activity.

This configuration is designed on windows 2012 R2 with ESX 6.0.

On windows server, install ‘File and ISCSI services’ and make sure ‘ISCSI Target server’ is selected.

1.jpg

Once the role is installed then select ‘File and storage services’ feature and then ‘ISCSI’.

Right click on empty space and select ‘New ISCSI Virtual Disk’ as shown in above example. In my screenshot one of the volume is created and I am creating a new one.

2.jpg

Now select the partition where you wish to place the Virtual Disk.

3.jpg

Give the appropriate Name and Description and confirm the path of SCSI target.

4.jpg

Give the appropriate size & select type of disk as mentioned below. In my example I have selected ‘Dynamically expending’ as I don’t need to worry about space.

5.jpg

You need to create target that will be the list of ESX servers participating in HA/DRS and access VHD using ISCSI protocol.

6.jpg

In my example, I have used DNS name of ESX servers as initiator but it can also be access using IQN, IP address OR MAC address.

16.jpg

In following screenshot, there are two ESX servers are added but you have even more.

8.jpg

Select type of authentication, I am leaving it blank to avoid confusion.

9.jpg

In next page, you can confirm the setting you have selected and the result of different section will be available for you.10.jpg

Now the shared ISCSI disk is ready, you can add this in ESX server using VSphere console. Select ESX server \ Manage \Storage \ Storage Adaptor \Target \ Add Target.

11.jpg

Rescan storage so all newly attache drives are visible.

12.jpg

Now you should be able to see the path of all available SCSI share disk.

13.jpg

SCSI disk will also be available as storage devices.

14.jpg

In your windows server, you would notice the target status as ‘connected’.

15.jpg

Advertisements

#disaster-recovery, #esx, #scsi, #storage, #vmware

Introduction to AlwaysOn Availability Group (AAG) and configuration.

The ‘Always On Availability Groups’ feature is a high-availability and disaster-recovery solution that combines the capabilities of ‘Fail-Over Clustering’, ‘Database Mirroring’, ‘Log shippingfor group of SQL databases. 

Key Components of AAG.

Availability Group: A container for a set of databases, availability databases, that fail over together.

Availability database: A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to four read-only copies (secondary databases).

Primary Replica (OR Database): The read-write copy of an availability database,  also responsible for sending transaction log records every secondary replica.

Secondary Replica (OR Database): A read-only OR standalone copy of an availability database and serves as a potential fail over targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.

Listener: A server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.

Quorum: Collector of votes among nodes within the Availability Group to determine if state is healthy.

Witness (File Share or Disk): needed in cases where there are an even number of nodes in Availability Group for tie-breaker

Availability modes

Synchronous Commit Mode: Prior to transaction commit, secondary must confirm the log has been hardened; Both Manual & Automatic failovers are available with Synchronous; COMMITTED TRANSACTIONS ARE FULLY PROTECTED, INCREASED TRANSACTAIONAL LATENCY

Asynchronous Commit Mode: Primary commits without waiting for secondary verification of a hardened log; Only Manual failovers are available with Asychronous; MINIMAL TRANSACTIONAL LATENCY, POSSIBLE LOSS OF DATA

Setting up AAG.

AlwaysOn Availability Group (AAG) can be configured either using wizard available in SSMS (SQL Server Management Studio) OR SQL queries.

Followings steps are tested on SQL 2016 installed on Windows 2012 R2 Machine.We have 3 SQL servers and SQL Server service running with domain user credential (Domain\sqlsvc).

 AAG configuration using wizard (Slideshow )

This slideshow requires JavaScript.

Setting up MSCS Cluster

Open Powershell on one of domain joined machine with Domain Admin credential and Install ‘Fail-over clustering’ windows feature on all SQL server.

$servers = "sql1","sql2","sql3"
foreach ($server in $servers)
{
Install-WindowsFeature -Computername $server –Name File-Services, Failover-Clustering –IncludeManagementTools
Restart-computer $servers -Force
}

Create a new cluster.

New-Cluster -Name MyCluster -Node SQL1, SQL2, SQL3 -StaticAddress 192.168.2.151 –NoStorage

Open PowerShell in one of the SQL Node using Domain Admin credential and enable AlwaysOn for all SQL servers.

$servers = "sql1","sql2","sql3"
foreach ($server in $servers)
{
Enable-SqlAlwaysOn -ServerInstance $server -Force
}

Creating AAG

Following queries need to run in SQLCMD mode (Query >SQLCMD mode)

:Connect SQL1
USE [master]
CREATE DATABASE DB1
CREATE DATABASE DB2
GO

/* Make sure you have share location for backup.*/
BACKUP DATABASE [DB1] TO DISK = N'\\FileServer\SQL2016\Backup\AAG\DB1Backup.bak'
BACKUP DATABASE [DB2] TO DISK = N'\\FileServer\SQL2016\Backup\AAG\DB2Backup.bak'
GO 

/* You may see warning “Cannot grant, deny permissions to sa, dbo, entity owner, information schema,
sys, or yourself.” Ignore if you are running this script using SQL service account. 
*/ 
/*Creating Endpoints on SQL1 */
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\sqlsvc]
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GO 

:Connect SQL2
/*Creating Endpoints on SQL2 */
USE [master]
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\sqlsvc]
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GO

:Connect SQL1
/*Creating AAG */ 
USE [master]
CREATE AVAILABILITY GROUP [CompanyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF, DTC_SUPPORT = NONE) FOR DATABASE [DB1], [DB2] REPLICA ON 
N'SQL1' WITH (ENDPOINT_URL = N'TCP://SQL1.MYDOMAIN.LOCAL:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
N'SQL2' WITH (ENDPOINT_URL = N'TCP://SQL2.MYDOMAIN.LOCAL:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO 

/*Adding Listener */
ALTER AVAILABILITY GROUP [CompanyAG] 
ADD LISTENER N'DBaccess' (WITH IP ((N'192.168.2.160', N'255.255.255.0')), PORT=1433);
GO

:Connect SQL2
/*Adding SQL2 as AAG partners */
ALTER AVAILABILITY GROUP [CompanyAG] JOIN;

Adding a new node (SQL) into Existing AAG.

/* Addding a node to existing AAG. */
:Connect SQL3
USE [master]
CREATE ENDPOINT [Hadr_endpoint] 
       AS TCP (LISTENER_PORT = 5022)
       FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\sqlsvc]
GO

:Connect SQL1
USE [master]
ALTER AVAILABILITY GROUP [CompanyAG]
ADD REPLICA ON N'SQL3' WITH (ENDPOINT_URL = N'TCP://SQL3.MYDOMAIN.LOCAL:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
BACKUP DATABASE [DB1] TO  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB1.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
BACKUP DATABASE [DB2] TO  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB2.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

:Connect SQL3
ALTER AVAILABILITY GROUP [CompanyAG] JOIN;
RESTORE DATABASE [DB1] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB1.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DB2] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\AAGDB2.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

:Connect SQL3
ALTER DATABASE [DB1] SET HADR AVAILABILITY GROUP = [CompanyAG];
ALTER DATABASE [DB2] SET HADR AVAILABILITY GROUP = [CompanyAG];
GO

Adding a new user database into AAG

/* Create & add new DB into AAG. */ 
:Connect SQL1
CREATE DATABASE DB3
BACKUP DATABASE [DB3] TO DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3Backup.bak'

:Connect SQL1
USE [master]
ALTER AVAILABILITY GROUP [CompanyAG]
ADD DATABASE [DB3];
BACKUP DATABASE [DB3] TO  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3.bak' WITH  COPY_ONLY

:Connect SQL2
RESTORE DATABASE [DB3] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3.bak' WITH  NORECOVERY
ALTER DATABASE [DB3] SET HADR AVAILABILITY GROUP = [CompanyAG];

:Connect SQL3
RESTORE DATABASE [DB3] FROM  DISK = N'\\FILESERVER\SQL2016\Backup\AAG\DB3.bak' WITH  NORECOVERY
ALTER DATABASE [DB3] SET HADR AVAILABILITY GROUP = [CompanyAG];

Manual (planned) Failover

-- Manual failover AAG to SQL3
:Connect SQL3
ALTER AVAILABILITY GROUP [CompanyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Delete AAG configuration.

/* Cleanup of AAG, Endpoints & DB*/ 
:Connect SQL3
USE [master]
DROP AVAILABILITY GROUP [CompanyAG];
DROP DATABASE DB1,DB2,DB3
DROP ENDPOINT Hadr_endpoint
GO 
:Connect SQL1
DROP DATABASE DB1,DB2,DB3
DROP ENDPOINT Hadr_endpoint
GO 
:Connect SQL2
DROP DATABASE DB1,DB2,DB3
DROP ENDPOINT Hadr_endpoint
GO

#aag, #alwayson, #availability-group, #disaster-recovery

SQL server database mirroring and practical demonstration.

SQL Server database mirroring is a disaster recovery and high availability technique that involves two or more SQL Server instances on the same or different machines. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Active copy of database is known as ‘Principal’ database & secondary copy is known as ‘Mirrored’ database. SQL Server instance that host principal database known as ‘Primary’ and server that holds mirrored copy known as ‘Secondary’ server.  In special cases, there can be a third SQL Server instance that acts as a Witness.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.

Please note, Microsoft has depreciated this feature and may not be available in SQL 2016 OR later version of SQL. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always-On Availability Groups instead.


Following steps can be used to setup SQL mirroring for a database reside on two different instances. In my test environment, I am using two SQL instance on different machine (SQL1 & SQL2). Version of SQL is 2014 & operating system is 2012 R2.


Verify SQL services running with domain user credential on both servers running.

1mirroring

On the primary SQL server, execute following script one by one.


----A. Create sample database for mirroring.
USE [master]
CREATE DATABASE MirroringDemo
GO

ALTER DATABASE [MirroringDemo] SET RECOVERY FULL
GO

----B. Create table schema.

USE [MirroringDemo]
CREATE TABLE EmployeeCity
(
ID INT IDENTITY(1,1),
NAME VARCHAR (100),
City VARCHAR(100)
)
GO

----C. Insert 100 records
USE [MirroringDemo]
INSERT INTO EmployeeCity
VALUES
('Pradeep', 'Pune')
GO 100

----D. Take database backup.

BACKUP DATABASE [MirroringDemo]
TO  DISK = N'D:\Backup\MirroringDemo.bak'
GO


On Secondary SQL server, execute following script one by one.

----A. Restore sample database.

USE [master]
RESTORE DATABASE [MirroringDemo]
FROM  DISK = N'D:\Backup\MirroringDemo.bak'
WITH   NORECOVERY
GO

-----B. Create mirroring end point (if mirroring endpoint already exists then drop it using query ‘drop endpoint ’

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

 ----C. Create SQL login for service a/c, if login already exists then leave this step.

USE [master]
CREATE LOGIN [domain\sqlservice] FROM
WINDOWS WITH DEFAULT_DATABASE=[master]
GO

 ----D. Assign permission on endpoint to SQL service a/c.

USE [master]
GRANT CONNECT ON ENDPOINT::endpoint_mirroring to [domain\sqlservice]
GO

----E. This is important step, set mirroring partner now. The URL should point to first server. This should always be performed on secondary database first.

ALTER DATABASE [MirroringDemo]
SET PARTNER = 'TCP://SQL1.Domain.LOCAL:5022'
GO


Go back to primary server, execute following script one by one.

-----A. Create mirroring end point (if mirroring endpoint already exists then drop it using query ‘drop endpoint ’

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

----B. Create SQL login for service a/c, if login already exists then leave this step.
USE [master]
CREATE LOGIN [domain\sqlservice] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

 ----C. Assign permission on endpoint to SQL service a/c.

USE [master]
GRANT CONNECT ON ENDPOINT::endpoint_mirroring to [domain\sqlservice]
GO

----D. Set mirroring partner now. The URL should point to Second server.

ALTER DATABASE [MirroringDemo]
SET PARTNER = 'TCP://SQL2.EV.LOCAL:5022'
GO


The database mirroring is ready now. The database will now be visible like below.


2mirroring.jpg


SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes. By default, SQL mirroring works in high safety mode.

High safety without automatic fail-over – Transaction logs are written and committed on the principal and mirror databases synchronously. This might produce delay and slower operation because transaction log must be committed on both databases before considering it secure. This does not provide automatic fail-over and DBA needs to manually change mirrored copy to principal one when primary server is not available.

High safety with automatic fail-over –This require one additional server called as witness server and works same as high safety mode. If the principal database goes down, the automatic fail-over process initiate by witness server, then mirrored database becomes the principal.

High performance – the asynchronous communication, transaction logs are written and committed on the principal server first, and later sent and committed to the mirror server. Automatic fail-over isn’t possible. When primary server is down then DBA needs to manually force mirrored copy to become principal database, this may cause data loss due to asynchronous communication between database. To reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database

----Change safety mode
USE [master]
ALTER DATABASE [MirroringDemo] SET SAFETY OFF


Use following syntax’s for pause, resume, remove OR fail-over mirrored database.

----Pause mirroring.
ALTER DATABASE [MirroringDemo] SET PARTNER SUSPEND
GO

----Resume mirroring:
ALTER DATABASE [MirroringDemo] SET PARTNER RESUME
GO

----Failover
ALTER DATABASE [MirroringDemo] SET PARTNER FAILOVER
GO

--Remove mirroring
ALTER DATABASE [MirroringDemo] SET PARTNER OFF
GO

My suggestion of every students to learn how things can be configure using SSMS for understanding the concepts. Configuration using SQL query greatly helps for documentation purpose, reapplying same steps and repetitive task. Here is the slide show of SQL mirroring using SSMS.

This slideshow requires JavaScript.


You can monitor the health of database mirroring by a feature called ‘database mirroring monitor.

13mirroring

14mirroring.jpg


SQL DMV to check mirroring details.

SELECT
               db_name(SM.[database_id])         AS [Database Name]
               ,SM.mirroring_state               AS [Mirror State]
               ,SM.mirroring_state_desc          AS [Mirror State]
               ,SM.mirroring_partner_name        AS [Partner Name]
               ,SM.mirroring_partner_instance    AS [Parnter Instance]
               ,SM.mirroring_role_desc           AS [Mirror Role]
               ,SM.mirroring_safety_level_desc   AS [Safety Level]
               ,SM.mirroring_witness_name        AS [Witness]
               ,SM.mirroring_connection_timeout  AS [Timeout(sec)]
               ,SM.mirroring_redo_queue          AS [REDO QUEUE]
FROM sys.database_mirroring AS SM
WHERE mirroring_guid IS NOT null
ORDER BY [Database Name];

15mirroring.jpg


All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.

If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

#database-mirroring, #disaster-recovery, #sql