November 20, 2015 at 10:47 am
Hi all,
Is there a way to make it so that when SQL server creates a database file, it inherits permissions from the directory its in? From what I can tell, what SQL server does is create the file, then disables permission inheritance, and has access to the file solely because it (the account running the SQL engine) is the owner.
Normally, this is fine, but I've got a specific need to grant another account permissions to these files.
Some background information on what I'm trying to accomplish:
I've got one license of SQL Server 2012 Standard to work with, and I am trying to set up pseudo warm-standby failover process from my primary environment to my DR environment.
Both of these systems are virtualized. My primary instance has an iSCSI connection to a SAN that it uses to access my user database files. The warm-standby exists specifically for the event that the SAN fails. Unfortunately, the SAN in the DR environment cannot accept snapshots from my primary environment directly, so I need to work around that limitation. The database backups are stored on our backup server, that runs the backup jobs for all servers. It is a physical server outside of the primary environment and the DR environment.
The goal is to get the SQL server back up and running as soon as possible in the event of a failure. I can maintain a up-to-date copy of my primary instance in the DR environment, except for the iSCSI volume with all of the user databases (and tempdb) on it.
To keep a warm-copy of the user databases, I have configured log-shipping on a warm-standby instance in my DR environment, with a script that re-initializes (restores from a full backup) daily, along with DBCC checkdb jobs so that I can verify my backups are good at the same time that I'm enabling a faster failover.
For the process of actually failing over, what I was hoping to do was simply shut down the warm-standby server, detach the virtual drive the user databases are on, attach it to my primary instance, mount the drive so that these databases are at the same path as the old ones, and start the services. This way I can avoid changing IP addresses, and re-configuring log shipping and all of the jobs on both servers, as well as keeping the logins synchronized.
The problem with this approach is that the warm-standby server and the primary instance are running under different user accounts (AD accounts), and the permissions of the database files on the warm-standby server aren't inherited from the directory they are in, so I can't make it so that the primary service instance has access to the files ahead of time.
After writing this all out, I'm thinking maybe my best solution would be to have a script that gets run manually as administrator during the failover process that would change the owner on the files in that directory, if I can't configure SQL server to do something sane with file permissions...
November 23, 2015 at 7:16 am
IP addresses have nothing to do with the drive the files are on and everything to do with the NICs in the servers / assigned to the servers.
That being said, what you're suggesting is a hack to clustering and probably won't work. Chances are likely that your data files will be corrupted and unattachable. So try going with either a mirroring solution or a clustering solution. Don't hack your way to a high availability solution. That way lies madness and much heartache.
November 23, 2015 at 8:57 am
clubbavich (11/20/2015)
Hi all,Is there a way to make it so that when SQL server creates a database file, it inherits permissions from the directory its in? From what I can tell, what SQL server does is create the file, then disables permission inheritance, and has access to the file solely because it (the account running the SQL engine) is the owner.
Normally, this is fine, but I've got a specific need to grant another account permissions to these files.
Some background information on what I'm trying to accomplish:
I've got one license of SQL Server 2012 Standard to work with, and I am trying to set up pseudo warm-standby failover process from my primary environment to my DR environment.
Both of these systems are virtualized. My primary instance has an iSCSI connection to a SAN that it uses to access my user database files. The warm-standby exists specifically for the event that the SAN fails. Unfortunately, the SAN in the DR environment cannot accept snapshots from my primary environment directly, so I need to work around that limitation. The database backups are stored on our backup server, that runs the backup jobs for all servers. It is a physical server outside of the primary environment and the DR environment.
The goal is to get the SQL server back up and running as soon as possible in the event of a failure. I can maintain a up-to-date copy of my primary instance in the DR environment, except for the iSCSI volume with all of the user databases (and tempdb) on it.
To keep a warm-copy of the user databases, I have configured log-shipping on a warm-standby instance in my DR environment, with a script that re-initializes (restores from a full backup) daily, along with DBCC checkdb jobs so that I can verify my backups are good at the same time that I'm enabling a faster failover.
For the process of actually failing over, what I was hoping to do was simply shut down the warm-standby server, detach the virtual drive the user databases are on, attach it to my primary instance, mount the drive so that these databases are at the same path as the old ones, and start the services. This way I can avoid changing IP addresses, and re-configuring log shipping and all of the jobs on both servers, as well as keeping the logins synchronized.
The problem with this approach is that the warm-standby server and the primary instance are running under different user accounts (AD accounts), and the permissions of the database files on the warm-standby server aren't inherited from the directory they are in, so I can't make it so that the primary service instance has access to the files ahead of time.
After writing this all out, I'm thinking maybe my best solution would be to have a script that gets run manually as administrator during the failover process that would change the owner on the files in that directory, if I can't configure SQL server to do something sane with file permissions...
whole host of issues here, most of it bad. You're attempting to use the system in a way it was not designed to be used. With the services using different accounts NTFS permissions will indeed be affected, also encrypted objects within the instance will likely fail as the service master key will also be affected.
Why not just synch the logins to the log shipping secondary and perform a failover when required??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2015 at 2:47 pm
Ok, given the problems in my current strategy that have been pointed out, I'm going to abandon the idea of trying to get my primary instance running in my DR environment from the secondary instance, and instead actually fail over to the secondary instance.
I've got a few questions on some aspects of the failover process, but it's probably best if I ask those in a separate topic.
Thanks to both of you for pointing out the problems with my plans.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply