In this tip we will show you how to configure email notifications if any SSRS report subscriptions fail.
Please read the latest MSSQLTips post: “SQL Server Reporting Services Failed Subscriptions Monitoring and Notifications“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
SQL Server Linked servers can introduce a security risk if not configured properly. Some settings out of the box can make Linked Servers more secure, like using proper logins mappings. In this article, we look at how to enable encryption between linked servers.
Please read the latest MSSQLTips post: “SQL Server Linked Server Encryption for Network Traffic“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
How do we manage schedules properly to ensure that we do not end up with dozens of unused or disabled schedules? How do we make sure that we use and update the correct schedules? What should we consider before we delete or disable an existing schedule?
Please read the latest MSSQLTips post: “SQL Server Agent Job Scheduling Management and Troubleshooting“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
The new SQL Server version (SQL Server 2022 Preview) is now available and there are some improvements related to security. There are also a couple of new built-in (fixed) server-level roles that were introduced in SQL Server 2022. Separation of duties and a principle of minimal permissions are SQL Server security best practices and we will cover how to use these new security features.
Please read the latest MSSQLTips post: “Compare SQL Server ##MS_LoginManager## and Securityadmin Role“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
Sparse columns were added with SQL Server 2008 as a new feature that helps save space when columns have a high ratio of NULL values. We have a couple of very large and wide tables with Nullable columns and most of them don’t have data. We’d like to take advantage of sparse columns to save space. We ran SSIS (SQL Server Integration Services) Data Profiling Task to find sparse columns candidates (Null Ratio profile). With more than 500 candidate columns it will be hard to review and alter each column individually. How can we use the SSIS Data Profiling output XML file? Can we generate a script to modify columns based on Data Profiling output and apply to this script sparse columns requirements/restrictions?
Please read the latest MSSQLTips post: “Generate SQL Server Sparse Columns Script“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
Dropping SQL Server logins without dropping associated the database users creates orphaned database users. By default, the DROP LOGIN statement in SQL Server only removes logins, but not database(s) users that are linked to these logins. The best practice is to delete all databases users that are mapped to this login first and then delete the login itself. When logins are deleted, and users remain orphaned, DBAs have a cleanup task to perform from time to time. This is to make sure that an orphaned user is not misused (to prevent a potential security issue). How can we make sure that we don’t forget to delete database users before dropping logins?
Please read the latest MSSQLTips post: “DDL Trigger Example in SQL Server for Login Deletion“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
SQL Server security monitoring is a critical part of the Database Administrator’s job. Some security related alerts could be setup very easy, but others require third-party tools or extra steps to setup. For example, SQL Server Audit can be used to monitor logins or users modification, but it requires audit log review. We would like to get real-time alerts every time a login or a user is created or added to a server or a database role, how can this be done?
In this tip we will provide steps and scripts for setting up WMI alerts and jobs responding to these alerts to monitor the creation and removal of users and logins as well as server and database roles membership changes.
Please read the latest MSSQLTips post: “Alerts and Notifications for SQL Server Login, Database User and Role Membership Changes“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
Central Management Server (CMS) is a SQL Server feature that allows you to manage multiple SQL Servers at the same time. You can run T-SQL scripts or execute Policy-Based Management policies on multiple servers. You can also group SQL Servers and run the scripts on a specific group of SQL Servers.
How else can CMS be used? How can we overcome some of the limitations when we run queries against CMS Server Groups?
In this tip we will show you a couple of CMS tricks. In our examples below we will show how you can sort the CMS query results for different scenarios. We will also provide a couple of examples of alternative uses of CMS.
Please read the latest MSSQLTips post: “Different ways to sort multi-server SQL Server query results using Central Management Server“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
Email alerting is an important feature that helps to automate a lot of Database Administration tasks. How can we be sure that Database Mail works properly? If there is email problem there is no another email that will notify us of an issue and it could be days before we realize that there is a problem.
In this tip we will provide a solution to check all of your servers for the different types of Database Mail issues. We are going to use Policy-Based Management (PBM) to perform these checks.
Please read the latest MSSQLTips post: “SQL Server Database Mail Health Check with Policy Based Management“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.
Most of the SQL Server DBAs will agree that we rely on email alerts too much. Tasks are automated, we have emails notifying us of failed jobs, of errors, etc. But how can we be sure that emails are always sent? If there is an email problem there is not another email that will notify us of an issue and it could be days before we realize that there is a problem.
In this tip we will provide a solution to monitor all of your servers for the different types of Database Mail issues. We will provide a T-SQL script that is executed with a PowerShell script. The PowerShell script can be scheduled to run on a Central Management Server (CMS) or another SQL Server. In our example we will run it once a day to check the Database Mail health, but you can modify it and run it every hour or as often as you need.
Please read the latest MSSQLTips post: “SQL Server Database Mail Health Check for all SQL Servers“.
Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.