Powershell script to delete SQL server databases matching a filter

I previously shared a SQL server script to bulk delete all SQL server databases which matched a search filter (http://tinyurl.com/oucvxfs), I have recently written a powershell script to perform the same process and to remove the database backup history. Firstly, we will need to specify the parameters required to run the script. Param ([Parameter(Mandatory=$true)][string] $ServerInstance, … More Powershell script to delete SQL server databases matching a filter

Enabling Inbound Windows Firewall rule for Analysis Services

As part of an automated deployment of a server hosting SQL Server Analysis Services, the inbound firewall rule was required to be created. This process was achieved by modifying the network configuration from a command line using the netsh.exe utility as a deployment step. The TCP service port required to allow inbound connections on  a default named … More Enabling Inbound Windows Firewall rule for Analysis Services

Working with SQL collation on Amazon Web Services instances

So when deploying a Amazon Machine Image (AMI) to a new instance in Amazon Web Services EC2 where SQL Server is installed as part of the AMI, you inherit the SQL server collation for that particular install. This is one issue I have experienced where the SQL Server collation of the AMI has been ‘SQL_Latin1_General_CI_AS’ and I … More Working with SQL collation on Amazon Web Services instances

Checking to see if CLR Integration is enabled for SQL Server

In order to check if command language runtime (CLR) integration feature is enabled within SQL Server, run the following query agaisnt the master database: SELECT * FROM sys.configurations WHERE name = ‘clr enabled’ If CLR is enabled the value returned will be ‘1’, as displayed in the ‘value’ row. By default this feature is turned … More Checking to see if CLR Integration is enabled for SQL Server

Automate SSAS database role group creation and cube permissions

I was recently required to create an automation task that would apply security settings to a all databases in a SSAS instance. The SSAS instance was created from a snapshot where no existing permissions existed on the source. This therefore required me to perform the following; 1) Create a database role for each database. 2) … More Automate SSAS database role group creation and cube permissions