SQL Server Installation Problem

Another day and another install of SQL Server Developer Edition (2005 in this case). Installation complete & there's no way to connect to reporting services or analysis services. Strange, no icons, nothing in the object explorer connection.

Turns out SQL Server Express edition is installed and it is a higher version that I've just tried to install so I am missing the options.

The resolution was to install using SKUUPGRADE = 1 as a startup parameter and then when that install completed I had to refer to an earlier post because management studio was still missing.

Got there in the end.

Sql Server Reporting Services 2005 Issue

Again SSRS 2005 has stopped working.

This time it follows install of Sql Server 2008 developer edition on my machine so I've been trying to figure out how to get both working on the same box - it is possible according to the documentation but there is no mention of how.

Unfortunately I am getting the following message when attempting to connection to Reporting Services via SQL Management Studio "unable to connect to the remote server. No connection could be made because the target machine actively refused it."

Now that's not very nice is it, I ask nicely & it does nothing.

Anyway after a few weeks of digging I've finally found my problem is not related to the Dev 2008 install - it is in fact our network wide security settings, they have been changed!

So the solution was to go into IIS Admin console, goto the properties of my default web site (which hosts my SSRS pages) and goto the Home Directory tab and then change the Execute Permissions to Script and Executables.

Job done.

SQL Server 2005 metadata error when installing Reporting Services

Shiny new install of Reporting Services all done. Unfortuntately attempts to login to RS via SQL Management Studio give an html string error mentioning metadata problems with IIS. Attempts to access http://localhost/Reports result in an IIS error about changing permissions.

This is install problems with .Net Framework.

Just goto the v2.0.50727 folder (c:\windows\Microsoft.net\framework\v2.0.50727) and run
aspnet_regiis -ga aspnet
where "aspnet" is the name of the ASPNet user found in the list of users on the local machine.

SQL Server Install Error

I've had the following error a couple of times when installing SQL Server :


sql server failed to obtain system account information for the aspnet account... 


The solution is in a dos prompt to goto c:\windows\framework\v2.0.xxxx (you'll have to check what xxxx is on your machine) and then type


aspnet_regiis -i


Has worked everytime so far.

SQL Server Job Rerun Utility

Ever had a server with numerous scheduled tasks that suffers numerous failures? It happens, one day your SQL Server is working then the next several jobs fail, you diligently rerun them all which takes you hours. Perhaps you even find the same problem again, maybe a drive is on the way out causing an intermittent problem, maybe some evil techie turned off that tape drive for purposes known only to him. Maybe your jobs are running but never finishing so you have to cancel some to free up resources.

You've got a problem. You need to fix it.

But you also need a way to quickly and easily rerun all of your outstanding jobs because that can take hours. Microsoft provide

[msdb].[dbo].[sp_start_job] 'job name'

to help out but that's only good for one job.

I've written the following code to rerun any jobs that have failed or been cancelled which fit the given name using the standard like syntax, waiting for a specified delay between each.

Syntax is

usp_ReRunJobs 'job name', 'delay'

where 'job name' is literally the textual name of the job ie if all of your full backups are called 'Full Backup Of xxxx' then 'Full Backup %', alternatively, just '%' to do everything, whilst 'delay' is in the format 'hh:mm:ss' ie '00:00:30' means a 30 second delay between each job.

You might have even tried the retries feature of scheduled jobs - that works wonders except where a job is genuinely broken and the retry impacts on the next job and so on, so usp_ReRunJobs could be scheduled to run daily after the usual job complete as a backstop.

Here's the code  :

IF OBJECT_ID ( 'usp_ReRunJobs', 'P' ) IS NOT NULL

    DROP PROCEDURE usp_ReRunJobs

go

create procedure usp_ReRunJobs (

      @Name       varchar(255)      = 'Full Backup%',

      @Delay            varchar(8)        = '00:00:60'

)

as

begin

      set nocount on

      IF ISDATE('2000-01-01 ' + @Delay + '.000') = 0

    BEGIN

        SELECT 'Invalid time ' + @Delay + ',hh:mm:ss, submitted.';

    END else begin

            create table #xp_results(

            job_id uniqueidentifier not null,

            last_run_date int not null,

            last_run_time int not null,

            next_run_date int not null,

            next_run_time int not null,

            next_run_schedule_id int not null,

            requested_to_run int not null, -- bool

            request_source int not null,

            request_source_id sysname collate database_default null,

            running int not null, -- bool

            current_step int not null,

            current_retry_attempt int not null,

            job_state int not null )

     

            declare @JName                varchar(255)

            declare cJobs cursor fast_forward for

                  select      j.name

                  from msdb..sysjobs j

                  join  msdb..sysjobhistory j2 on j.job_id = j2.job_id

                  where j.name like @Name

                  and         j.enabled = 1

                  and         j2.instance_id = (

                                                            SELECT            max(h.instance_id)

                                                            FROM        [msdb].[dbo].[sysjobhistory] h

                                                            where       j2.job_id = h.job_id

                              )

                  and         not exists (

                                                      select      x.job_id

                                                      from  #xp_results x

                                                      where x.job_id = j.job_id

                                                      and         x.running = 1

                              )

                  and         j2.run_status not in ('1','4')

                  order by j.name

            insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner= ''

            open cJobs

            fetch next from cJobs into @JName

            while @@fetch_status = 0

            begin

                  select 'Started '+@JName

                  exec msdb..sp_start_job @Job_Name = @JName

                  waitfor delay @Delay

                  fetch next from cJobs into @JName

            end

            close cJobs

            deallocate cJobs

            drop table #xp_results

      end

end

go

I'm not claiming it's the prettiest code or the most efficient but it gets the job done. The only thing I haven't managed to crack is getting the job start message appearing during the execution of the code so if you want to watch your jobs you'll have to resort to activity monitor, I have tried raiserror nowait but that didn't work either but that is a downside I can live with given on the second day of having this code in place it saved me about an hour of rerunning tasks by hand.

Remote access to SSIS or SSRS

I've recently encountered an issue with enabling users to remotely access SSRS and SSIS (SQL Server Reporting Services and SQL Server Integration Services).

My problem comes about when the users affected are not in the Local Admins group on the destination server - we want to lockdown rights to the box whilst still allowing SQL services to be accessible via SSMS (SQL Server Management Studio).

The users can still access SSRS remotely because they can go via the web interface but as anyone who uses it frequently will know it is so slow compared to using SSMS.

The fix to enable remote access to SSIS is as follows :
Goto Control Panel -> Administrative Tools on the destination server
1) Add the users (their login accounts, or even better an AD group) to the Distributed Com Users group unders Users & Groups. We found this step necessary but that might vary on different servers, the ordering of these steps doesn't really matter so if you fancy you could try it later. We have also added the users into the other SQL server groups there too (probably just "for good measure").

2) Goto Component services -> Computers -> My Computer, right click and go into Properties, Com Security and add the same users / group with the Remote Access privilege - you shouldn't need to add others because the everyone group should do that for you.

At this point Integration Services should now be accessible via Management Studio but Reporting Services is probably telling you it does not know the machine name you've entered (try it, go on). So to finish off for Report Services :

3) Open Computer Management, browse to Services and Applications -> WMI Control and right click on it then open Properties -> Security and finally add the requried users or group with the Remote Enable privilege. This is rather brute force, if you want more granular control let me know if you find which individual modules need amending.
Job done.

Moving tempdb

I'm doing it again, moving the SQL Server tempdb. For some reason people setup tempdb in places which just makes no sense whatsoever - like in the same location as other files. It's well documented that tempdb should be on its own drives as its performance is critical to that of the system as a whole.

What type of drive is arguable, generally I've used raid 10 although raid 1 is viable too.  There are arguments for raid 0 out there for the performance benefit, the downside is that if you lose 1 drive then your whole instance is in trouble so not really worth the risk.

You should have 1 tempdb file for each core (I've seen a suggestion of up to 8 files, after which .5 files per core applies but I've no evidence at that scale) and every file must be exactly the same size so the server can create 1 thread per file and use proportional fill.

Microsoft's own advice (this is from Microsoft staff presenting but doesn't make it into written documentation) is to fill the tempdb drive to 90% and have your tempdb at a fixed size ie figure out the max tempdb size you'll ever need, add a bit just in case & create your disk partition so that the size of the tempdb you calculated is 90% of the partition size then create the tempdb files to go in there. I would put a caveat on that, auto-grow is stupid - why would anyone with a live database want auto-grow to come along and trash their database performance while doing its thing & fragmenting your hard disk - plan properly! That said, I like the idea of planning in advance for file sizes but as long as disk space exists auto-grow I leave auto-grow turned on because it is better to take that performance hit rather than your database stop because it has no space. Naturally, you will be reporting regularly on database space used and free disk space to avoid issues won't you? While on "auto" things, there's no point in having auto create stats on and auto-shrink / auto-close are not appropriate for tempdb.

Big note, from SQL Server 2005, each time the instance restarts the tempdb is copied from MODEL so inherits some config options (eg ALLOW_SNAPSHOT_ISOLATION).

It is worth talking a little about the tempdb log file - yes it has one and is used. However, SQL Server 2005 has been optimised so that instead of the usual before & after log, some after logs are not saved so reducing the traffic and file size.

No real advice exists about the placement of the tempdb log file, I would say that if you are forced to keep your tempdb on the same drive as your data then at least place the log file with your other log files, otherwise the norm appears to be to keep the log file next to the tempdb datafiles.

Here is the Microsoft summary which is unfortunately woolly on some details (eg what type of drive to use) but specific on others http://technet.microsoft.com/en-us/library/cc966545.aspx

If you just want to move your tempdb, that's easy, just run the following query to identify what you already have in the way of tempdb files :

USE tempdb


GO

EXEC sp_helpfile

GO

and then run the following to move your files replacing tempname with the name of the file & c:\tempdb.mdf with your new path, similarly change the templog alter command. You will need to use the alter command for each file that you are moving

USE master

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempname, FILENAME = 'c:\tempdb.mdf')
GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\templog.ldf')

GO

Having done that you will need to restart the database and once successfully started and you have confirmed the changes are in place remember to delete the old tempdb files.

Granting privileges to stored procedures

I've just encountered a user who wanted to run a stored procedure or 3 against as SQL Server database on which he ws not the owner of the stored procedures. This was a bit of a surprise because although added to the database after creation he is in there as dbo. Never mind needed a solution sooner rather than later.

It's simple really in that Securables can be manually editted for that user, unfortunately there are a large number of procedures potentially required - in fact it is a 3rd party product so we won't have a full list of required procedures until we've run the process repeatedly weeding out each issue as it arises.

The answer :

select 'Grant Execute on ' + name + ' to "username"'
from sysobjects where xtype in ('P')

This works nicely by outputting a full list of statements that can be copied from the output and executed in the query window in SSMS. The important feature here is the username - this is the full username ie if you are using Windows accounts it will be DOMAIN\user enclosed in the shown speech marks to ensure spaces are handled properly.

Restoring SQL Server databases to a different machine

SQL Server is extremely flexible and allows databases to be backed up and restored very easily and independently of hardware requirements.

There is, however, one nagging problem when restoring a database to a different machine(say copying a development database to a live environment) and that is SQL Servers security system.

SQL Server has logins against the instance and then users against each database - so each database has security independent of all the other databases unless logins have been granted access to any server roles.

When the database restore process completes there might be users listed on the database who do not have logins on that instance or even if the user has a login the guids might not match.

So for SQL Server 2000 and above firstly identify what the problem is by running :

exec sp_change_users_login @Action='Report'

Then for each UserName identified you can either match existing logins to database user using :

exec sp_change_users_login 'Auto_Fix', @UserNamePattern ='username'

(replacing username with the UserName from the first query).

To save having to check which to do manually it does no harm to run that command for users that don't exist yet - just run the first command again to identify who is still not sorted and then run the following to create logins for each remaining user :

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Just make sure you are connected as an admin user and you are connected to the correct database before running the commands.

It's also worth pointing out that this isn't news - I was passed most of this info when I started the role and have modified as I've gone along and there are plenty of postings out there with the same /similar information.

Changing timings of SQL server agent jobs on mass

This is an example of how to change the timing of a series of jobs on the SQL Server agent - in this instance we needed to change all of our full backups to run every third day (well Monday then Thursday, Tuesday then Friday and Wednesday and Sunday leaving Saturday for other maintenance activities).

-- Routine to set the times of all jobs named "Full backup%" on a machine, splitting the jobs into 3 batches, Mon&thur, Tue & Fri and Wed & Sun
-- log backups should be occuring the rest of the time which leaves saturday for maintenance work
-- output is a series of statements which when executed to text output can be copied and executed in a new query window.
set nocount on
create table results (dsp varchar(500))
declare cJobs cursor fast_forward for
select schedule_uid from msdb..sysschedules where name like 'Full Backup%' order by name, schedule_uid

declare @UID varchar(128)
declare @count int
declare @MT int
declare @TF int
declare @WS int
declare @MTStart int
declare @TFStart int
declare @WSStart int

set @MT = 0
set @TF = 0
set @WS = 0
set @MTStart = 40000
set @TFStart = 40000
set @WSStart = 40000

set @count = 1
open cJobs
fetch next from cJobs into @UID
while @@fetch_status = 0
begin
if @count%3 = 0
begin
set @MT = @MT+1 -- Mon & Thur = freq_interval = 18
insert into results select 'update msdb..sysschedules set freq_interval = 18, active_start_time='+cast(@MTStart as varchar)+' where schedule_uid = '''+@UID+''''
set @MTStart = @MTStart + 200
if @MTStart = 46000 begin
set @MTStart = 50000
end else if @MTStart = 56000 begin
set @MTStart = 60000
end else if @MTStart = 66000 begin
set @MTStart = 70000
end else if @MTStart = 76000 begin
set @MTStart = 80000
end
end else if @count%2 = 0 begin
set @TF = @TF+1 -- Tue & Fri = 36
insert into results select 'update msdb..sysschedules set freq_interval = 36, active_start_time='+cast(@TFStart as varchar)+' where schedule_uid = '''+@UID+''''
set @TFStart = @TFStart + 200
if @TFStart = 46000 begin
set @TFStart = 50000
end else if @TFStart = 56000 begin
set @TFStart = 60000
end else if @TFStart = 66000 begin
set @TFStart = 70000
end else if @TFStart = 76000 begin
set @TFStart = 80000
end
end else begin
set @WS = @WS+1 -- Wed & Sun = 9
insert into results select 'update msdb..sysschedules set freq_interval = 9, active_start_time='+cast(@WSStart as varchar)+' where schedule_uid = '''+@UID+''''
set @WSStart = @WSStart + 200
if @WSStart = 46000 begin
set @WSStart = 50000
end else if @WSStart = 56000 begin
set @WSStart = 60000
end else if @WSStart = 66000 begin
set @WSStart = 70000
end else if @WSStart = 76000 begin
set @WSStart = 80000
end
end
fetch next from cJobs into @UID
set @count = @count +1
end
close cJobs
deallocate cJobs
print 'Mon Tue:'+cast(@MT as varchar) +', Tue Fri: '+cast(@TF as varchar)+'Wed Sun : '+ cast(@WS as varchar)
select * from results order by 1
drop table results

Update : we have discovered that the table sysjobschedules hold start times too but cycling the agent should bring everything back into step.

SQL Server 2005 BIDS Installation Problem

Here’s a new one – I’ve just installed SQL Server 2005 and selected everything including client tools and Business Intelligence and the install completes perfectly only to find that BIDS is not installed, it is missing, no trace of devenv.exe or related components on disk.

 

After several attempts via Google I finally found an answer that works here http://weblogs.asp.net/kdente/archive/2007/03/07/the-case-of-the-missing-business-intelligence-studio.aspx, it seems from MSDN that lots of people have the problem so I’ll repeat the answer :

 

Goto the installation CDs, find disk 2 and in \setup run firstly vs_Setup.exe – you’ll get absolutely no notification that anything is happening for what seems like an age and then it will be finished. Then run from the same location sqlrun_tools.msi and select the components you want – in my instance they were shown as missing by it seems that is not always the case so just go and reselect everything you want and run the install.

 

Job done.

 

Another MS feature to make my life painful.

 

Using Sql Server Management Studio Offline

First post, a tip I desperately needed today.

I have been using my laptop offline and occasionally find myself faced with a "cannot generate sspi context" error message.

Working offline I have found that I was unable to connect to my local instance (SQL Server 2005) using the server name so I was having to connect as localhost and for most local SQL / T-SQL purposes that was fine. However, any attempt to access DTS was met by the aforementioned problem.

The solution turned out to be simple - open SQL Server Configuration Manager (under the Microsoft SQL Server 2005 program group in Configuration Tools) and goto SQL Native Client Configuration then Aliases. Right click on your instance name and goto Properties. I found that the protocol was set to TCP which is not fully functioning without my work network so changing to Named Pipes means the whole communication is internal to the laptop.

Now I can logon to the expected instance name and work with no problems. 5 minute fix.

Click here for a discussion of the SSPI problem on SQL Server Central