Occasionally, I get a thought like that run through my mind. Or I might see something on Twitter or Blog post about something, and it sparks the question. Not about Star Wars, but my SQL Server environment.
Today something triggered me to confirm that all of my SQL Servers had the default backup compression setting set to enabled.
Finding Configuration Setting on Multiple Servers
I jumped into a trusty PowerShell session and ran the following command.
|
1 2 |
$servers = Get-DbaRegisteredServer -SqlInstance CMSServer -Group "SDLC" Get-DbaSpConfigure -SqlInstance $servers -Name DefaultBackupCompression | ogv |
The first command gets a list of my servers from my Central Management Server in a -Group named SDLC. The next command runs the dbatools get function to find all configuration items with a -Name of DefaultBackupCompression, and that output is being piped to the
Out-GridView cmdlet. In the below screenshot, you see that my environment has five servers that do not have the default setting I was expecting.
Good thing all of these outliers are DBA servers. This is why settings are non-standard, and it is not crucial to keep them in line as no backups are taken.
Fixing configuration values
If I wanted to bring the SQL Servers up to a standard configuration to match the rest of my environment, I could run the command below for each SQL Server to set the configuration value to my desired state.
|
1 |
Set-DbaSpConfigure -SqlInstance Server01 -Name DefaultBackupCompression -Value 1 |
Conclusion
One good thing about this is that if you wanted to build daily reports to show this type of information, it would be straightforward. For example, I have a SQL Agent Job that runs several checks on important things each morning in my environment. So maybe one day, I can build a blog post about that topic. 🤣
You could also look at the PowerShell module dbachecks. This module allows you to configure automated checks on different aspects of your SQL Server environment.
Good afternoon Garry,
I don’t want to derail this topic but came across a post you made on mssqltips.com talking about installing SSRS Report Server on a Azure SQL managed instance. I didn’t see a place where I could comment on that page so I found you here.
My question is around an error I receive when following these steps around a “RECOVERY” option that Azure managed SQL instance do not seem to support.
Searching the internet, some crowds say the answer is to create a local instance of SQL Server, build the DB there and migrate that to the managed instance. As you did not include these complexities in your guide I was curious if I was missing something.
Any help would be greatly appreciated!
Error:
System.Data.SqlClient.SqlException (0x80131904): Keyword or statement option ‘RECOVERY’ is not supported in this version of SQL Server.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
ClientConnectionId:97474cb5-c47e-4de7-8b17-5e00057f1e27
Error Number:40517,State:1,Class:16
ClientConnectionId before routing:4478084f-2599-423f-8b64-82339abd9257
Routing Destination:f8d1bbff4660.tr630.eastus1-a.worker.database.windows.net,11020
Fix?
https://social.msdn.microsoft.com/Forums/azure/en-US/5668d1a1-cc4c-4a79-82b5-909cb0e60297/cant-configure-ssrs-with-sql-server-running-on-azure?forum=sqlreportingservices