Tag Archives: HowTo

All topics “HowTo” in Microsoft SQL Server.

#0423 – SQL Server – Exporting Database Diagrams for offline viewing


We a colleague of mine used Database Diagrams to explain our database structure to a new team member. Now typically, we would have started to “draw” the relationships on a white board, but in a world where everyone was working remotely, this was the only option.

The Visual Aspect

I am sure almost everyone in the audience has also used database diagrams at some point as a means of database documentation before switching to methods (like Extended Properties) more suited to modern database development and deployment techniques.

Unfortunately, these techniques do not have the ability to demonstrate the relationships visually. Database diagrams provide this unique ability and hence, warrant a rethink in terms of usage.

Now, the goal of this post is not to show “how” to build a database diagram, but to:

  1. Demonstrate effective ways of providing as much detail as possible on the diagram
  2. Show how to export the diagram for offline reference

Increasing the level of detail in a Database Diagram

For effective database diagraming, the recommendation is to group the tables/information shown on the diagram by one of the following two (2) strategies:

  1. By schema: If you use database schemas, group objects by schema and have at least one database diagram per schema
  2. By Use-case: Alternatively, tables that are related from a domain perspective (or for a particular use-case, e.g. Authentication) can be selected and be the subject of a diagram

Once you have put all the required tables on the diagram, you may want to right-click on the canvas and choose “Show Relationship Labels”. Additional annotations may also be applied as necessary by using the “New Text Annotation” functionality.

Image showing how to enable visibility of relationship labels on the diagram by right-clicking on the canvas and choosing "Show Relationship Labels"
Image showing how to enable visibility of Relationship labels on a database diagram

By default, the database diagram will only show the table name and list of columns. For maximum details, you can right-click on the table name -> select “Table View” -> select “Standard”.

Screenshot showing how to select the "Standard" view of tables on a database diagram. This will add more details (like datatype and null-ability of columns).
Screenshot showing how to select the “Standard” table view, which increases the level of detail on the diagram

As you will notice, using the “Standard” table view will add more details (like datatype and null-ability of columns) on the diagram. The columns can be added/removed by using the “Modify Column” option of the same menu.

Screenshot showing the "Modify Columns" screen which allows the user to select/choose columns on the table that may be necessary to review the design.
Column selector for the “Standard” view
Image showing how the standard view adds more details (like datatype and null-ability of columns) on the diagram.
“Standard” view of database tables on a diagram.

This process will need to be done for all tables. Once done, arrange the diagram on the canvas manually.

Exporting the Database Diagram

Once a database diagram is prepared, it can be saved in the database. However, there is no way to export or save a diagram into a file that can be sent via E-mail or stored on a collaboration tool for offline viewing. There is however, a very simple way by which the ultimate goal can be achieved – by storing it as an image!

Now, I am now talking about taking multiple screenshots and stitching them together in an image editing app. It is very simple to copy the diagram as an image.

Simply right-click on the canvas and choose “Copy Diagram to Clipboard”

Screenshot showing how to copy the database diagram to the clipboard.
Image showing how to copy the diagram to clipboard.

Once the diagram is on the clipboard, it can be pasted as an image to any image editing application or document!

Further Reading

I trust this little tip comes to your assistance someday.

Until we meet next time,

Be courteous. Drive responsibly.

#0418 – SQL Server – How to disable Shared Memory connections and configure a SQL Server instance to accept connections only via TCP/IP?


As part of my normal development activities, I use my trusted developer instance on my local machine. However, one of the main things I realized was that while I could connect to the instance using SQL Server Management Studio (SSMS), I could not connect to my instance via some of my SSIS packages.

I realized shortly afterwards that this was because the instance only has “Shared Memory” enabled as the protocol for connections by default. I turned on TCP/IP as well and was able to complete my work. However, I thought it best to document the steps I took for future reference.

What is my current connection using – Shared Memory or TCP/IP?

Before we go any further, let us investigate the default connection mechanism used by client applications running on the same machine as the SQL Server instance.

To do this, I have opened connections to the SQL Server via both – SSMS and SQLCmd and am then querying the DMV (sys.dm_exec_connections) to investigate the protocol being used for the connection. Session Ids used by each connection have been highlighted in the image below.

A connection to my local SQL Server instance via SQL Server Management Studio
Another connection using SQLCMD to the same SQL Server instance

Now, because my connections are active, I can take their session Ids and query the DMV – sys.dm_exec_connections which will give me the physical transport protocol that is used by this connection.

As can be clearly seen, the physical transport protocol used when connecting to a SQL Server on the same machine is “Shared Memory” by default.

If I explicitly try to connect to the instance using TCP/IP, note that I get an error #26 (Error Locating Server/Instance Specified):

Error 26 (Error Locating Server/Instance Specified) when connecting to the SQL server using TCP/IP network protocol in the SSMS “Connection Properties” window

Enabling TCP/IP

In order to change the connection, one needs to use the SQL Server Configuration Manager.

In the Configuration Manager, when we navigate to the SQL Server Network Configuration -> Protocols for <SQL Server Instance>, we notice that TCP/IP and Named Pipes are disabled – only the Shared Memory protocol is enabled.

Notice that by default, only the “Shared Memory” physical transport protocol is enabled

Now, all that needs to be done is to enable TCP/IP from the Protocol properties (right-click -> Enable or simply double-click to open the properties window) and restart the SQL Server service.

TCP/IP connections are now enabled.

Specifying Transport Protocol when connecting to the database

Now that I have reconfigured the SQL Server instance, I can now specify the protocol when connecting to a SQL server:

  1. In SSMS, when connecting to a SQL Server, click on “Options”
  2. Under “Connection Properties”, choose “TCP/IP” as the connection protocol
Accessing the “Connection Properties” screen when connecting to an instance using the Management Studio
Choosing the Network Protocol as “TCP/IP”

When I use the DMV (sys.dm_exec_connections) to check the session, I can see that it is now using TCP/IP and not Shared Memory.

Connections to the SQL Server are now using TCP/IP as the physical network protocol

How to disable Shared Memory?

One of the questions that we started with was how to disable “Shared Memory” for connections?

This can be achieved in the same way as we enabled TCP/IP. Simply use the SQL Server Configuration Manager to disable the “Shared Memory” protocol.

Disabling the Shared Memory protocol

I hope you will find this post helpful.

Disclaimer: Please DO NOT try this on your production SQL Server instances.

References:

Until we meet next time,

Be courteous. Drive responsibly.

#0415 – SQL Server – Performance Tuning – Use STRING_AGG to generate comma separated strings


With more and more data being exchanged over APIs, generating comma-separated strings are becoming a much more common requirement.

A few years ago, I wrote about two different ways to generate comma-separated strings. The most common one I find to be in use when generating comma-separated values from a table is the intermediate conversion of XML. This however, is a very costly mechanism and can potentially take minutes for the query to run depending upon the amount of data involved.

SQL Server 2017 brings a new aggregate function that can be used to generate comma-separated values extremely fast. The function is STRING_AGG().

Here’s a sample of it’s usage:


 --WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
-- WARRANTY.
-- FOR DEMONSTRATION PURPOSES ONLY
--Step 01: Generate Temp table to store source data
DECLARE @NamesTable TABLE ([Id] INT,
[Name] NVARCHAR(50)
);
--Step 02: Generate test data
INSERT INTO @NamesTable
VALUES (1, 'A'),
(2, 'D'),
(2, 'C'),
(3, 'E'),
(3, 'H'),
(3, 'G');
--Step 03: Using STRING_AGG to generate comma-separated strings
SELECT STRING_AGG(tbl.Name, ',') AS [CommaSeparatedString]
FROM @NamesTable AS tbl;
GO
/RESULTS**
CommaSeparatedString
A,D,C,E,H,G
*/

Advantages of STRING_AGG:

  • Can be used just like any other aggregate function in a query
  • Can work with any user supplied separator – doesn’t necessarily have to be a comma
  • No manual step required – Separators are not added at the end of the concatenated string
  • STRING_AGG() is significantly faster than using XML based methods
  • Can be used with any compatibility level as long as the version is SQL Server 2017 (or higher) and Azure SQL database

Here’s an example of how STRING_AGG can be used with any separator:

 --WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
-- WARRANTY.
-- FOR DEMONSTRATION PURPOSES ONLY
--Step 01: Generate Temp table to store source data
DECLARE @NamesTable TABLE ([Id] INT,
[Name] NVARCHAR(50)
);
--Step 02: Generate test data
INSERT INTO @NamesTable
VALUES (1, 'A'),
(2, 'D'),
(2, 'C'),
(3, 'E'),
(3, 'H'),
(3, 'G');
--Step 03: Using STRING_AGG to generate comma-separated strings
SELECT STRING_AGG(tbl.Name, '-*-') AS [CustomSeparatorString]
FROM @NamesTable AS tbl;
GO
/RESULTS**
CustomSeparatorString
A--D--C--E--H--G /

A minor challenge

As with every new feature, there may be a small usability challenge with STRING_AGG. One cannot use keywords like DISTINCT to ensure that only distinct values are used for generating the comma-separated string. There is however a Azure feedback item open where you can exercise your vote if you feel this feature is useful.

Further Reading

  • Different ways to generate a comma-separated string from a table [Blog Link]
  • STRING_AGG() Aggregate Function [MSDN BOL]

Until we meet next time,

Be courteous. Drive responsibly.

Import Event Viewer Logs into Excel

#0414 – Analyzing Event Viewer Logs in Excel


When troubleshooting issues, the Event Viewer is one of the most handy of all tools. Assuming that appropriate coding practices were used during application development, the Event Viewer contains a log of most problems – in the system, in the configuration or in the application code.

The only problem is analyzing the Event Viewer logs when you have a thousand events. It becomes extremely difficult to try and answer questions like the following while going through events serially:

  1. Events logged by type for each source
  2. Events by severity
  3. Events by category
  4. And many more such analytical questions…

These analytical requirements are best achieved with tools like Microsoft Excel. And so, I went about analyzing Event Viewer logs in Microsoft Excel in just 2 steps.

Step #1: Export the Event Viewer Logs to XML

  1. Once the Event Viewer is launched, navigate to the Event Log to be evaluated
  2. Right-click on the Event Log and choose “Save All Events As” option
  3. In the Save As dialog, choose to save the Events as an XML file
    • If asked to save display information, you can choose not to store any or choose a language of your choice

And that’s it – it completes the 1st step!

Screenshot showing how to Save the Event Viewer Logs
Save the Event Viewer Logs
Screenshot showing how to save the Event Viewer Logs as an XML file
Choose to save the Event Viewer Logs as an XML file

Step #2: Import the XML file into Excel

  1. Launch Microsoft Excel
  2. In the File -> Open dialog, choose to search files of “XML” type
  3. Select the exported Event Viewer Log file
  4. In the Import Options, you can choose to import as an “XML Table”
    • Excel will prompt to create/determine the XML schema automatically. It’s okay to allow Excel to do so

And that’s it – the Event Viewer Logs are now in Excel and you can use all native Excel capabilities (sort, filter, pivot and so on).

Choose to import the Event Viewer Logs into Excel as an XML table
Import the Event Viewer Logs as an XML table
Image showing the successfully imported Event Viewer data into Microsoft Excel
Event Viewer Logs successfully imported into Excel

I do hope you found this tip helpful. If you have more such thoughts and ideas, drop in a line in the Comments section below.

Until we meet next time,

Be courteous. Drive responsibly.

#0405 – SQL Server – Msg 5133 – Backup/Restore Errors – Directory lookup for file failed – Operating System Error 5(Access is denied.).


We got a new server recently and one of my colleagues ran into an error when restoring a database. The error was a quite generic (reformatted below for readability):

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file 
"C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf" 
failed with the operating system error 5(Access is denied.).

Msg 3156, Level 16, State 3, Line 1
File 'AdventureWorks2014_Data' cannot be restored to 
'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf'. 
Use WITH MOVE to identify a valid location for the file.

My immediate reaction was to  review the restore script.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2014]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak'
WITH
MOVE 'AdventureWorks2014_Data' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Log.ldf';
GO

All looked well, I subsequently moved to the environmental aspect of troubleshooting. It was a new server and we had just created the target folders to which the database was to be restored.

We attempted to restore to the default database locations configured during SQL Server installation and the restore worked. So, one thing that became clear: the SQL Server service did not have appropriate security rights on the destination folder.

The Solution

Once we determined that it was the security rights on the destination folder, the only thing remaining was to grant the rights. Here’s how we do it.

  1. Cross-check the user set as the service user for Microsoft SQL Server database engine (use the SQL Server Configuration Manager for interacting with the SQL Server service – here’s why).
  2. Under Folder properties, ensure that this user has full security rights (or at least equivalent to the rights assigned on the default database folders specified at the time of installation)

Here’s are detailed screenshots showing the above process.

01_SQLServerConfigurationManager

Identifying the user running the SQL Server Database Engine service

02_GrantingPermissions_01

Navigating into file system folder security options to grant access to the SQL Server service

02_GrantingPermissions_02

Choosing the appropriate account running the SQL Server service

02_GrantingPermissions_03

Applying appropriate rights to folder

By the way: If you encounter similar issues in accessing your backup files, the root cause and solution are the same. Check the permissions on the folders housing your backups and you should  see that the database engine does not have the necessary security rights.

Further Reading

Maintaining permissions on data folders and appropriate registry entries is something that is handled by the SQL Server Configuration Manager when you change the service account under which  the database engine is running. If you use services.msc (the old way), this is not done and your SQL Server may stop working.

  • Changing SQL Server Service Account or Password – Avoid restarting SQL Server [Blog Link]
  • Blog Post #0344 – SQL Server – Missing Configuration Manager on Windows 8 [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.