Using Proxy Accounts in SQL Server Agent Jobs

When using SQL Server, in many cases you will might need to set up a SQL Server Agent job that will be accessing a resource within the domain.

 

For example, you want to include a step in a SQL Server Agent job that based on some logic, will be handling a Windows service on a server within the domain by using the Operating System (CmdExec) SQL Server subsystem.

 

In order for the job to be successfully executed, the specific job's execution context should be allowed access to the target resource in the domain.

 

If you are using a domain user as a service account for the SQL Server Agent in the specific instance, you can assign the necessary access rights to that user account.

 

However, there is also another way which I personally prefer; using a Proxy Account for executing the specific job step :)

In order to be able to do this you must perform the following actions within the instance of SQL Server:

1. Create a credential
2. Create a Proxy Account that uses the credential you created in the first step  

For creating a credential, in SSMS you navigate to: Security -- Credentials
You can then create the credential by providing an identity (i.e. a domain user) along with its password.

For creating a Proxy Account, in SSMS you navigate to: SQL Server Agent -- Proxies

You can then create a new Proxy Account by giving it a name and performing the following:
- Provide the credential you earlier created
- Enter a description (optional)
- Set the subsystems for which the Proxy Account will be active. These are:
------- ActiveX Script
------- Operating system (CmdExec)
------- Replication Distributor
------- Replication Merge
------- Replication Queue Reader
------- Replication Snapshot
------- Replication Transaction-Log Reader
------- SQL Server Analysis Services Command
------- SQL Server Analysis Services Query
------- SQL Server Integration Services Package

That's it!

You can now proceed and set up the SQL Server Agent job along with its steps, and in the step you want to use the Proxy Account you select it in the "Run as" drop down box.

Whenever the specific job step runs, it will be executed in the context of the provided Proxy Account.

* Note that in order to be able to use a Proxy Account in a specific job step, the Proxy Account needs to be activated for the specific subsystem (i.e. Operating system - CmdExec).

I hope you found this post useful!
Read more on this article...

Updating SQL Server Tables Without Causing Blocking

Even though the SQL Server Database Engine automatically sets the best possible locking hints on the underlying database objects of the various T-SQL operations that executes, there are cases
where we need to manually control locking as a part of the business logic in our T-SQL script.

A popular locking hint is the NOLOCK as it is being used many times in environments with high concurrency. By using the NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. Of course, this means that the query may see inconsistent data (dirty reads), that is data not yet committed, etc. The NOLOCK hint can only be used in SELECT statements.

Now imagine the following scenario: You need to design a special UPDATE statement that will be updating an unknown number of records in a table which is being concurrently accessed by several other T-SQL statements (mostly UPDATE statements). The above statement will be updating the table with non-critical information meaning that if it skips some records the first time, it can update them the second time and so on.

If even one of the other UPDATE statements has locked a row that needs to be modified by your UPDATE statement, this will cause the latter to wait (blocking). However, in the case where waiting is not a very "desired" option what should you do? That leads us to the following question: What table hint can be used?

Answer: The READPAST locking hint :)

The READPAST locking hint when used, instructs the SQL Server Database Engine to skip row-level locks.
This means that the UPDATE statement using READPAST will only update the table rows that are not locked by another operation. In the opposite case, the Database Engine would block the UPDATE statement's execution until the rest of the target rows' locks are released. A typical UPDATE statement with the READPAST locking hint would look like this:

UPDATE [TABLE_NAME] WITH (READPAST)
SET ...
WHERE ...

Even if the above locking hint can become quite handy, as well as the rest of the locking hints, you always need to have in mind that you should use them very carefully as you might cause locking issues in the database. SQL Server Query Optimizer typically selects the best execution plan for a query, so it is not recommended for inexperienced developers and administrators to make use of the locking hints.

As a last note, the READPAST hint can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels.
Read more on this article...

Backing up a Database in a Network Folder

Hi! I hope you are all doing well and having a great summer time!

It's been a while since the last time I have posted an article but hey, besides the heavy workload, it's summer time and the beaches in Cyprus are really great! :)

This post discusses about a quite simple task in SQL Server: backing up a database in a network folder / remote server.

To cut the long story short, imagine the following scenario:

You have a SQL Server Instance located on a server with only one local drive (!) and you urgently need to backup a database somewhere! Well, the first thing that comes on my mind, is to backup the database on a remote location (yep, you do not have physical access to the server and cannot mount a USB flash drive :)

In order to do this you first need to mount the remote location (network folder) as a backup device.

To this end, let's say you have the network folder \\serverName\backupFolder and you want to mount it as a backup device. For doing that, you have to run the following stored procedure:

USE master

GO

EXEC sp_addumpdevice 'disk','NetWorkDeviceName','\\serverName\backupFolder\BackupFileName.bak'

GO

Then from SSMS you can select to backup the database on the mounted backup device and that's it!

However, there is one consideration; the service account which runs the SQL Server Instance needs to have read/write permissions on the network folder (shared permissions).

In the opposite case you will receive the following error message:

I hope you found this post useful!

Until next time!
Read more on this article...