How to find what all features of SQL Server are installed in the server.

I received a query from one of my team member: Is there a way to find out what all components of SQL Server are installed in a server?

This is a useful information when we want to set up a new SQL instance similar to an existing instance. Below is how we can find out.

1.   Login to the server you want to find out what all SQL features are installed in it. In my case its TEST server.

2.   Open SQL Server Installation Center.

3.   In the installation center click on the ‘Tools’ tab and click on ‘Installed SQL Server features discovery report.’.

4.   It will open up a report like this.

Please add in comments in what other ways we can find out what all SQL features installed in a server.

Thanks VV!!


#sqlfeatures, #sqlserver, #featuresinstalled, #installed, #sql, #server #report

Please create a master key in the database or open the master key in the session before performing this operation. (Microsoft SQL Server, Error: 15581).

In one newly configured Integration Services Catalog instance while deploying a project through *.ispac file, the deployment failed at ‘Deploying project’ step as shown below and has thrown below error.

Below is the error received:

Please create a master key in the database or open the master key in the session before performing this operation. (Microsoft SQL Server, Error: 15581).

To fix this error we need to open the master key manually by using below command.

USE [SSISDB]

OPEN master Key decryption by password = 'Password'

But I was not able to run the above command due to a special character (which is a single code symbol ) in my master key password. So to overcome this error I have to change the password of the master key first.

How to change the password of the master key?

To change the password of the master key, use the below command as mentioned here. Make sure you run this on the SSISDB database if not it will throw an error.

USE DatabaseName;

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'New_Password';

GO

Now I tried deploying the project again using the same *.ispac file and this time it completed successfully.

If you noticed I have tried deploying the project without opening the master key and it completed successfully so in my scenario the issue was with the special characters in the password so after changing the password the project deployment completed successfully.

Points to Note:

·        If you are receiving an error stating create a new master key or open the existing master key, then you need to open the master key manually with the command provided above.

·        If you want to change the password of the master key you can use ALTER MASTER KEY command as mentioned above.

Let me know is there any other way you fixed this issue in your environment in the comment section below.

Thanks VV!!

#SSISDB; #SQLServer; #SQL; #SSISCatalog #IntegrationServicesCatalog

How to remove log shipping when secondary server not available.

Recently I was working on a task to remove log shipping on one of the PRIMARY servers but it failed with below error. After checking realized that the secondary server was already decommissioned. So the log shipping components are not getting removed completely.

“SQL Server Management Studio Could not delete Log Shipping Configuration. Object reference not set to an instance of an object.”

To remove log shipping components from the PRIMARY server completely we first need to find on which databases log shipping is still enabled by using the below query:

Select primary_database FROM msdb..log_shipping_primary_databases;

Now run below 2 queries by replacing with server and database names to remove the log shipping components:

USE [master]

GO

EXEC sp_delete_log_shipping_primary_secondary @primary_database = '[Database_Name]', @secondary_server = '[Secondary_Server_Name]', @secondary_database = '[Database_Name]';

GO

EXEC sp_delete_log_shipping_primary_database @database = '[Database_Name]'

Thanks VV!!