Connect PerfMon to a Remote Server

I faced several connectivity issue during connecting Perfmon from remote server.

Server Access denied
Solution: PerfMon is trying to connect the remote server using logged in user account which have no access on remote server. Now, I need to run perfmon using a different user account which has access to the remote server.

DNS not able to resolve the server name
Solution: I have fixed the issue by adding an entry in the hosts file (C:\Windows\System32\drivers\etc).

Unable to connect the server
Solution: I have checked & found that Perfmon is trying to use Port 445 for connectivity. Telnet showed that port number 445 is blocked. I have added port into firewall exception & it worked.

Check Expiry Date of SQL Server Evaluation Version

SQL Server Evaluation Version is available free of cost for evaluating the Server. It is a trial version. It is valid for limited time (180 Days). The expiry date is always 180 days from the initial installation Date. The following query will return the expiry date of SQL Server Evaluation Version:

SELECT @@SERVERNAME AS Server_Name, Create_Date AS Installation_Date, 
ServerProperty('EDITION') AS [Version], DATEADD(DD, 180, Create_Date) AS [EXPIRY DATE]
FROM SYS.SERVER_PRINCIPALS 
WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is associated with login 'NT AUTHORITYSYSTEM' and it is created at the time of installation only.

List all Disabled Constraint in a database

Use below query to list out the disabled constraint in a database:

SELECT OBJECT_NAME(CONSTID) AS Constraint_Name,
OBJECT_NAME(FKEYID) AS Table_Name,
COL_NAME(FKEYID, FKEY) AS Column_Name,
OBJECT_NAME(RKEYID) AS Referenced_Table_Name,
COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name, 
(CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS [Status]
FROM SYSFOREIGNKEYS
WHERE (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) = 'DISABLED'
ORDER BY Table_Name, Constraint_Name, Referenced_Table_Name

DMV to check SQL Server edition specific feature

There are some features of Server Server Database Engine which change the way that Database Engine stored information in Database Files. These features are restricted to specific edition of SQL Server. If a database is containing these feature than that database cannot be moved to some other edition of SQL Server (Which does not contain that Feature).You can use below DMV to find all edition specific features enabled in the current database. 

SELECT DB_NAME() AS [Database Name], FEATURE_NAME AS [Feature] FROM SYS.DM_DB_PERSISTED_SKU_FEATURES

This DMV can check below four feature: 

• Change Data Capture 
• Data Compression
• Transparent Data Encryption
• Partitioning

Error showing while opening SSMS 2012, Value cannot be null

Today when I was opening SQL Server Management studio in SQL Server 2012, I got below error:

Value cannot be null. Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)

To solve above problem I done below steps:

1. Open Windows Explorer, paste the %USERPROFILE% in the navigation bar,
or go to C drive --> Users --> Your user ID.

2. Go to the following directory My Documents\SQL Server

Management Studio\Settings and delete all the files exists there.

3. Restart the SQL Server management Studio.

And problem is solved.