Always On - commit comparing normal DB - Lab test (POC)

Objective:

The objective of this POC is to understand the time delay or the time to get committed for insert statement for the database which is configured with AlwaysOn synchronous and async failover.

Note: Synchronous commit for a insert/update query will first harden the transaction in the secondary AG and based on the acknowledgement the primary will commit.

Server configuration:

Test Case:

1.       We are going to create one database named Sync_POC_WithoutAG is a standalone DB which is not involved in AlwaysOn.

2.       In the second set we are going to create one database named Sync_POC which will be involved in AlwaysOn with Synchoronus failover on the secondary server with a readable copy.

3.       Both (#1 and #2) DBs, we are going to create a table with same structure and insert data.

4.       We are planning to insert the data with different record counts.

5.       The above insert will happen based on a loop and find the time difference between both.

6.       The same test has been observed for AlwaysOn AG with async mode.

Setup AlwaysOn with Sync:

 

 Setup AlwaysOn with Async:

 

Create table script:

CREATE TABLE [dbo].[Name](

       [id] [numeric](10, 0) IDENTITY(1,1) NOT NULL,

       [name] [varchar](50) NULL,

 CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED

(

       [id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Simulation query:

DECLARE @StartTime datetime,@EndTime datetime

SELECT @StartTime=GETDATE()

DECLARE @intLimit AS INT = 100000

DECLARE @intCounter AS INT = 1

WHILE @intCounter <= @intLimit

BEGIN

INSERT INTO Name VALUES ('Name1')

SET @intCounter = @intCounter + 1

END

SELECT @EndTime=GETDATE()

SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in milliseconds]

Sample observations for standalone DB:

Sample observations for AlwaysOn Sync DB:

  

Sample observations for AlwaysOn Async DB:

  

Analysis:

No of records inserted	Normal DB (milli secs)	Sync AG DB (milli secs)	Sync AG DB (milli secs) NIC card	Async AG DB (milli secs)
100	0.106	0.203	0.203	0.103
250	0.266	0.541	0.541	0.26
500	0.573	1.071	1.071	0.516
750	0.716	1.643	1.643	0.85
1000	0.971	1.963	1.963	1.3
2500	2.446	4.963	4.963	2.94
5000	4.746	12.276	12.273	5.41
7500	7.411	15.506	15.502	8.683
10000	9.696	20.813	20.811	11.26
25000	25.161	50.411	50.41	25.813
50000	50.523	107.312	107.31	53.97
75000	74.603	155.45	155.45	82.406
100000	98.99	214.82	214.81	108.016

 Conclusion:

Time taken =  (Avg. time of Sync (or) Async AG DB / Avg. time of Normal DB)

From the above analysis, the time taken to do an insert in AlwaysOn Sync would be twice (2.125 milli-secs) the time as compared to a standalone (or) normal DB.

Same way, to do an insert in AlwaysOn Async would be bit time consuming (1.091 milli-secs) compared to the DB which is not part of AG.

Time taken for insert query:

Normal DB (without AlwaysOn enabled)                                            = 1 milli sec

AlwaysOn Async                                                                                  = 1.1 milli sec

AlwaysOn Sync                                                                                    = 2.1 milli sec

AlwaysOn Sync with dedicated NIC card                                            = 2.1 milli sec

Note: All the above observations are based on the primary DB commit.

Automate DB Restore

To automate the database restore, create the following steps.

Step 1: Take Backup of source and destination.

BACKUP DATABASE <DB Name> TO DISK = <disk path> 

Step 2:  Script out permissions on destination database and save it or store it in a table.

Use <DB name >

GO

SET NOCOUNT ON

print 'Use ' + db_name() 

print 'go'

--Grant DB Access---

select 'if not exists (select * from dbo.sysusers where name = N''' + usu.name +''' )' + Char(13) + Char(10) +

'         EXEC sp_grantdbaccess N''' + lo.loginname+  '''' + ', N''' + usu.name +  '''' + Char(13) + Char(10) +

'GO'

from sysusers usu , master.dbo.syslogins lo

where usu.sid = lo.sid and (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)

go

--Add Roles---

select 'if not exists (select * from dbo.sysusers where name = N''' + name +''' )' + Char(13) + Char(10) +

'         EXEC sp_addrole N''' + name +  '''' + Char(13) + Char(10) +

'GO'

from sysusers where uid> 0 and uid=gid and issqlrole=1

go

--Add RoleMember---

select 'exec sp_addrolemember N''' + user_name(groupuid) + ''', N''' + user_name (memberuid) + '''' + Char(13) + Char(10) +

'GO'

from sysmembers where  user_name (memberuid) <> 'dbo' order by groupuid

--Add Alias Login also---

select 'if not exists (select * from dbo.sysusers where name = N''' + a.name +''' )' + Char(13) + Char(10) +

'         EXEC sp_addalias N''' + substring(a.name , 2, len(a.name)) +  '''' + ', N''' + b.name +  '''' + Char(13) + Char(10) +

'GO'

from sysusers a , sysusers b where a.altuid = b.uid and a.isaliased=1

go

SET NOCOUNT OFF

--Add object & DB level permission also---

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) 

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

+ SPACE(1) + perm.permission_name + SPACE(1)

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

WHERE perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC

Step 3: Refresh destination database from source database backup.

----Make Database to single user Mode

ALTER DATABASE <Destination DB>

SET SINGLE_USER WITH

ROLLBACK IMMEDIATE

----Restore Database

RESTORE DATABASE <Destination DB>

FROM DISK = <disk path>

Step 4: Apply Step 2 Output: saved in table on destination database.

Step 5: Run orphan user script to fix orphan users on destination database. Save the output in a different table.

Use <destination_database>. 

GO

setnocount on

select 'exec' +' '+'sp_change_users_login ''update_one'',''' + name + ''',''' + name + '''' +char(13) 

fromsysuserssu

wheresid NOT IN 

(selectsid from master..syslogins )

AND 

islogin = 1 

AND  

name NOT LIKE 'guest' and su.issqluser=1

Step 6: Run step 5 output: saved in table on the destination database 

Step 7: Change database owner to SA.

Use <destination_database>

GO

sp_changedbownersa

Execute SSIS package using domain(windows) account through SQL job

Environment:

In my client's production environment, we use service account (Domain based) having read/write/execute access and SQL login for read only (support) access. Since the interactive login is disabled at the group policy, no users can directly connect to SSMS with servicce account.

To execute any jobs to do a push/pull of data, the user needs to execute it from a different/remote server where as to share the load of ETL. 

Problem statement:

In the above setup, user needs to run his job\packages only using the service account (Windows) as it has the read/write/execute access.

How to execute a ssis package job using windows account?

Solution:

Answer is to setup a proxy and provide the credentials in the job step.

1.      Open SQL Server Management Studio (2008 R2) and connect to the SQL server that would run the SSIS package as a SQL Server system admin or local server admin.

2.      Run the following script (provide the actual service account (AD) and its network password that would be used to connect to the database) to create a proxy.

USE msdb

CREATE CREDENTIAL UserSvc WITH IDENTITY = 'XYZ\UserAcc', SECRET = 'Domain pwd of xyz'

GO

Note: SQL Server will store the above-given network password in an encrypted way; no security issues.

EXEC dbo.sp_add_proxy @proxy_name = 'UserProxy',

      @enabled = 1,

      @description = 'Proxy to execute User SSIS as service account',

      @credential_name = 'UserSvc'

GO

EXEC dbo.sp_grant_proxy_to_subsystem

      @proxy_name = 'UserProxy',

    @subsystem_name = N'Dts'

GO

3. Assuming the above script ran without errors, open the SQL Agent job (from SQL Server Management Studio) used to run the SSIS package and edit the SSIS package step. 

Choose "UserProxy" from the Run As drop-down and click OK. See the screenshot below for reference. 

4. Edit  the connection string to include password as below:

5. By setting up above, we can execute a package using domain account.

POC on Nagios(monitoring tool) for SQL Server

Purpose:

One of my client is planning to create a centralized monitoring system for all their databases. Due to budget constraint they thought of going for Nagios. From MS SQL Server end, to have the basic understanding on what is Nagios and the feasibility, I have done a small POC. Below are the areas, I have worked, read and learnt on Nagios.

About Nagios:

üNagios is a monitoring and alerting engine.

üIt does

ØSystem Monitoring

ØDatabase Monitoring

ØApplication Monitoring

üIt serves as the basic event scheduler, event processor, and alert manager for elements that are monitored.

üIt is designed to run natively on Linux/Unix systems.

üProducts:

ØNagios XI

ØNagios Core

ØNagios Fusion

ØNagios Incident Manager

ØNagios Network Analyzer

Pre-requisite:

ØNagios Enterprises highly recommends and will only support installing Nagios XI on a newly installed, “clean” system

ØAttempting to install Nagios XI on a pre-existing system with other applications already installed can cause the Nagios XI installation process to fail

ØInternet access is required for installation and upgrades!

ØLinux distributions:

qRHEL 5 & 6 32-bit and 64-bit (requires RHN registration)

qCentOS 5 & 6 32-bit and 64-bit

Database Monitoring:

ØNagios XI is the product to be installed for database monitoring.

ØNagios supported databases:

üMy SQL

üPostgres

üDB2

üOracle

üMS SQL Server

Installation:

http://ptihosting.com/blog/it-blog/monitor-mssql-with-nagios/ (Monitor from Linux)

Plugins for SQL Server:

http://exchange.nagios.org/directory/Plugins/Databases/SQLServer/

Pricing:

For more details: http://www.nagios.com/products/nagiosxi/pricing

Conclusion:

Considering the pricing as compare to other monitoring tools, Nagios is cheap for licensing and support. But to do so, the person configuring requires linux/unix knowledge with Nagios core also it is not easy and time consuming, since it requires linux server with Nagios installation. Overall it definitely a choice for who wants to implement monitoring tool having cost constraint.

References:

E-Book:

http://oss.org.cn/man/newsoft/nagios/index.html

Few links:

http://nagios.frank4dd.com/howto/db-monitoring.htm

http://exchange.nagios.org/#/

http://assets.nagios.com/downloads/nagiosxi/docs/XI_High_Availability_Options.pdf

Online Demo:

http://nagiosxi.demos.nagios.com/

SQL Server 2012 - Always On - a glance

AlwaysOn = HA + DR 

Features:

• Multi-Database Failovers .
• Multiple Secondaries.
• Active Secondaries .
• Integrated HA Management.
• Does not Require any Shared Storage . 

Benefits:

• Supports alternative availability modes (Sync / Async)
• Supports several forms of availability group fail-over (auto/manual/forced)
• Readable secondary replicas (Yes/No/Read Intent)
• Supports automatic page repair for protection against page corruption. Supports encryption and compression, which provide a secure, high performing transport
• Backups from secondary replicas

Performance Improvements:

• Data Latency (secondary replica)
• Read-Only Workload Impact (secondary replica)
• Statistics for Read-Only Access Databases (secondary replica)

Limitations and Restrictions:

Availability group Restrictions :

• Availability replicas must be hosted by different nodes of one WSFC cluster.
• Unique availability group name
• Maximum number of availability groups and availability databases per computer(as per white papers 10 Availability Group with 10 DB’s each )

Cross DB connection:

• Cross DB access can be given as a work around cannot be given directly between availability groups.
• For SQL authentication the cross DB can be performed through rev- logins only.
• Cross availability group DB connection will not work if any of the referenced AG fails.

Availability Database Restrictions :

• Impact on add-file operations.
• RESTORE WITH MOVE

Network restrictions :

• An Availability group to support automatic fail-over, the secondary replica that is the automatic-failover partner must be in the SYNCHRONIZED state.
• If the network link to this secondary replica fails (even intermittently), the replica enters the UNSYNCHRONIZED state and cannot begin to re-synchronize until the link is restored.
• If the WSFC cluster requests an automatic fail-over while the secondary replica is unsynchronized, automatic fail-over will not occur.

Database level Audit for DDL changes

One of the application team came with a requirement that they don't have a control of who is modifying the schema level changes as the development team have full access in development environment. After discussion, we thought of enabling an audit which traps the necessary information in a table. That can be provided as a report to the application team when needed.I just tried this and it worked fine.

Steps followed:

1. Create a DDL trigger on the datbase which needs to track the schema changes.
2. Create a table on the database which is used by DBA for auditing.
3. Insert that values into the audit table.
4. The table will have data viz Event time, event type, object name, object type, login name, command text and Host Name.
5. So whenever there is a schema change all the above information will be captured and inserted into a different DB which is owned by the DBA team.

Setup:

Creation of trigger in the AppDB:

USE [Appdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [trg_DBEvents]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

      SET NOCOUNT ON

      DECLARE @xmlEventData XML

      DECLARE @hostname NVARCHAR(100)

      SET @xmlEventData = eventdata()

      SET @hostname = HOST_NAME()

      INSERT INTO AuditDb.dbo.DDL_Event_log([EventTime], [EventType],

[ObjectName], [ObjectType],[LoginName],

       [CommandText],[HostIP])

      SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

            CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),

CONVERT(VARCHAR(100),

@xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),

            CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),

            CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),

            CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')),

            @hostname

           

END

Creation of table in the AuditDB : 

USE [AuditDb]

GO

CREATE TABLE [dbo].[DDL_Event_log](

      [EventID] [int] IDENTITY(1,1) NOT NULL,

      [EventTime] [varchar](100) NULL,

      [EventType] [varchar](100) NULL,

      [ObjectName] [varchar](100) NULL,

      [ObjectType] [varchar](100) NULL,

      [LoginName] [varchar](100) NULL,

      [CommandText] [varchar](max) NULL,

      [HostIP] [varchar](50) NULL,

 CONSTRAINT [PK_DDL_Event_log] PRIMARY KEY CLUSTERED

(

      [EventID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Secondary]

) ON [Secondary]

GO

Check how it works :


I am going to create a new stored procedure in AppDB database logging in as sql user.

USE [Appdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[new_dummy_proc]

     

AS

BEGIN

SELECT * from dbo.inventorydetails where inventoryid is not null;

     

END

When we see the log table created under the AuditDB database the changes has been captured.

Now I will alter the same procedure with a windows user and see the log table.









Security:

When a new user is created, DBA needs to be provide write access to the AuditDB to capture the ddl changes.

Note:Though creating trigger impact performance, it is accepted as the setup is easy and it is implemented on non-production environment.