Thank you Kennth Fisher (B | T) for hosting this mont’s T-SQL Tuesday party. For those of you that do not know T-SQL Tuesday was started by Adam Machanic (B | T) in 2009. If you feel like hosting 1 of these monthly events drop Adam a line. This month’s topic is How do you Manage Security.
Like all previous topics for the TSQLTuesday Blog party this one provides the ability to look at it from many angles. With that in mind I am attacking this month’s article looking at managing security with Alwayson Availability Groups. Availability Groups became available with the release of SQL Server 2012 and provide the ability for a granular approach to high availability and or disaster recoverability for your SQL server environments. Because Availability Groups are at the database level, this is where this article fits in with this month’s topic. For you to be able to utilise your SQL server databases configured in an Availability Group, security in your environment needs to be managed and monitored.
On our primary replica we may have many databases that are required to perform normal business functions. For your end users or applications, these require various accounts to be able to access the data within those particular databases. These accounts may be utilising any of the following:
- SQL Server Accounts
- Windows Accounts
- Windows Groups
To go along with these accounts we will have various levels of permissions assigned to each account or group to allow the appropriate activity to be undertaken on the database. These permissions may be configured by adding an account to an existing server level group (replace with the correct name) like SysAdmin. In this article I am not going to talk about the right level of permissions to be granted, I am sure that will be covered off by 1 of the other articles in this TSQLTuesday event. So our environment is configured and our application is working without a hitch. We look at configuring a couple of these databases into an Availability Group to meet the business requirement of being able to continue operations after a disaster event forcing us to be up and running in a secondary data centre. Out databases have come online but our application is not able to connect as we are receiving login failures.
This is where as part of the design and implementation process to implement this technology we need to understand that only the database is migrated across onto the secondary replica(s) in our environment. Previously and even know if we were using Failover Clustered Instances this is not a consideration as the security is included in the Instance and thus are available after a failover. For our environments to be able to continue to function seamlessly after a failover of the Availability Group we need to identify those security accounts, associated group memberships or individual permissions required to allow for normal processing to continue with the application database. This is the simple part of our security requirements, the next step is to decide on how to maintain each replica in sync.
Manual process
A manual process can be implemented which entails processes to be followed when creating, modifying or removing security accounts from the primary replica. This type of approach would need to be included as part of the change control process in your environment to ensure all environment remain in sync with the primary replica to allow for continuity after a failover has occurred. This type of process can if not followed correctly can lead to environments being out of sync and potentially not being known about until it is too late. This process also adds extra work for the database administrators to maintain all environments.
Automated Process
An automated process can also be implemented with a range of approaches:
- SSIS package that is scheduled on a regular basis
- Powershell script that is scheduled on a regular basis
- T-SQL jobs that are scheduled on a regular basis
The automated process alleviates the extra ongoing work for the database administrator to maintain the environment. Appropriate rules need to be determined and implemented into either of the approaches and thoroughly tested to ensure the automated process is performing appropriately. In conjunction with the automated process there still needs to be a process implemented and followed as part of the change control process to ensure that security accounts are added, modified or removed from the appropriate replica that is deemed to be the source of truth by the automated process. If the identified source of truth is not maintained then the automated process has the ability to replicate incorrect security configurations.
Which of these 2 solutions is the better solution? Well using a term that is used very often by a lot of my fellow SQL Server professionals ‘It Depends’. If you do not have any SSIS, Powershell or T-SQL Skills to create the appropriate scripts with correct logic to ensure all replicas are maintained in sync and you only have a small environment (Ie 1 Availability Group) you may wish to use the Manual process. Alternatively if you have a large environment with many Availability Groups configured then you would probably want to invest time in creating a bullet proof automated process and strict change control to ensure that the source of truth is always correct.
If you are going to use Availability Groups in your SQL Server environment remember that you will need to include into your design and implementation 1 of these approaches to ensure your environment continues to run as expected and required after failing over from the primary replica.
Leave a Reply