Microsoft SQL Server PFE

Who has access to your SQL Server infrastructure?

Ever had a person leave the organization or your team and wanting to know where do they have access? What explicit permissions, logins, or database users you have to clean up? If are like many organizations with 100s, 200s, or eve 300+ instances, this is an impossible task. The SQLOpsDB solution collects all security details for the server and each database. The report “Security – User Lookup”…

The SQLOpsDB data collection can help build an inventory of applications and their respective owners. For each database introduce the following extended properties on the user database. ApplicationName ApplicationOwner Both are optional, if none are supplied, it will default to “Unknown.” However, if ApplicationOwner is specified then ApplicationName is expected, or again it default to…

SQLOpsDB Reports: Dashboard – Drill Through

As stated in previous reports, all the dashboard elements offer drill through. To investigate the additional details behind the summary data. The summary layout is saved in Snapshot.* tables and are only generated once at the end of DataCollection script. If you feel snapshot is incorrect or incomplete, execute Snapshot.CreateDatabaseSnapshot stored procedures. It will drop existing snapshots…

SQL Health, Configuration Health, and Disk Capacity Planning. Single Stop!

SQLOpsDB Dashboard is primary report where all investigation starts for data collection. The database dataset is generated at the end of execution of the SQLOpsDB_DataCollection.ps1 PowerShell script. If for some reason the dashboard is returning blank, it means the data collection script did not complete. Review the SQLOpDB Data Collection Logs to understand where it is stuck. The dashboard is…

The PowerShell model is the primary code for collecting and updating the information. The command-lets in this module are broken into three categories. I have tried to follow modular development strategies. Although I did break some rules. For example, you will notice there is no Add-* command for most objects. I did this to minimize row-by-row based processing. The Update-* command lets handle…

Windows Task Scheduler for SQLOpsDB

The Windows Task Scheduler executes the PowerShell script for data collection for both SQLOpsDB data collection and configuration health. These collections can be completed via the SQL Agent jobs also, however, I chose to leverage the Windows task scheduler so jobs can be executed using gMSA. In the SQL Server, gMSA cannot be used as a proxy account. As stated in the previous posts, there are…

All the report files are accessible directly or indirectly from the primary report “SQLOpDB Dashboard.” I would recommend after deployment to hide all the reports and folders not needed to make it easier for your team to focus. To deploy the report open the solution file from the deployment location, “C:\SQLOpsDB\Traditional SSRS\SQLOpDBReports.sln.” In Visual Studio, first double click on…

SQL Server Policies for SQLOpsDB

The SQL Server policies are part of collection from Microsoft default best practice policies plus additional policies I have written over the year. The policies typical follow the naming convention of rule check, if the policy passes, means your configuration is correct. If rule fails, an update is required. I will not belong about how to write policies, but if you need assistance please reach…

Central Management Server (CMS) for SQLOpsDB

The CMS is the driver of all the data collection needs. When registering a server in CMS, follow these best practices. Register only the replicas, stand-alone, or failover cluster instances. That is do not register Availability Group Listener in same CMS group as Availability Group Replicas. This will cause duplicate data collection. The SQLOpsDB maps data by server name registered in the…

As stated in SQLOpDB Architecture, there are three components to configure, collect data, and do reporting. The requirements are briefly discussed on the GitHub page. A single virtual or a physical server, with minimum 4 processors and 8 GB of RAM. Microsoft SQL Server 2019 (any version will work — however all development and testing is against 2019). Microsoft SQL Server 2019 Report Server…

Over the years I have tried to minimize number of moving parts in the architecture. The overall architecture can be broken down into three primary components. SQL Server Instance Central Management Server (CMS) – Provides list of target servers. SQLOpsDB – Central database to record all the policy evaluation results and SQLOpsDB data collection. I have integrated the Enterprise Policy…

Deploying SQL Server Operational Dashboard

I have tried to automate parts of the deployment. When deploying the current version, there is no way to upgrade from an existing deployment.  In later posts I will talk about how you can use Microsoft Data Tools to do a compare and update. To start, download the deployment package from GitHub. Only download SQLOpDB_Solution_V3.00.00.0000_RTM.zip. All the source code is part of the…

One spot to see the over on all health of the SQL Server infrastructure. Make this part of morning DBA Top 5 tasks. Don't guess the database sizes & disk space. An application cost on SQL Infrastructure. Or who has access to the entire infrastructure.

I am releasing a solution that I have worked on for on-and-off for many years for many different customers. The solution is combination of the feedback and the requirements collected over many years. One spot to see the over on all health of the SQL Server infrastructure. Make this part of morning DBA Top 5 tasks. Don’t guess the database sizes & disk space. Get understanding of the capacity…

DBAs Stop Restoring Databases Manually!

I know many DBA build custom solutions to restore databases for some application. But in many cases I find customers are restoring same user databases manually again and again. Even if restores only take 5-10 minutes, over the period of week or month it takes way time away from important tasks. As DBAs are always fighting the fires, this creates a bottleneck for development team to request…

Ransomware Awareness for SQL Server

As I am seeing Ransomware attacks increase around the globe, I have been engaged with multiple customers whose SQL Server infrastructure has been infected.  I do not know the variant these customers were affected with, however, these variants were able to take SQL Server services offline and encrypt the MDF/LDF of each database.  In one scenario, they were also able to encrypt non-native SQL…

#SQLServer Smart Index Management (SQLSIM) V2.06.01 Released!

Some updates to an existing solution, fixing some bugs, refactoring some code, and adding new new functionality on my client’s request. Fixed bug in how the index fragmentation for IN_ROW_DATA. Refactored how operation time requirement is calculated. Now the solution will also warn you if FragScan will break the maintenance window end time.Corrected some spelling mistakes in column…

SQL Smart index management (SQLSIM)

Typical index management requires you to check for fragmentation every week and decide to maintain it using index reorganization or index rebuild. However, there is very little to no awareness in existing solutions as to will index will be fragmented?

The disk subsystems is extremely fast, severs with multi-terabyte of memory is not rare any more. But still, reading index just to assess if it…