SQL DBA Blog

Category: SQL Server

SQL Server Blog Archives, by Peter Whyte (Database Administrator).
Includes: Database Admin, Management Studio (SSMS), Windows Admin, PowerShell, WSL, MSSQL Backups, Restores & more…

Creating a Linked Server with a Postgres Database

In my previous post, I created a Link Server to another SQL Server instance This time, it’s a link to a Postgres database!

This guide can be followed from start to finish using the links below.

# Install SQL Server (separate post).
# Install PostgreSQL (separate post).
# Test Connectivity to Remote Server (previous Linked Server post).
# Create Linked Server Login on Postgres.
# Install the Postgres ODBC Driver.
# Configure ODBC Data Source Settings.
# Create Linked Server to a Postgres Database.

Create Linked Server Login on Postgres

As always, this kind of task can be done using a management tool like pgAdmin 4 or via command (psql in this case).

Making this one a Superuser for quickness.

And for the post, here’s the above being created using psql.

Install Postgres ODBC Driver

The ODBC driver must be installed on both ends – This is a local set-up though, so I only need to install this once. You can use Stack Builder to install it, or download the package on its own (link here) which I’m doing below.

Click famous Next button.

Accept License stuff.

Specify directory & we don’t need the documentation.

And then install!

Configure ODBC Data Source Settings

We need to point the ODBC driver on the SQL Server to the Postgres database.

Run ODBC Data Sources as Administrator.

Click into the System DSN tab and click Add.

Select the PostgreSQL Unicode driver.

Enter all details required as shown. I created a ‘sales’ database for this test which I’m connecting to.

Always worth it to test.

Create Linked Server to a Postgres Database

(Within SQL Server Management Studio’s Object Explorer)

Expand Server Objects, right-click Linked Servers and select New Linked Server.

The Linked server name at the top of the next window is what you’ll need to include in queries, so choose that wisely.

Then choose a provider for ODBC, enter any semi-relevant Product name, and then enter the Data Source name which you entered within the ODBC driver settings.

Enter the Postgres login details as created above within this post.

I’m ignoring the Server Options tab for now and rolling with the defaults.

When ready, take a breath, then hit OK!

Nothing will display if it’s successful, so have a go at testing the connection.

We’re now linked with the Sales database that’s in Postgres!

March 10, 2019
Create a Linked Server to SQL Server

Linked Servers in SQL Server allow you to query external databases, such as remote SQL Server instances, Oracle, ODBC or MS Access databases.

This blog post contains a demo on how to create a Linked Server to another SQL Server, with links to Microsoft Documentation throughout.

I also have more Linked Server demos if of interest:
-> Create a Linked Server with a MySQL Database
-> Create a Linked Server with a Postgres Database

The following steps should help walk you through creating a Linked Server to another MS SQL Server:
# Test Connectivity to Remote Server
# Create a SQL Login for Linked Server
# Create a Linked Server to SQL Server

Test Connectivity to Remote Server

An important step before we begin any SQL Server stuff, we should confirm line-of-sight to the remote server. On this local setup, I have nothing to worry about, but if configuring this in the real world, you may have to arrange network rules to be added.

The network information we’ll need is the server address (IP Address or FQDN) and port number. The SQL Server default port is 1433, so we just need the server address to test the connection using PowerShell’s Test-NetConnection cmdlet.

The SQL Server default port is 1433, so we just need the server address to test the connection using PowerShell’s Test-NetConnection cmdlet.

Both the Ping and TCPTest failed as expected.

Use this script if running PowerShell on an OS older than Windows 8/Windows Server 2012.

Other note-worthy default database server ports include:
# PostgreSQL (5432)
# MySQL (3306)
# Oracle (1521)
# Sybase (5000)
# DB2 (50000)

Create a SQL Login for the Linked Server

We need an account on the server we’re linking to. I’m using my SQL Server 2019 (PETE-PC\SQL2019) as my master server, creating the new login on the PETE-PC instance as shown on the image below.

I’m selecting SQL Server authentication as I’m not on a domain and unticking the password reset on the next login.

I’ll test a read-only case by selecting db_datareader on the AdventureWorks database.

Once we click OK the account is ready for use.

Create a Linked Server to SQL Server

Open Server Objects, right-click Linked Servers and select New Linked Server.

Select SQL Server as the server type and enter the ‘remote’ server name.

Into the Security tab next, enter the remote login details (as created above).

We can hit OK, or script it out before running. This is a condensed version.

TSQL can be found here.

The 3 system stored procedures used are:

# sp_addlinkedserver
# sp_serveroption
# sp_addlinkedsrvlogin

We can verify the link by querying the new linked database.

And finally, verify permissions.

March 4, 2019
Database Growth Events in SQL Server

When a database file gets full and it needs more space, a growth event will happen. The size of said database growth can be set within the database options, or can be done using the ALTER DATABASE statement as shown below.

When considering growth event sizes for log (.ldf) files, you may want to have a look at this post by Paul Randall to avoid VLF fragmentation.

We don’t need to worry about such things as much with the Data (.mdf) files though. There’s no perfect number to set it to, just try not make it too small or big…. no… wait. Let me explain.

If I say keep it in proportion to your database size, you might think that percent growth would be the way to go? Waiting on gigabyte sized growth events may cause performance problems when managing larger databases (Instant File Initialisation might mitigate that), or it could potentially lead to unexpected disk space alerts, particularly if configured on all TempDB files.

For the reasons above, some folks avoid using percent growth completely, setting growth to around the 250MB mark if the database isn’t tiny. If it’s a business-critical database, you could measure organic growth with the use of a monitoring tool, then grow the database out during maintenance periods. The screenshot below is how we set a new database size.

You could then track free space within each database on the server, with the help of your chosen monitoring solution. If you don’t have one of those, a simple PowerBI Report does the trick!

A few ideas anyway. That’s it for now!

February 23, 2019
Setting Maximum Database File Sizes in SQL Server

Setting maximum database file sizes in SQL Server is something you’d ideally not have to set, but it’s a good last resort option available if you need it.

The reason I say this is because, you never want a production database to stop updating/inserting data due to a size cap on the database. However, if something is causing unpredictable disk space issues, you should really try stop it from happening again at source… Sometimes that’s not so easy though.

For example, if random user queries are consuming massive amounts of TempDB space and throwing disk space monitoring alerts, and let’s say, you’re actively killing their queries to avoid the disk becoming full. That SQL Server instance could have many other live databases, and we could also say there’s more than 10 users running ad hoc queries throughout the day.

Those users causing the unexpected will need to understand why this is happening before you can sleep easy. How long will it take to get everyone up to speed? We’ll need to show them what happens when they try throw a 100GB table into a temporary table, or how to measure the size of table they’re working with!

If they’re remote users, things could be all the more difficult to manage. What information will you try send them within that “~~Sorry~~ I killed your SPID” email?

There’s many variables, so it’s definitely one of those “it depends” scenarios in the land of SQL Server. My point here is, you might have a legit reason to set a cap on a database, but it should be more considered a short-term measure in most cases.

Below, is a simple guide on how to set a size cap on a database in SQL Server.

Setting Max Database Size

Right click database & select Properties.

Click into the Files tab on the left-hand menu and click the button as shown below.

Don’t go setting TempDB files to 100MB like I’m doing above. This is test stuff.

Alternatively, we can use the ALTER DATABASE statement.

As you can see above, you can enter these values in GB/MB/KB.

February 21, 2019
Moving TempDB Files in SQL Server

It’s a solid best practise to have TempDB files stored in separate drive, away from your C: drive.

This is because TempDB can grow very large, very quickly (within minutes); depending on workloads of course, and this can happen on both the TempDB data (.ndf) and log (.ldf) files.

This post is a quick tutorial on how to move all TempDB files from one location to another in SQL Server. A planned maintenance window is required on production servers for this one.

Moving TempDB Files

1. Use sp_helpfile to view logical TempDB names.

2. Before running the SQL below, amend the name and full file path within the single quotes.

(You’ll need an ALTER statement per file – if copy/paste isn’t fast enough, here’s a post by Brent Ozar to script out the statement.)

3. As mentioned in the output message, the path will be used the next time the database is started. So we need to restart the SQL Server service!

4. Once that’s done, I like to visually check the new files.

5. Run sp_helpfile again.

6. Have a look at the location of files within the Shrink File window.

7. Send a test Database Mail (people do this).

8. And finally, remember to remove the old TempDB files, especially if you’re moving to another folder on the same disk!

(You’re unable to copy or delete TempDB files if their being used by an online SQL Server service.)

February 17, 2019
The SSMS Import Flat File Feature

The Import Flat File feature within SQL Server Management Studio (SSMS) was introduced in December 2017 (14.0.17213.0). This is a streamlined version of the Import Data feature and it minimises the user intervention during a file import.

Unlike the Import data feature, you don’t have the option to save the import as a .DTS package. This makes it a good tool for quick imports of individual files to SQL, but not so much if it’s a repetitive task you want to automate.

Below is a guide on using the Import Flat File feature, with a (realistic) twist.

How to Import Flat File SQL Server

The steps below are an example of a CSV file import to a SQL Server 2019 database.

1. Create a database.

2. Right-click your destination database, select Tasks and Import Flat File as shown below.

3. Click Next to continue.

4. Browse to the file, enter the desired table name and amend the schema name accordingly. Have a look here for posts on exporting data if useful.

5. Preview the data on the next window and click Next when happy.

6. Review Column Names/Primary Keys/NULLs, and take as much time as you need when selecting Data types!

7. Hit Next & Finish when ready.

8. Oh wait. I wasn’t expecting this…

This is something that has to be included as it happens to us all. You won’t know if there are exceptions within the data, so data type conversion errors are a frequent thing in the business of importing & exporting data. We have to use any useful information we can find within the error.

9. If you don’t know the column name, search the .csv file for ‘101.8936’ as quoted within the error message.

10. Going back within the wizard to Modify Columns, there’s only one Decimal Data Type, so it would have been easy to see which column needs changed.

Increasing Decimal Max Precision.

11. Now finish the wizard and hope for the best!

12. Let’s have a look at the new table.

January 24, 2019
Attaching SQL Databases Without Log Files

If you’ve lost a database log file and you’re in critical need of the data, well… that would never happen ( ͡° ͜ʖ ͡°)

Nevertheless, this is might be a handy random fact to know, that you can attach a data (.mdf) file without the associated log (.ldf) file in SQL Server.

This post will come in 3 parts.
1. Create scenario (get a test database, stop SQL, copy database, drop database).
2. Restoring without a log file.
3. Errors when attaching (if you get one).

Create Scenario

1. Create a test database.

2. Close down SSMS, or disconnect the query session.

3. Stop the SQL Server Service.

4. Locate the Data and Log files.

5. Copy funkdata into a different location.

6. Start the SQL Service.

7. Drop the database.

8. Yep, the database files are gone.

9. Copy the data file (funkydata) back into this directory.

Now, we’re ready to try attach this back into SQL.

Attach Database without the Log File

There’s 2 ways of going about it.

1. Using the FOR ATTACH

2. Using sp_attach_single_file_db (not recommended).

Click here for the code used above.

Errors when Attaching

While testing the above, I first receive an error upon attaching the data file;

File activation failure. The physical file name “c:\sql\funklog.ldf” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 3
Could not open new database ‘funky_db’. CREATE DATABASE is aborted.

This was due to having a SSMS query window open in the background when stopping the services (Step 2 during prep).

It’s possible to get it attached I’m sure, but is it really worth it?…

Nah.

January 22, 2019
Backup & Restore (WITH FILE)

In this post I’m going to show what the WITH FILE = X option does when backing-up or restoring a SQL Server database.

microsoft_documentation

The first thing we need to know is that a backup file can contain multiple databases, and even multiples of the same database. If you run a quick backup command on a small database, then hit F5 again without amending the file path, you’ll end up with 2 database backups within the same media.

When restoring from a backup we can specify which version of the database we want by using the WITH FILE option, with the help of the RESTORE HEADERONLY command.

This post is a run-through of how the above works!

Backup & Restore With File

1. To get started, create a database & insert some test data.

2. Backup the database.

The backup file;

3. Insert some more data into the test table and then backup the file to the very same location.

The backup file;

4. Drop the database, readying for a restore.

5. Using RESTORE HEADERONLY, we can have a look into the backup media to find out more information. The Position column is what we need from this.

6. Restore the first backup performed above WITH FILE = 1.

7. Check the data.

8. Restore the database in position number 2, with REPLACE to avoid the need of a drop.

9. Verify again which backup it is.

10. Finally, restore again without WITH FILE.

We can see that the first backup taken has been restored as default.

If you are backing up multiple different databases into the same media, the above won’t work. You’ll have to specify the position. You’d also probably want to give it a backup name using WITH NAME = ‘backupName’.

Hope this is useful stuff!

January 20, 2019
Applying Data Retention to a SQL Table

There are many reasons why you’d need to delete old data from a table within a database, some of which include;

# GDPR (adhering to data retention policies)
# Ever increasing tables becoming tougher to manage (e.g. a replication refresh is taking 2 days)
# SQL Server Express limitations (10GB max database size)
# The table is storing application errors & logs and you’ve been told it’s there to stay

Maybe it’s a one-off job for you, and the deletions can be done within a days work. Or, you might find yourself dealing with very large tables, deleting daily during a small off-peak window over the course of however long… this is a task that can come along with many variations, and if asked the question, how would you do it yourself? Well, “it depends!”.

I’ve run through a simple version of the task below, which also summarises a bunch of my other recent tech posts too. All links below are internal.

Deleting Data in SQL Server

1. Get a hold of a test database and restore it on your SQL Server.

2. Look for one of the larger tables and pick your fancy.

3. Get row counts by date. For this example, I’m storing the row count information into a temp table. This deletion won’t take long so my session will still be active.

4. Perform delete in batches.

5. Get new row counts and compare against previous results.

Plus a few additional checks to verify.

Looking good – All data on orders beyond 7 years (1 January 2012 00:00) has been deleted.

When the initial task is done and all tables are meeting retention periods, we’d then need to consider things like scheduled SQL Agent jobs to maintain the tables every month.

January 13, 2019
Using Custom Connection Colours in SSMS

SQL Server Management Studio (SSMS) is the tool that most SQL Server user queries are run from. It has an option that allows you to change the colour of your query banner, which is useful if you want visually mark a server that you work with every day.

My choice of colour below stands out quite well:

Amending this is particularly useful when you are running queries in test, development & production environments from the same host. You want to have a visual warning/display to indicate clearly which MSSQL host you are running your next query on.

This is a feature that I find myself using from time to time, and I see others using it frequently.

Changing Connection Colours in SSMS

To change a connection colour for a particular SQL Server Instance, follow below:

1. Click to connect to a SQL Server instance within SSMS.

2. Click Options.

3. Within the Connection Properties tab, check the box to use custom colours and select your preference.

4. Open a new query window to view the change.

October 22, 2018