Vision: SQL Homeostasis

Recently I have had to troubleshoot quite a bit of SQL login issues and often times the issue was with the users active directory user account. I was aware the my organization has security policies that includes among other things, active directory password policy. Of course there is also SQL Server user security policy, which I know by heart. To better support my users, I thought it would be a good idea to familiarize myself with the active directory password policy that is in effect. Now, there is a document that outlines the policy but, I want to get what is actually implemented. Here, I am using powershell to get the password policy values. You will need to have the ActiveDirectory PowerShell module installed for the following cmdlets to work. Get-ADUserResultantPasswordPolicy -Identity aduser1 That returned nothing in my case. That most likely implies that the password policy is not assigned per user basis. So now I am going to check what is the default password policy in the...

If your organization uses a password policy (there are very good odds these days that it does) and, especially stricter password requirement for administrative users, your might have experienced instances where yours or your users Active Directory user might be locked out. How do you check if that is the case? Well, for one thing the Windows will tell you so when you try to login and/or failed login attempts are logged in to sql log, event logs etc.  What if user does not logout or have more than one user account, one for regular use and one for administrative tasks? There maybe other scenarios where you have a need to check status of a user account in the Active Directory. I don't have admin privileges and presumably you don't either.  However, I do have read permission on the AD so I could have used Active Directory Users and Groups snap-in. But, here I am going to show the powershell way. You will need to have the ActiveDirectory PowerShell module installed for the fol...

Most of the times I use the CSV files whenever I need to import or export SQL Server data. And then if I need to do further analysis on the data or simply beautify the results to share with the users or colleagues, I simply open the CSV file in Excel and do the rest of the work manually. But what if I could just skip the CSV and export data directly to Excel format? Wouldn't that save me time and efforts and also help me to automate if I wanted to? No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site. https://www.powershellgallery.com/packages/ImportExcel/5.2.0 You can import the module directly from there or do the manual download. I decided to use the import method. For that I would need to have the PSGallary as one of the registered repositories in the PowerShell If you don't already have registered the Powershell Gallary as one of the repository, there are couple methods depending on the PowerShell version you have. I have the 5.x ve...

If you are new at your job or a new client you would like to gather and review the sql server meta data to understand the environment and get up to speed as quickly as possible. Most of us are already familiar with the DMVs, system functions, procedures etc. to gather the SQL server metadata. And if you want to gather the information for all your SQL servers, you could run a multi-server query against all through the Central Management Server. In fact, in newer versions of SSMS you don't even need the CMS, you just register all your sql instances in the Local Server Groups. So from that perspective this post is not adding much values except maybe that it is another alternative to SSIS or other ETL tools to capture the meta data on regular basis. If nothing else I hope you find this educational regarding how to use powershell to interact with sql servers. <# Export SQL Server properties, settings and configuration values to CSV files #> # name of the sql server instance you wo...

At one of my clients I received an email from one of the IT Project Managers asking a simple question: "Can you please let us know which databases reside on the server below, Server1?" First thought in mind, well from what particular sql instance on that server? At that point I was not even sure if that server has multiple instance, is it a stand alone sever or a node/virtual name of a cluster server, alwayson cluster etc... But I kept that thought to myself. Now, I could launch SSMS, connect to the sql instance, query the sys.databases and get requested information. But I don't know the instance name top of my head. So I would need to RDP into the server or look up the meta data somewhere. Instead of that, I decide to launch the Powershell and issue this command: Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "Server1" It has only once sql instnace, great. Then I issued the following c...

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

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