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...