Tag Archives: Administration

Articles related to Microsoft SQL Server Administration

#0425 – SQL Server – Backup exists but doesn’t display on the restore window in SSMS. Why? How to fix?

Recently, I ran into a forum post where the ask was to figure out why a perfectly valid backup was not visible when attempting to restore it via the wizard in SSMS. Today, I will reproduce the issue, explain the root cause and provide the solution for the same.

Building the scenario

In one of the my test SQL Servers, I have a copy of the [AdventureWorks2019] sample database, which I have backed up using the following simple script.

USE [master];
GO
BACKUP DATABASE [AdventureWorks2019]
 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2K17\MSSQL\Backup\AdventureWorks2019.bak'
    WITH COMPRESSION;
GO

Now, to simulate the movement of the backup to a different machine, I created a new folder under my default “Documents” folder and placed the backup there.

Screenshot showing the placement of the target folder where the backup is placed

Reproducing the symptom

Restoring via SSMS

Connect to the target SQL Server using SSMS
Right-click on the “Databases” folder in the Object Explorer
Choose to Restore a database
Under “Source”, select the radio-option for restoring from a “Device”
Use the ellipsis to open the “Select Backup Devices” window and open the File explorer by choosing “Add”
Navigate to the folder where the backup has been placed
1. Expected Result: We should be able to see the folder and the backup file
2. Actual Result: The backup file is not seen (the folder may or may not be seen)

Screenshot showing that the backup exists, but it is not seen in the "Locate Backup File" window — Screenshot showing that the backup exists, but it is not seen in the “Locate Backup File” window

Restoring via T-SQL

While the UI keeps things a bit mysterious, attempting to restore via T-SQL does point us to the right direction.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2019_Copy]
    FROM DISK = 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak';
GO

Here’s the error that we run into:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Notice that the error clearly says – “Access is denied.“

Root Cause

As highlighted by the results of the T-SQL script, SQL Server is actually running into a security problem. The operation is done under the context of the SQL Server instance service user (i.e. the user under which the SQL Server service runs).

Because the user doesn’t have access to the folder we just created, the service cannot see the files underneath.

Solution

The solution is to use the SQL Server Configuration Manager to figure out the user under which the SQL Server service runs.

Once the user is identified, provide access to the target folder to the user and the files should now be visible – both to SSMS and to T-SQL.

Screenshot showing the backup file is now visible once the SQL Server instance service has been granted to the folder

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

1 Reply

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:

Demonstrate effective ways of providing as much detail as possible on the diagram
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:

By schema: If you use database schemas, group objects by schema and have at least one database diagram per schema
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!

#0422 – SQL Server – SSIS – Delete or rename files and perform other file operations without a script or writing code

The Solution

Assume that I have a set of files in a folder (following the pattern – SQLTwins*.txt) and I wanted to delete them. The Control Flow is quite procedural:

A ForEach Iterator is used to iterate through files in the directory
- The iterator is completely configurable – allowing the user to specify the folder name and the file name pattern
A FileSystem task is used to perform the actual file operation

Control Flow of a package that manipulates files without a script or code!

Now, allow me to walk-you through the package configuration:

Variables

The package is dependent upon the following variables:

Variable Name	DataType	Expression / Default Value	Remarks
SourceFolder	String	(My source folder path)
FileNamePattern	String	SQLTwins*.txt
CurrentFile	String		Variable to hold current file being iterated upon by the ForEach Iterator
FullyQualifiedFileName	String	SourceFolder + CurrentFile	Fully-Qualified file name to be used by the FileSystem task

List of User Variables on the SSIS pacakge

ForEach Iterator

The configuration of the Foreach Iterator is quite simple:

Collection
- The “Descriptions” and “FileSpec” expressions are set with the user variables – “SourceFolder” and “FileNamePattern” respectively
Variable Mappings
- This allows the package to capture the output of the iterator
- The variable “CurrentFile” will be used to capture the current file name

“Collection” tab of the ForEach Iterator showing “Descriptions” and “FileSpec” expressions set with the user variables – “SourceFolder” and “FileNamePattern” respectively

Variable Mappings showing the output of the ForEach Iterator setting the “CurrentFile” variable

File System Task

The configuration of the FileSystem task is even simpler! Other than the Name, the only configuration I did was to set the “Operation” and the “SourceVariable” variables.

That’s it! We are all set to give the package a spin and did not write a single line of code!

When we run the package, we can see right away that the files have been deleted.

Prior to execution, we can see that the files are still present.

Once the package is executed, the files are deleted!

The intention of the post was to demonstrate that with Microsoft SQL Server and related services, there are tools and components available which allow one to get started extremely quickly. If you have never worked with SSIS before, do explore the components available in the SSIS toolbox before getting into some serious scripting!

#0420 – SQL Server – SSIS – Does SSIS use TCP/IP by default?

Does SSIS always use TCP/IP?

To find the answer to this question, I devised a simple test. I wrote a simple SSIS package and went about checking the connection properties of the connections opened by the SSIS package.

The test package

The test package is quite simple. I have a Data Flow task with two (2) – OLEDB connection managers – one connected to the [AdventureWorks2019] sample database and the other connected to the [tempdb] database.

The Data Flow task simply pumps all rows of the [AdventureWorks2019].[dbo].[Employee] table to an identical test table [tempdb].[dbo].[dEmployee] that I had created before the test.

To allow for easier monitoring of the connection, I have also added a Script Task with a simple 1 second (1000 milli-second) sleep interval in the processing of each row.

The image shows the internals of the data-flow task. At the top, I have a OLE DB source pumping data to an OLE DB Destination through a Script Component. — Sample SSIS Package – Internals of the Data flow task

If you are interested, here’s how the Script task has been configured:

Script task showing all input columns as selected and directly getting transferred to output stream.

Here’s the code for the the 1 second delay (1000ms = 1 sec):

/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
///  string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
///  Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    //Introduce 1 second sleep at each row
    Thread.Sleep(1000);
}

Test Execution & Results

The test strategy is quite simple – to run the package and monitor the connections opened by the package based on the hosting process.

From Visual Studio

When the package is executed from Visual Studio, the SSIS package is executed under a Debug Host Process. So, with the package running, I use the Windows Task Manager to find the host process Id of the debug host process.

Finding the Host process PID for the DtsDebugHost.exe process.

With once the process Id has been identified, we will head over to the SQL Server Management Studio (SSMS) and run the following query (after substituting the various Ids).

SELECT [session_id],
       [host_name],
       [program_name],
       [host_process_id],
       [client_interface_name],
       [database_id],
       DB_NAME([database_id]) AS [DatabaseName],
       [row_count]
FROM [sys].[dm_exec_sessions]
WHERE [is_user_process] = 1
  AND [host_process_id] = 4388; --This is the PID from the Task Manager
GO

SELECT * 
FROM [sys].[dm_exec_connections]
WHERE [session_id] IN (63, 66); --This is the list of session_id from the query above
GO

Here’s what the query returned in my case:

T-SQL Query output showing that SSIS connections open Shared Memory connections by default. — The SSIS connections open Shared Memory connections by default

As can be seen, the SSIS used Shared Memory connections by default when debugging the package from Visual Studio.

Independent execution of the SSIS package

To confirm that the Shared Memory connections were not caused by the SSIS Debug Host process, I simply executed the same package by double-clicking on it (which will invoke DTExec.exe).

Getting the host PID for the DTExecUI.exe process — Getting the Host PID for the DTExecUI.exe process

Using the same set of queries from above, here’s the output:

As can be seen, the SSIS connections open Shared Memory connections by default.

References

SSMS uses Shared Memory connections by default [Blog Link]
How to download the AdventureWorks2019 sample database [MSDN Link]

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?

1 Reply

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.

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:

In SSMS, when connecting to a SQL Server, click on “Options”
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.

I hope you will find this post helpful.

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

References:

DMV: sys.dm_exec_connections [MSDN Link]
SQL Server Configuration Manager [MSDN Link]