Running SQL Queries On Sitecore Databases Using Sitecore PowerShell Extensions

 


Whether you are developing on Sitecore or supporting a live Sitecore instance, there are times you need to access the SQL databases to run queries to fetch some data. While the Sitecore APIs provide access to the items in the content tree, they are not very useful when you want to check the old login tickets in core database, or need to inspect the JWT tokens received from identity provider when using federated authentication. 

There are many reasons for a Sitecore developer to want to access the Sitecore database. I am sure there are equal if not more number of reasons for the security team to deny access. Many a times you would be an admin on the Sitecore instance but won't even have read-only access to the SQL databases.

In this article I am going to show how you can run simple queries on the Sitecore databases using the power of SPE. For this to work, you need to have access to use the PowerShell ISE on the Sitecore instance.

First, we will need to fetch the connection string of the Sitecore database we would like to run the queries on. We can use the [Sitecore.Configuration.Settings]::GetConnectionString method to get the connection string by database name. 

Once we have the connection string we can use PowerShell's Invoke-SqlCommand cmdlet to run our query against the database. In the below example, I am running a SQL query against Core database to get the count of bearer tickets in Properties table to identify old login tickets and delete them to improve the CMS login times. Too many old tickets (because of people using Rememeber me option) slows down the CMS login performance as Sitecore need to check for existing tickets before creating new one (check this blog from George Tucker for more details).
 
The above script gives the count of tickets that are older than a given date. Now I can use a Delete query to delete all these old tickets.

This method can be used to run any queries against master or web or core databases without having to connect to the databases using SQL Server Management Studio.

However, always remember With Great Powers Comes Great Responsibility. Use your powers wisely or else you will be stripped of your Sitecore admin privileges.

Comments

Popular posts from this blog

SXA Scriban extension to get link field target URL

View and download Sitecore log files using Log Viewer

How to ace your Sitecore .Net Developer 10 Certification Exam