Granting Windows AD group to access to Integration Services Catalog in SQL Server 2012

Issues

Though we give access to AD group in SSIS Package folder directly, the AD group members are not able to see SSIS packages under Integration Service Catalogs.  I did a little bit of research and found that Microsoft has a bug in Integration Service Catalogs security. The issues will exist if you do not upgrade SQL server 2012 to SP1. Since we are not upgrading SQL server to SP1, we follow workaround steps from Microsoft.

http://support.microsoft.com/kb/2890052

First, add AD Group group login in Security on Instance level

USE master
GO

CREATE LOGIN domain\username FROM WINDOWS WITH DEFAULT_DATABASE=master
GO

USE SSISDB
GO

CREATE USER domain\username FOR LOGIN domain\username
GO

Create SSIS_users role in SSISDB

use SSISDB

Go

create role ssis_users

go

Add Domain login to ssis_users

USE SSISDB

GO

ALTER ROLE ssis_users ADD MEMBER domain\username

After you run all the scripts above,

• Right click on your SSIS package folder under Integration Services Catalogs

• Click on Permissions and browse and check ssis_users role. It will be added under logins or roles tab

• Then, give appropriate access to ssis_users under Explicit tab. That's it.  Now, members of AD groups can view SSIS packages.

Connecting to a database using Domain account via SMS

I have my windows login given by my company. I use my windows authentication to login to my computer and then connect to any databases via SQL Server Management Studio(SMS) . 

As a DBA, I will need to grant a proper access a domain account (service accounts for some applications) to databases. Sometimes, they have issues connecting to databases though I give them proper access. So, I make sure that the domain account which is given access to my database is able to connect to the database.

In order to connect to database using a domain account,

First, I make a shortcut of SQL Server Management Studio on my desktop.

Second,  update the Target command with the following command. The path "c:\program files..." might be different because it depends on the location of your SQL server management studio is installed 

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\ACCOUNTNAME "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Then, double click on the shortcut and enter domain account password. 

SQL Server Management Studio will open and you will see your windows login(domain\yourlogin) but you are connected to a database as the domain account. Type sp_who2 in on the server and you can see your login as a domain account.

SQL Server 2012 AlwaysOn Testing

Preparation

Use SQL server management studio (SMS), create a test database and add the database to availability group database.

create database test

create table test

(testno int)

Data Load script

I will use the following simple data load script to test data synchronization and connection.

declare @i as integer

declare @id as integer

set @i = 200000

set @id = 1

insert into test(testno)

select @id

while ((select max(testno) from test) < @i )

begin

insert into test(testno)

select @id

set @id = @id +1

print @id

end

Test Case

Test 1: Ensure synchronization works normal

• Connect to Availability group Listener and run data load script
• Check the secondary replica to see if the log records made it across

Expected Result :Logs are written to Secondary replica. Row counts are the same.

Test 2: Primary replica with Manual failover

• Start manual failover from AlwaysOn Dashboard
• Confirm Primary is successfully failover to Secondary replica
• Reconnect to Availability group Listener

Expected Error : In SMS,Remote harden of transaction 'INSERT' (ID 0x0000000007016159 0000:000199ce) started at Jan 17 2014  2:04PM in database 'test' at LSN (211:148:3) failed.

Expected Result: Failover successfully. During the failover, client connection is disconnected to Availability group listener. Reconnect to Listener is successful and data load continues. Connection lost during failover is expected by design. It is between 3 sec to 15 sec.

Test 3: Primary replica with Manual failover (cont.): missing rows to determine the data loss

• Start manual failover from AlwaysOn Dashboard while data load script is running
•  Determine any missing rows are not submitted to Primary
•  Determine any missing rows are not replicated to Secondary

Expected Result: No missing rows in primary and secondary replicas

Test 4: Primary replica with Automatic (forced) failover

4.1    Stopping SQL Server service

• Stopping SQL Server service
• Confirm secondary replica becomes primary replica
•  At this point, primary replica run exposed and RPO of 0 data loss is not met because there is no secondary replica
• Reconnect to listener and run data load script

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues.

4.2    Power down Primary replica

• Power down Primary replica from VSphere
• Confirm secondary replica becomes primary replica
•  Reconnect to listener and run data load script

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues

4.3    Stop Current owner Cluster service

• Stop a current owner cluster service from failover cluster manager
• Confirm secondary replica becomes primary replica
• Reconnect to listener and run data load script

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues.

4.4    Disable Primary server network adapter

• Open Network Connection on Primary Replica
• Click on both device and disable
• Confirm secondary replica becomes primary replica
• Reconnect to listener and run data load script
• Open VShpere and open console of earlier Primary replica and enable network adapers

Expected Result: In Failover Cluster Manager, Secondary replica becomes Primary. During timeout, client connection is disconnected to Availability group listener. Then, reconnect to Listener is successful and data load continues.

Test 5: Primary replica with Automatic failover (cont.): missing rows to determine the data loss

• During automatic forced failover cases (4.1 – 4.4)
• Determine any missing rows are not submitted to Primary
• Determine any missing rows are not replicated to Secondary

Expected Result : No missing rows

Test 6: Secondary replica down

• Connect to availability group listener and run data load script
• Power off Secondary replica
• Confirm data load script is not interrupted

 Expected Result: Data load is NOT interrupted.

Test 7:  Primary replica also fails and Secondary comes up later

Imagine due to power issues, Primary and Secondary replicas are down.

• In Test 6, secondary replica is down
• Now Power off Primary
• Both nodes are offline and Cluster is down
• After 1 minute, power on earlier Secondary
• Confirm earlier Secondary comes up as a primary replica
• Confirm Cluster service comes up
• Reconnect to Listener and run data load

Expected Result : Earlier Secondary replica is online but it doesn’t change the role to Primary. Databases are Recovery pending state in Secondary. Unable to connect to Listener. The failover cluster manager is unable to connect to our WSFC.

Test 8:  Bring earlier Primary replica back online

• Power on earlier primary replica
• Confirm Cluster service is up
• Reconnect to Listener and run data load

Expected Result : Earlier Primary replica doesn’t come up as a secondary. It role is still Primary. Data load started and database is synchronized.

Test 9:  Quorum is down

• Take quorum offline. The WSFC cluster service offline
• Determine how application behaves in this situation
• After 1 min, Bring quorum online
• Confirm cluster service online
• Confirm all nodes are online
• Confirm database are synchronizing
• Confirm SQL Management Studio is able to connect to listener

E    Expected result : The cluster service stays online even if the quorum is taken offline. The quorum configuration is Node and File Share Majority. We had three nodes before the quorum is taken offline.  After the quorum is taken offline, the total node is 2 and we have two votes. We need more than half of the votes to running the cluster service. This means we can only sustain a single node failure.

     Test 10: Primary replica is taken offline while the quorum is 

•   Stop SQL service on Primary Replica
•  Verify a cluster service is still online

      Expected Result : The cluster service is offline. We need at least 2 out of 3 nodes to be online in order to the cluster service to stay online

Test 11:  VMotion

• Test VMotion on both replicas with System Adminstrator team
• Confirm no replica role changes
• Confirm no changes in cluster node role

Expected Result : VMotion is performed with no impact to running workloads or connected users and replica role.

Test 12:  Load testing/ performance test

• Application will log millions of rows
• Take a performance base line of the server while the loading takes place

Ø  IO

Ø  CPU

Ø  Disk

Ø  Network

Ø  SQL Server : Availability group replica

Ø  SQL Server: Availability group Database