Sql-server – Disable TCP Connections in SQL Script

sql serversql-server-agentt-sql

We have a SQL Server Agent job for loading dumps in case we need to restore from our daily backups. The first step in this job is supposed to kill all connections to the database. However, we frequently encounter a problem where somebody on the network has left an application running which will try to reconnect to the database if disconnected, and so after this first step has completed, this application will reconnect. Therefore, when the restore step comes it fails because it was unable to gain exclusive access to the database.

Usually I would use ALTER DATABASE [db] SET SINGLE_USER WITH ROLL BACK IMMEDIATE to ensure exclusive access, but unfortunately in this case the application reconnects sooner than the restore begins, and so the restore script is unable to access the database at all.

In order to get around this, I thought that it might be possible to disable all TCP connections during the restore so that only the locally running restore script could access the database. So, my question is is there any way in T-SQL to disable/block TCP connections? Or perhaps is there a way to configure the SQL Server Agent to do this when it starts the job?

Best Answer

If you disable TCP connections to SQL Server, SQL Server Agent might also be disconnected. However, that could be mitigated, and is by default, by enabling the Shared Memory Network Protocol for the server.

Having said that, disabling TCP connectivity is a bit like using a sledgehammer to kill a fly.

Normally, altering the database with SINGLE_USER WITH ROLLBACK IMMEDIATE will suffice; however occasionally you run into an application that is especially aggressive at reconnecting, and will reconnect to the database once it is in single user mode, but prior to the RESTORE DATABASE commencing.

One work-around I've used in the past for this is to put the database into RESTRICTED_USER mode, as in:

USE master;
ALTER DATABASE [xyz] SET PRIVILEGED_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [zyx] FROM ...

I've seen this not work when the app connects as sa or some other sysadmin user. In that case, I've done the following rather more aggressive action:

USE master;
ALTER DATABASE [xyz] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [xyz] SET ONLINE;
DROP DATABASE [xyz];
RESTORE DATATABSE [xyz] FROM ...

Having the above code in separate SQL Server Agent Job steps is problematic since each statement is executed in a different batch, which takes time to process. Keep that code in a single job step to further reduce the ability for a connection to succeed in between statements.

Related Question