March 12, 2019 at 3:50 pm
Hope this is the right board for this, if not please let me know.
I was asked to help with a backup maintenance plan for an always on sql server 2016 database. This is not a server I generally look after, and is set up with always on high availability with one other server, and I have not worked in or with clustered environments before.
I got the maintenance plan working (it was already working, but they did not have copy-only checked off and an error was showing in the maintenance plan wizard) and while I was reading up on how best to set up backups for an Always On setup, I notices this one is set up for backing up on primary only, which is probably not the best option.
When I check the properties on the availability groups, the options for backup preferences are greyed out for all groups. After some more reading I found out that my account needs some more securables (alter any availability group, control server, etc) added to my account. My AD account I connect with has sysadmin server role, but I am unable to add the needed securables to my own account. When I add them, save and then check account properties, they are gone. When I add them to another AD account of a coworker they are there when I check back. I'm assuming I probably need to connect as sa to the server, but I don't know the sa password.
My question is, can I get another AD user that has sysadmin server role to grant me the permissions I need so I can modify the availability groups backup preferences?
I'm more used to Oracle databases, where the security is a little more straightforward and not so tied in with the OS, so unsure exactly how all these permissions mesh with one another in MSSQL.
thanks in advance.
March 13, 2019 at 8:29 pm
Glen_A - Tuesday, March 12, 2019 3:50 PMI was asked to help with a backup maintenance plan for an always on sql server 2016 database. This is not a server I generally look after, and is set up with always on high availability with one other server, and I have not worked in or with clustered environments before.I got the maintenance plan working (it was already working, but they did not have copy-only checked off and an error was showing in the maintenance plan wizard) and while I was reading up on how best to set up backups for an Always On setup, I notices this one is set up for backing up on primary only, which is probably not the best option.
Backing up on Primary Only is not a good or bad option, it is dependent on your requirements. The main impact of backing up on primary is the performance impact. Backing up on a secondary limits you to COPY_ONLY backups and no differentials, so there are important considerations there. In addition, any backups run on a secondary count as production workload and impact licensing. Typically, I will always backup on primary unless I have a compelling reason not to.
Also, Maintenance Plans are not the ideal way to handle backups. They are clumsy and not very intuitive, and they don't handle change well. You need to look at other options, such as Ola Hallengren's excellent T-SQL based maintenance solution.
Glen_A - Tuesday, March 12, 2019 3:50 PMWhen I check the properties on the availability groups, the options for backup preferences are greyed out for all groups. After some more reading I found out that my account needs some more securables (alter any availability group, control server, etc) added to my account. My AD account I connect with has sysadmin server role, but I am unable to add the needed securables to my own account. When I add them, save and then check account properties, they are gone. When I add them to another AD account of a coworker they are there when I check back. I'm assuming I probably need to connect as sa to the server, but I don't know the sa password.
If you are a member of sysadmin on that instance you do not need any securables added to your login, you already have sufficient access. Are you viewing the AG properties from a secondary replica? If so, the greyed out options are likely caused by this. Try connecting to the Primary Replica then viewing the properties, or use T-SQL rather than the GUI. T-SQL usually gives better error information to help understand why something isn't necessarily working.
Glen_A - Tuesday, March 12, 2019 3:50 PMMy question is, can I get another AD user that has sysadmin server role to grant me the permissions I need so I can modify the availability groups backup preferences?I'm more used to Oracle databases, where the security is a little more straightforward and not so tied in with the OS, so unsure exactly how all these permissions mesh with one another in MSSQL.
You cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. If you try to run GRANT CONTROL SERVER TO [Domain\User] you will literally see a message printed that says "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.". Another example of why T-SQL is better than GUI, the GUI actually just closes the window without the warning, making you assume it ran correctly, but it did not (well done Microsoft!).
Some points here:
March 14, 2019 at 7:22 am
HandyD - Wednesday, March 13, 2019 8:29 PMGlen_A - Tuesday, March 12, 2019 3:50 PMI was asked to help with a backup maintenance plan for an always on sql server 2016 database. This is not a server I generally look after, and is set up with always on high availability with one other server, and I have not worked in or with clustered environments before.I got the maintenance plan working (it was already working, but they did not have copy-only checked off and an error was showing in the maintenance plan wizard) and while I was reading up on how best to set up backups for an Always On setup, I notices this one is set up for backing up on primary only, which is probably not the best option.
Backing up on Primary Only is not a good or bad option, it is dependent on your requirements. The main impact of backing up on primary is the performance impact. Backing up on a secondary limits you to COPY_ONLY backups and no differentials, so there are important considerations there. In addition, any backups run on a secondary count as production workload and impact licensing. Typically, I will always backup on primary unless I have a compelling reason not to.
Also, Maintenance Plans are not the ideal way to handle backups. They are clumsy and not very intuitive, and they don't handle change well. You need to look at other options, such as Ola Hallengren's excellent T-SQL based maintenance solution.
Glen_A - Tuesday, March 12, 2019 3:50 PMWhen I check the properties on the availability groups, the options for backup preferences are greyed out for all groups. After some more reading I found out that my account needs some more securables (alter any availability group, control server, etc) added to my account. My AD account I connect with has sysadmin server role, but I am unable to add the needed securables to my own account. When I add them, save and then check account properties, they are gone. When I add them to another AD account of a coworker they are there when I check back. I'm assuming I probably need to connect as sa to the server, but I don't know the sa password.If you are a member of sysadmin on that instance you do not need any securables added to your login, you already have sufficient access. Are you viewing the AG properties from a secondary replica? If so, the greyed out options are likely caused by this. Try connecting to the Primary Replica then viewing the properties, or use T-SQL rather than the GUI. T-SQL usually gives better error information to help understand why something isn't necessarily working.
Glen_A - Tuesday, March 12, 2019 3:50 PMMy question is, can I get another AD user that has sysadmin server role to grant me the permissions I need so I can modify the availability groups backup preferences?I'm more used to Oracle databases, where the security is a little more straightforward and not so tied in with the OS, so unsure exactly how all these permissions mesh with one another in MSSQL.
You cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. If you try to run GRANT CONTROL SERVER TO [Domain\User] you will literally see a message printed that says "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.". Another example of why T-SQL is better than GUI, the GUI actually just closes the window without the warning, making you assume it ran correctly, but it did not (well done Microsoft!).
- Yes, another sysadmin or a securityadmin could grant you the permission on those securables and it would stick.
- This is why you should manage domain user access via Windows Groups instead of individual logins. You would be able to grant this permission to a group you're a member of and have it stick.
- Try to do more of this sort of work via T-SQL. You get better feedback about things that won't work than from the GUI and often you can include options in commands that the GUI does not expose.
- Please, please, get rid of the maintenance plans, especially for AG-joined databases. They are old, clumsy and cumbersome. Ola's solution is pretty much industry standard these days.
Thanks for the reply.
I did grant another user the permissions needed to see the backup preferences for the availability groups, and he was still unable to see them (still greyed out) so we must be missing something here. He's also a member of sysadmin on the server as well. I am logged into the primary server here so not sure what the issue could be. I will try to log into the secondary replica and see what happens. I did not set up this instance so I don't know exactly how it was done.
Good point on the T-SQL, I will check out the solution you suggested. With Oracle I primarily use SQL Plus instead of their OEM and for backups I use RMAN command line with RMAN scripts and run those outside the GUI, I just have not spent a lot of time with T-SQL and the MSSQL databases I am overseeing all have maintenance plans already in place for them.
I was wondering if MSSQL had an option like Oracle's RMAN where I just specify a recovery window (of x days) from command line and then anything not needed outside that recovery window is automatically aged out and deleted. The MSSQL maintenance plans method for aging out old backups is, like you said, cumbersome and I have found it doesn't always work.
The backing up of the secondary replica was just what I had read while researching this, in any MS documents it seemed to be the recommended practice. If this database is not going to be intensively used then I may just keep it on primary, as I would like to put in a weekly full with differentials in between like I do with other large databases. I do wish SQL Server had an incremental option though.
March 14, 2019 at 6:43 pm
I did grant another user the permissions needed to see the backup preferences for the availability groups, and he was still unable to see them (still greyed out) so we must be missing something here. He's also a member of sysadmin on the server as well. I am logged into the primary server here so not sure what the issue could be. I will try to log into the secondary replica and see what happens. I did not set up this instance so I don't know exactly how it was done.
Good point on the T-SQL, I will check out the solution you suggested. With OracleOracle I primarily use SQL Plus instead of their OEM and for backupsbackups I use RMAN command line with RMAN scripts and run those outside the GUI, I just have not spent a lot of time with T-SQL and the MSSQL databases I am overseeing all have maintenance plans already in place for them.
Unfortunately, maintenance plans still permeate SQL Server environments everywhere. They are okay for the basics and for accidental/part-time DBAs to use to get backups in place quickly, certainly better than no backups, but I would recommend doing some research if you have time on SQL's BACKUP command and Ola's solution so you can implement a comprehensive backup solution
I was wondering if MSSQL had an option like Oracle's RMAN where I just specify a recovery window (of x days) from command line and then anything not needed outside that recovery window is automatically aged out and deleted. The MSSQL maintenance plans method for aging out old backups is, like you said, cumbersome and I have found it doesn't always work.
One of the major problems with maintenance plans is they are not dynamic, so as things change on the server, they do not change with it. Its the reason I prefer a pure T-SQL or PowerShell solution where you can build the framework to be dynamic and deploy across hundreds of servers with little issue.
The backing up of the secondary replica was just what I had read while researching this, in any MS documents it seemed to be the recommended practice. If this database is not going to be intensively used then I may just keep it on primary, as I would like to put in a weekly full with differentials in between like I do with other large databases. I do wish SQL Server had an incremental option though.
Its a major gripe of mine with the AlwaysOn documentation around backups, the main doco suggest that this is the way to do it, and in fact by default an AlwaysOn AG is setup to "Prefer Secondary" backups, but if you dig in to the documentation you find all these caveats that can have a significant impact on your backup and recovery process. For VLDBs and those with too much performance impact due to backups, you need to go the extra mile and develop a backup solution that can leverage these features, but in reality most databases do not have that need.
March 25, 2019 at 4:05 pm
I think I found out the answer to a lot of my questions, the Always On was installed on standard edition SQL Server 2016, which I believe explains the greyed out options in the backup preferences etc. The client I am working for only uses SE for all their SS databases, I was also wondering why there was an availability group for each database instead of just one AG, again, because SE.
Thanks again everyone for their help & suggestions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply