Monday, 14 April 2014

By far my most used script...

The one for an ad hoc backup of a database where I'm using Ola Hallengren's excellent solution, and I've ensured that I'm using the default backup location.


SET @dbname = 'dbname'

DECLARE @servername VARCHAR(50)

SET @servername = @@SERVERNAME
SET @servername = Replace(@servername, '\', '$')
SET @SERVERNAME = LEFT(@servername, 30)

/*Specify backup location*/
DECLARE @filefolder VARCHAR(50)

--SET @filefolder = 'W:\SQL_Backups_FULL\'
/* Or, to use default location*/
EXEC master.dbo.xp_instance_regread
  @filefolder output;

DECLARE @filelocation VARCHAR(150)

SET @filelocation = @filefolder + '\' + @servername + '\' + @dbname
                    + '\FULL\' + @dbname
                    + Replace(CONVERT(VARCHAR(20), Getdate(), 101), '/', '')
                    + Replace(CONVERT(VARCHAR(5), Getdate(), 108), ':', '')
                    + '.bak'

DECLARE @backupname VARCHAR(50)

SET @backupname = @dbname + '-Full Database Backup'

BACKUP DATABASE @dbname TO DISK = @filelocation  


Thursday, 13 March 2014

SQL Integration Services Access

Once upon a time, a long time ago, in SQL 2008, all users had access to connect to Integration Services in Management Studio. Naturally, SQL 2012 does it differently, and you have to explicitly grant access via Component Services
  1. Run Dcomcnfg.exe. This lets you edit the registry.
  2. In the Component Services dialog, expand
    Component Services --> Computers --> My Computer --> DCOM Config .
  3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
  4. On the Security tab, click Edit in the "Launch and Activation Permissions" area.

  5. Add users and assign appropriate permissions, and then click OK. If you don't like your users remoting onto the server, chose 'remote launch' and 'remote activation'

  6. Repeat steps 4 - 5 for the "Access Permissions" area. Again, grant remote access if you don't allow users to log directly onto the server.

  7. Restart the Integration Services Service in SQL Configuration. Restart Management Studio if you had it open, and were trying to connect from that.

Friday, 21 February 2014

SQL Agent Jobs in SQL2012

In the last week or so, we've been tightening up the security on our SQL boxes, and removing as many logins with sysadmin access.  This has included various of the service accounts.

On pretty much every single SQL 2012 box, this has had an unintended consequence: the SQL Agent Jobs have suddenly stopped running. Moreover, if you start a SQL Agent job, it looks like it is running, but, then, hit close - and actually it's done nothing at all. Until you look in the SQL Agent Error Log.

A selection of errors:

Date        21/02/2014 08:14:38
Log        SQL Server Agent (Current - 21/02/2014 09:45:00)

[298] SQLServer Error: 229, The SELECT permission was denied on the object 'sysjobschedules', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (SaveAllSchedules)

Date        21/02/2014 09:45:31
Log        SQL Server Agent (Current - 21/02/2014 09:45:00)

[298] SQLServer Error: 229, The SELECT permission was denied on the object 'sysjobservers', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)

Date        21/02/2014 09:45:31
Log        SQL Server Agent (Current - 21/02/2014 09:45:00)

[298] SQLServer Error: 229, The UPDATE permission was denied on the object 'sysjobservers', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)

Date        21/02/2014 09:45:31
Log        SQL Server Agent (Current - 21/02/2014 09:45:00)

[298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_log_jobhistory', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnExecuteCachableOp)

Note: just giving the SQL Agent account db_owner permissions on msdb didn't work. I just got different errors:
Date        20/02/2014 09:17:24
Log        SQL Server Agent (Current - 20/02/2014 09:17:00)

[298] SQLServer Error: 14262, The specified @job_id ('CF59EAF1-FAEC-4E5B-BE53-D07FE2CD9F60') does not exist. [SQLSTATE 42000]

Date        20/02/2014 09:17:24
Log        SQL Server Agent (Current - 20/02/2014 09:17:00)

[000] Unable to retrieve steps for job SQL Backup full backup: master and 5 others (created 05/07/2013 13:06:40)

(Yes, we use Red Gate SQL Backup here. And jolly good it is too).

Giving it additional SQL Agent Operator rights, however, did work...and was rather less sledgehammery in its approach than giving it sysadmin rights. If using Red Gate SQL Backup, you will need to ensure the account can execute the stored procedures in master. If using Ola Hallengren's Backup Solution (we use that too), then the only answer is, in fact, to grant sysadmin access - otherwise the SQL Agent Account cannot create the relevant folders. If you have regular checkdb jobs running, then you need to make sure it can execute DBCC CHECKDB on all databases.

Monday, 10 February 2014

How to install SQL Configuration Manager

When moving Sharepoint Foundation 2010 databases, the official advice is to set up a SQL Alias so that the Web Application Servers point at the new SQL server while using the old SQL Server name.

In order to do this, you need to have installed SQL Server Configuration Manager on the Web and Application Servers.

It's very easy to install SQL Server Configuration Manager without installing all the rest of SQL Server: just install the 'Client Connectivity Tools'. Ensure you install the version of the tools which matches the version of SQL Server installed on your database server.
  • Choose Install -> New installation or add features to an existing installation.
  • Choose all defaults until you get to the Feature Selection tab
  • Select Shared Features -> Client Tools Connectivity
  • Choose all remaining defaults and confirm installation
And that's it. No restart required. I did find that CPU on the server shot up to 100% for much of the install in our QA environment, so I don't recommend doing this in anger during office hours. Leave it to out of hours if you can.

Monday, 30 December 2013

TSQL - Who is a sysadmin on this server anyhow?

SELECT AS [Name] ,
        r.type_desc ,
        r.is_disabled ,
        r.create_date ,
        r.modify_date ,
FROM    sys.server_principals r
        INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
        INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE   r.type = 'R'
        AND = N'sysadmin'

Which is a heck of a lot easier than faffing about clicking things...

Tuesday, 10 December 2013

Oh what a horrible error!

RAISERROR('Error performing SQLBackup backup.', 16, 1)SQL Backup v7.3.2.12                                                                                                                                                                                                                                             ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ERRSQB: 5240 (SQB service did not acknowledge receipt of data. (WAIT_TIMEOUT)) ()

And the log backups completely and utterly failed. Trawling round the internet gave me one post in a Red Gate Forum, and that looked scary. Removing mutex objects? Restarting the server (not exactly an option)?

However, simply restarting SQL Backup itself solved the problem. As to why it happened? I'm not sure. The cluster hadn't failed over, or received any patches. It might be co-incidence that I'd just applied the licence key after having run out of evaluation period. It might not.

We now have log backups again. Phew.

Wednesday, 6 November 2013

So how do I enable SQLCMD Mode?

Well, in SSMS.exe, you click on 'Query' then 'SQL CMD Mode'. Simples!

But wait! There's a catch if your script has variables, or os prompts:

Because you are not starting SQLCMD from the command line, there are some limitations when running Query Editor in SQLCMD Mode. You cannot pass in command-line parameters such as variables, and, because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

What then?

Open up the Command Prompt, and load your script in thusly:

sqlcmd -S servername -U username -P password -i C:\Folder\Script.txt

And then it'll all work nicely, assuming that the script itself is sensible. For more parameters for the SQLCMD utility, see here.