Query VMWare vCenter VCDB database for guest power state = OFF

Question: I want to identify virtual machines which are powered off. What query can I run which will return the full list of virtual machines within a VMWare vCenter database where the power state = OFF. Answer: It is possible to return the recordset by querying the database. Find a sample query executed on a vcDB database. The query includes some extra information , which you may find useful . The key column is the POWER_STATE column in the vpxv_vms table. There are different potential power states for a virtual machine . Possible values are off, on, and suspend SELECT... Read more →

How to find the local tcp port used on SQL Server

Question: How can I find the tcp port number used by a session ? I want to use a SQL Query to find this information as I don't have access to the cmdline. Answer: One method to use from within SQL Server is the sys.dm_exec_connections DMV . This SQL Server DMV returns various details about the current connections to a SQL Server Instance. This query returns connections information for all existing connections. One of the columns is the "local_tcp_port" , and when the net_transport is TCP - there will be a value in the "local_tcp_port" SELECT suser_sname() as username ,local_tcp_port... Read more →

SQL Server database roles and GDPR

Corporate Audits and other regulatory initiatives such as GDPR , it is increasingly important to apply solid database access security practices. Users should only be able to view approved data . Avoid applying wider roles to users , such as db_owner when all the user needs is some access to 2 tables and a stored procedure. One of the tools available to the DBA is the database role. The principle of the database role is basic , and any sql server database comes with a set of default database roles such as db_datareader and db_owner. Always using the default database... Read more →

Checking compatibility for a SQL Server database migration to Azure with sqlpackage.exe

The SSMS installation comes with a command line utility called sqlpackage.exe . Commonly used to export sql server databases to Azure. On a typical default installation the path will be like : C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe One of it’s features is the compatibility checks ,generating a report of potential issues The basic structure for using the compatibility checks is : 'sqlpackage.exe /Action:Export /ssn:< server_name > /sdn:< database_name > /tf:< target_file > /p:TableData=< schema_name.table_name > > < output_file > 2>&1' Argument Description < server_name > source server name < database_name > source database name < target_file > file name and... Read more →

How to enable MSDTC XA transactions with Powershell

Question: I've read your post on Enable XA transactions for SQL Server , which outlines how to enable XA transactions via the Computer Services module. I'd like to integrate the enabling of XA transactions as part of a scripted SQL Server installation, whoch includes enabling XA transactions for some applications. I'm using Powershell to develop the script . How can I see the current value and also change the value to enable XA ? Answer: It is possible to access and set the MSDTC XA Transactions values using Powershell . You need to make sure your current login has the... Read more →

How to create a graph table in SQL Serevr 2017

Question: How do I create a graph table in SQL Server 2017? Answer: To create a graph database table uses similar syntax as the CREATE TABLE syntax with the addition of the AS NODE keyword or the AS EDGE at the end of the CREATE TABLE syntax. Normally you would create a table such as : CREATE TABLE dbo.MyNode (ID int , NAME varchar(30)); In the graph table CREATE you would do it like: CREATE TABLE dbo.MyNode (ID int , NAME varchar(30)) AS NODE; or CREATE TABLE dbo.MyEdge(ID int , NAME varchar(30)) AS EDGE ; Once you've created the tables... Read more →

How to use parsename with SERVERPROPERTY('productversion')

Question: I'd like to use SERVERPROPERTY('productversion') for purposes of reporting on current sql server level. I'm using the SERVERPROPERTY('productversion') funcion , but am struggling with how to split the parts of the details , to make it easier for reporting. For example : 13.0.5081.1 , I'd like to split as Major = 13 Minor = 0 Build = 5081 Revision = 1 Answer: The PARSENAME function can be used to split the SERVERPROPERTY('productversion') and avoid using various string functions. PARSENAME is commonly used to split object names into there constituent parts. Here is an example: select parsename('master.dbo.sysdatabases',1) select parsename('master.dbo.sysdatabases',2) select... Read more →