Vision: SQL Homeostasis

This is actually part 2 of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there will be a series of handful of posts. You can find the part 1 of this blog series at the following link:  Part 1: Powershell script to find new servers in an AD domain https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html I will be using the CSV file generated by the powershell script mentioned in the above post. In below powershell script all I am doing is to check if the remote servers have sql server instance winodws services setup and their current status. I am not checking yet whether I have access to them or what version of sql servers these instances are running. That will be in the next post in this series! Additionally, in this post I am also inserting the collected information into a sql staging table. But first, if you are just interested in looking up sql server services on a single remote computer, you c...

First, some blah... blah... blah... By default when a primary key constrained is created on a table/view,  SQL Server automatically creates a unique clustered in order to enforce it.  And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index. That works best in most cases and is the recommended best practice. And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to. Scenario: So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time. But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now tha...

I should start off by mentioning that this post is applicable to sql server versions 2012 and up. If you have an older version of sql server, the solution discussed here will not work. We are generally aware that a user in a database is orphaned when it does not have a matching SID record in the sys.server_principals table.   This is not an issue if your databases is CONTAINED and uses database authentication. Otherwise, the user will not be able to login into the sql server instance and as a result cannot access the database even though the user has access to the database. Generally, you will get orphaned database users after restoring a database to a different server and one or more users in the database do not have corresponding LOGIN at the instance level or has mismatched SID.  Another possibility is that the login got deleted from sys.server_principals or from the Active Directory or local machine. I am sure there are other possible situations. Microsoft h...

I was at a client where they are using non-Microsoft clustering technology to achieve the high availability of SQL Server instances. This was party because of legacy reasons and partly because it supports clustering across all major hardware, operating system and applications including SQL Server. SQL Server instances are setup in either 2 or 3 nodes active/passive, active/active etc. configuration. There are about 30 physical servers hosting sql server instances.  Yes, the client is going to move all the SQL workloads to Always On Clusters but the process has been slow because all the databases are used for COTS/third party applications. A virtual name is used to make connection to a sql server instance.  Often I would need to know actual physical node name where a particular sql instance is active and, I needed to find it out programmatically. You may have different reason/s where you are connected to SQL server using a virtual nam...

This is actually part of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there will be a series of handful of posts. In case you are wondering if I am reinvesting the wheel here, you are right, in most cases. As a consultant and visiting DBA, I have good reasons to resort to this. Fortunately I already had the scripts so this is more about automating the whole process. This is part 1 in the series. It finds new servers added to the AD. At this stage, we would not know if any of those servers are SQL Servers. That will be in my next blog! It will display the results of discovery to the console as well as export to CSV. Please feel to comment/change anything you would like. Before trying this script, please review and adjust the default values for the variables. <# You will need powershell active directory module installed on the computer where you are running this script from. If you are using a W...

We know that indexes are necessary for query performance but comes with overhead during DML operations. Usually, the overhead is worth the performance gain we get out of them. What I needed was to find and consider for removal indexes that incur heavy maintenance overhead, but are only lightly used for queries or enforcing constraints. Here is an a version of query to find unused or lesser used indexes. It looks for non-unique indexes with large number of DMLs with ratio of writes vs reads is relatively high (I am using 10 but at this point its an arbitrary starting point). WITH   cte       AS   ( SELECT   Db_name ( iu . database_id )                     db_name ,                  Object_name ( iu . object_id ,   iu . database_id )   ob...

If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is every approximately 10%. You can change that frequency interval by specifying STATS [ = percentage ] option. BACKUP DATABASE [AdminDBA] TO  DISK = N'O:\MSSQL13.SQL2016AG01\MSSQL\Backup\AdminDBA.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdminDBA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO But what if the backup/restore was started from a different session that you don't have access (another DBA, scheduled job etc.) or you need more information to troubleshoot issues? Here I should first mention that there are already couple options to track the progress. You could review or query the sql server error logs (unless trace flag 3226 is enabled). By default its disabled. You can if that trace flag is enabled using: DBCC TRACESTATUS ( 3226 ); DBCC TRACESTATUS ( 3226 ) If trace fla...