DavidWimbush

Have you really fixed that problem?

2010-03-18T07:46:00Z

The day before yesterday I saw our main live server's CPU go up to constantly 100% with just the occasional short drop to a lower level. The exact opposite of what you'd want to see.

We're log shipping every 15 minutes and part of that involves calling WinRAR to compress the log backups before copying them over. (We're on SQL2005 so there's no native compression and we have bandwidth issues with the connection to our remote site.) I realised the log shipping jobs were taking about 10 minutes and that most of that was spent shipping a 'live' reporting database that is completely rebuilt every 20 minutes. (I'm just trying to keep this stuff alive until I can improve it.) We can rebuild this database in minutes if we have to fail over so I disabled log shipping of that database. The log shipping went down to less than 2 minutes and I went off to the SQL Social evening in London feeling quite pleased with myself. It was a great evening - fun, educational and thought-provoking. Thanks to Simon Sabin & co for laying that on, and thanks too to the guests for making the effort when they must have been pretty worn out after doing DevWeek all day first.

The next morning I came down to earth with a bump: CPU still at 100%. WTF? I looked in the activity monitor but it was confusing because some sessions have been running for a long time so it's not a good guide what's using the CPU now. I tried the standard reports showing queries by CPU (average and total) but they only show the top 10 so they just show my big overnight archiving and data cleaning stuff. But the Profiler showed it was four queries used by our new website usage tracking system. Four simple indexes later the CPU was back where it should be: about 20% with occasional short spikes.

So the moral is: even when you're convinced you've found the cause and fixed the problem, you HAVE to go back and confirm that the problem has gone.

And, yes, I have checked the CPU again today and it's still looking sweet.

Finding rows that intersect with a date period

2010-03-09T19:40:00Z

This one is mainly a personal reminder but I hope it helps somebody else too. Let's say you have a table that covers something like currency exchange rates with columns for the start and end dates of the period each rate was applicable. Now you need to list the rates that applied during the year 2009. For some reason this always fazes me and I have to work it out with a diagram. So here's the recipe so I never have to do that again:

select  * 
from    ExchangeRate
where   StartDate <= '31-DEC-2009'
        and EndDate >= '01-JAN-2009'
 

That is all!

Log shipping and shrinking transaction logs

2010-03-04T11:51:00Z

I just solved a problem that had me worried for a bit. I'm log shipping from three primary servers to a single secondary server, and the transaction log disk on the secondary server was getting very full. I established that several primary databases had unused space that resulted from big, one-off updates so I could shrink their logs. But would this action be log shipped and applied to the secondary database too? I thought probably not. And, more importantly, would it break log shipping? My secondary databases are in a Standby / Read Only state so I didn't think I could shrink their logs. I RTFMd, Googled, and asked on a Q&A site (not the evil one) but was none the wiser.

So I was facing a monumental round of shrink, full backup, full secondary restore and re-start log shipping (which would leave us without a disaster recovery facility for the duration). Then I thought it might be worthwhile to take a non-essential database and just make absolutely sure a log shrink on the primary wouldn't ship over and occur on the secondary as well. So I did a DBCC SHRINKFILE and kept an eye on the secondary. Bingo! Log shipping didn't blink and the log on the secondary shrank too.

I just love it when something turns out even better than I dared to hope. (And I guess this highlights something I need to learn about what activities are logged.)

Cursor bad, set-based good

2010-02-16T07:40:00Z

I just had another reminder, as if one was needed, that a cursor should be your last resort to get something done. I was looking to shave some time off my data warehouse build job and noticed a step that looked like it was taking a lot longer than I would expect. I found it was doing 12 cursors in a row on the same table. For each row in the source table it would try and insert into the target table and the insert was wrapped in a TRY...Catch to suppress duplicate row insert errors and keep going. Why? Because a few rogue cases had two rows instead of the one row they should have had. (For the record, I didn't write this, I just inherited it.)

I changed each cursor to a straight INSERT target-table SELECT ... FROM source-table using a row_number function to pick out just the first row for each case. I thought it would go a fair bit faster. In fact, it went from 7 minutes to 15 seconds. I had to check there was still the right number of rows in the target table!

I wish I had time to see how much of the overhead was the cursor and how much was the TRY...CATCH. I suspect each TRY...CATCH only makes a tiny difference but thousands of them will add up.

Does this folder exist?

2009-12-11T13:21:00Z

Today I tripped over a problem that was new to me. I wanted to find out, in SQL, whether a folder existed in the file system. I knew about using xp_fileexist to check for existence of a file but what about a folder? Well, it doesn't work on a folder name and there's no sign of xp_folderexist. I finally found in a forum that you use xp_fileexist and pass in the imaginary file nul in that folder (eg. to check if the folder 'C:\test' exists, check if file 'C:\test\nul' exists). There was no explanation but, according to Raymond Chen who knows a few things, NUL is a special, 'magic' file name that goes back to DOS 1.0. So some days you learn an old new thing.

Reporting security audit

2009-12-01T22:06:00Z

Today I set out to produce a list of who has access to each report so I could get this audited by the business management. I didn't think it would take long. Download a couple of scripts, evaluate them, pick a good one, execute it, and on with the next job. After all, this is crucial stuff. There's no point agonising over reducing users's rights in the database engine to the minimum possible if you then go and give them access to reports that summarise all the data in a readable, saveable, printable form. Clearly then, security auditing of Reporting Services must be a common practice. Or so I thought.

Well, I found bits of the puzzle but I didn't see anything packaged up and ready to go. I'm sure there are tools you can buy but I kind of resent paying serious money for a product that basically just queries my own data. I prefer source code where I can see what's going on and learn something new.

So I pulled the pieces together, solved a few problems myself and came up with a stored proc that goes through Reporting's security records for each report, resolves any domain groups recursively down to lists of users, and lists it all out. If you have a look through, you'll see Reporting doesn't make this security data easy to get hold of. Also, I had to add a caching mechanism for domain group membership because it took ages to query the domain that many times.

Feel free to use the code, which you can download here.

set quoted_identifier on
go
set ansi_nulls on
go
 
if exists (    select     1 
        from     dbo.sysobjects 
        where     id = object_id(N'dbo.rpt_ITReportsSecurity') 
            and objectproperty(id, N'IsProcedure') = 1    )
    drop procedure dbo.rpt_ITReportsSecurity
go
 
 
 
create proc dbo.rpt_ITReportsSecurity
as
 
/*
----------------------------------------------------------------------
Version:    1.0
Date:        01/12/2009
----------------------------------------------------------------------
*/
 
set nocount on
 
declare @FolderName        nvarchar(850)
    , @ReportName        nvarchar(850)
    , @ReportPath        nvarchar(850)
    , @idoc            int
    , @xmlfile        varchar(8000)
    , @GroupUserName    varchar(50)
    , @Role            varchar(50)
    , @GroupNoDomain    varchar(50)
    , @i            int
    , @cmd            nvarchar(2000)
 
 
create table #perms
(
    GroupUserName    varchar(50)
    , Role        varchar(50)
);
 
create table #allperms
(
    FolderName    nvarchar(850)
    , ReportName    nvarchar(850)
    , GroupUserName    varchar(50)
    , [Role]    varchar(50)
);
 
create table #temp_domaingroup 
(
    [output] varchar(8000)
);
 
create table #group_member 
(
    [Group]        varchar(50)
    , Member    varchar(100)
    , [Role]    varchar(50)
);
 
 
-- Loop through reports
declare report_cursor cursor
local forward_only 
for 
select    f.Name as Folder
    , r.Name as ReportName
    , r.Path as ReportPath
from    ReportServer.dbo.Catalog r with (nolock)
    inner join ReportServer.dbo.Catalog f with (nolock) on f.ItemID = r.ParentID
where    r.Type = 2 -- Report
order by f.Name
    , r.Name
 
open report_cursor
 
    fetch next from report_cursor 
    into     @FolderName
        , @ReportName
        , @ReportPath
 
 
    while @@fetch_status = 0
    begin
 
        -- Get the user/groups and their roles from the XML
 
        truncate table #perms;
 
        set @xmlfile = (select    sd.XmlDescription
                from    ReportServer.dbo.Catalog c
                    inner join ReportServer.dbo.Policies p on p.PolicyID = c.PolicyID 
                    left join ReportServer.dbo.SecData sd on sd.PolicyID = p.PolicyID and AuthType = 1
                where    c.Path = @ReportPath
                    and p.PolicyFlag = 0) --as far as I can tell, this means not a system policy
 
        exec sp_xml_preparedocument @idoc output, @xmlfile
 
        insert    #perms 
            (GroupUserName
            , [Role])
        select    GroupUsername
            , Role
        from    openxml    (@idoc, N'/Policies/Policy/Roles/Role',2)
            with    (GroupUsername varchar(50) '../../GroupUserName'
                , Role varchar(50) './Name')
 
        exec sp_xml_removedocument @idoc;
 
        -- Loop through users/groups
        declare group_cursor cursor
        local forward_only 
        for 
        select    GroupUserName
            , [Role]
        from    #perms
        where    GroupUserName <> 'BUILTIN\Administrators'
            and [Role] <> 'View Folders Role';
 
        open group_cursor
 
            fetch next from group_cursor 
            into     @GroupUserName
                , @Role
 
            while @@fetch_status = 0
            begin
 
                -- Strip the domain off the user/group
 
                set @GroupNoDomain = @GroupUserName
                set @i = charindex('\', @GroupNoDomain)
 
                if @i > 0
                begin
                    set @GroupNoDomain = substring(@GroupNoDomain, @i + 1, 100)
                end
 
                -- Users in the groups are cached so we only need to query the domain once for each group
 
                if not exists (select 1 from #group_member where [Group] = @GroupNoDomain)
                begin
                    -- Query the domain
 
                    truncate table #temp_domaingroup;
 
                    set @cmd = 'dsquery group -name "' + @GroupNoDomain + '" | dsget group -members -expand';
 
                    insert    #temp_domaingroup 
                    exec xp_cmdshell @cmd;
 
                    if exists (select 1 from #temp_domaingroup where [output] like 'dsget failed%')
                    begin
                        -- It's a user, not a group, so add it to the cache
 
                        insert    #group_member
                            ([Group]
                            , Member
                            , [Role])
                        values    (@GroupNoDomain
                            , @GroupUserName
                            , @Role);
                    end
                    else
                    begin
                        -- Add the users in the group to the cache
                        insert    #group_member
                            ([Group]
                            , Member
                            , [Role])
                        select    @GroupNoDomain
                            , substring(x.Member, 5, len(x.Member) - 4)
                            , @Role
                        from    (
                            select    substring([output], 1, charindex(',OU=', [output], 1) - 1) as Member
                            from    #temp_domaingroup 
                            where    [output] is not null
                            ) x;
                    end
                end
 
                -- Add the user or group users and their roles to the permissions list
 
                insert    #allperms
                    (FolderName
                    , ReportName
                    , GroupUserName
                    , [Role])
                select    @FolderName
                    , @ReportName
                    , Member
                    , [Role]
                from    #group_member
                where    [Group] = @GroupNoDomain;
 
                -- Get the next cursor row
                fetch next from group_cursor 
                into     @GroupUserName
                    , @Role
 
            end
 
        close group_cursor
        deallocate group_cursor
 
        -- Get the next cursor row
        fetch next from report_cursor 
        into     @FolderName
            , @ReportName
            , @ReportPath
 
    end
 
close report_cursor
deallocate report_cursor
 
 
select    *
from    #allperms
order by ReportName;
 
go

A better way to arrange your query windows

2009-11-28T19:37:00Z

The developers of Management Studio and, before that, Query Analyzer have made a bit of a habit of building killer features and then hiding them in the small print of Books Online. Now you may already know that SQL 2000 introduced a new way to arrange your query and result windows, but I only just found out about it. I think it's great so, in case you blinked and missed it too, here's how it works.

Since the dawn of time you edited your query in a full window and, when you ran it, the results covered the bottom half of the query. You could use Ctrl+R to toggle the visibility of the results. When you think about it, it's actually pretty clumsy. These are the only combinations you can see easily:

half of the query and half the results
the whole query
er, that's it

I was so used to it that I didn't really notice. Until somebody showed me a much better way to do it.

There's an option under Query Results | SQL Server | Results to Grid called Display results in a separate tab. If you tick this and its sub-option the behaviour gets much more usable. When you run the query the results fill the screen. You get a second row of tabs below the row of tabs for the query windows you've got open. This second row has tabs for the query, results and messages and (optionally) things like the query plan. It looks like this:

You can tab backwards and forwards between these tabs using F6 and Shift+F6 respectively. You can quickly toggle between the query and results and you can see both of them properly.

You really should try this. Your muscle memory will protest but stick with it. I think you'll find it was worth it.

How to process a cube in a SQL Agent job

2009-11-25T11:09:00Z

It can be done but it's not well documented and it's complicated by the fact that you can't easily get the result of the process. Unless you actively check and manually raise an error if there's been a problem, the job will report sucess no matter how screwed up the cube processing is. That part is all about the XML results that are output by the process.

Here's how I do it (and I'd be very interested if you have any suggestions to improve it):

Connect to the Analysis Services server in SQL Server Management Studio.

Right click on the database and select Process.

Configure all the options and then use the Script button to grab the XML that defines the processing job. It will look something like this:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
        <KeyErrorLimit>-1</KeyErrorLimit>
        <KeyErrorLogFile>\\rep01\joblogs\ProcessASOnBI1KeyErrors.log</KeyErrorLogFile>
        <KeyNotFound>ReportAndContinue</KeyNotFound>
        <KeyErrorAction>ConvertToUnknown</KeyErrorAction>
        <KeyErrorLimitAction>StopProcessing</KeyErrorLimitAction>
        <KeyDuplicate>IgnoreError</KeyDuplicate>
        <NullKeyConvertedToUnknown>IgnoreError</NullKeyConvertedToUnknown>
        <NullKeyNotAllowed>ReportAndContinue</NullKeyNotAllowed>
    </ErrorConfiguration>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
        <Object>
            <DatabaseID>BI1</DatabaseID>
        </Object>
        <Type>ProcessFull</Type>
        <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
</Batch>

Create a new SQL Agent job.

Add a step to get rid of any old output file(s). Change the step type to CmdExec and paste in this code, modifying the file name:

if exist "\\rep01\joblogs\ProcessASOnBI1Log.xml" (del "\\rep01\joblogs\ProcessASOnBI1Log.xml")

Add a step for the processing. Change the step type to SQL Server Analysis Services Command and paste in the XML. Go to the Advanced page and give it an XML output file - with the same name you used in the previous step - to write its results to.

Add a step to check the output XML file to see whether the process was successful. Change the step type to T-SQL and paste in this code:

-- Check the result files from the cubes
 
set nocount on;
 
create table #File
(
        ResultFileName        varchar(255)
);
insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI1Log.xml');
insert #File (ResultFileName) values ('\\rep01\joblogs\ProcessASOnBI2Log.xml');
 
create table #FileContents 
(
        LineNumber        int identity
        , LineContents        nvarchar(4000)
);
 
declare @FileContents        nvarchar(4000)
        , @NewLine        char(2)
        , @FileName        varchar(255)
        , @CubesFailed        bit
        , @CmdLine        varchar(300)
 
set @NewLine = char(13) + char(10);
 
set @CubesFailed = 0;
 
-- Loop through result files
declare file_cursor cursor
local forward_only 
for 
select        ResultFileName
from        #File;
 
open file_cursor
 
        fetch next from file_cursor 
        into         @FileName
 
 
        while @@fetch_status = 0
        begin
 
                set @CmdLine = 'type ' + @FileName;
 
                insert #FileContents
                exec master.dbo.xp_cmdshell @CmdLine;
 
                select        @FileContents = isnull(@FileContents, '') + @NewLine + isnull(LineContents, '')
                from        #FileContents;
 
                select @FileName;
 
                select @FileContents;
 
                set nocount off;
 
                if @FileContents like '%error%'
                begin
                        set @CubesFailed = 1
                end
 
                delete #FileContents;
 
 
                -- Get the next cursor row
                fetch next from file_cursor 
                into         @FileName
 
        end
 
close file_cursor
deallocate file_cursor
 
 
drop table #FileContents;
drop table #File;
 
 
if @CubesFailed = 1
begin
        raiserror('Cubes failed to build.', 16, 1)
end

Modify it to add your own file name(s) into #File at the top. This step will raise an error if the processing failed. If you don't do this, you won't know whether the job has worked.

Is this log backup file the next one to restore?

2009-11-22T20:52:00Z

When you restore from a transaction log backup, SQL Server checks the file against the database and gives you an error if the file is not the next one in the sequence. I wanted to know how this works so I did some digging. Not much in Books Online that I could see so I did some practical investigation with some of my log shipping files.

It looks like it compares the last LSN in the backup file's header with the last LSN in the database's restore history. If the last LSN in the file is less than the last LSN in the restore history, it indicates that the database already contains those transactions. Here's some code that does it:

declare    @LastDBLSN    numeric(25,0)
        , @LastFileLSN    numeric(25,0)
 
--Get last LSN from database
select    top 1 @LastDBLSN = bs.last_lsn
from    msdb.dbo.restorehistory rh with (nolock)
        left join msdb.dbo.backupset bs with (nolock) on bs.backup_set_id = rh.backup_set_id
where    rh.destination_database_name = @DBName
order by rh.restore_history_id desc
 
--Get last LSN from backup file
declare @logbak table
(
    BackupName nvarchar(128)
    , BackupDescription nvarchar(255)
    , BackupType smallint
    , ExpirationDate datetime
    , Compressed tinyint
    , Position smallint
    , DeviceType tinyint
    , UserName nvarchar(128)
    , ServerName nvarchar(128)
    , DatabaseName nvarchar(128)
    , DatabaseVersion int
    , DatabaseCreationDate datetime
    , BackupSize numeric(20, 0)
    , FirstLSN numeric(25, 0)
    , LastLSN numeric(25,0)
    , CheckpointLSN numeric(25,0)
    , DatabaseBackupLSN numeric(25, 0)
    , BackupStartDate datetime
    , BackupFinishDate datetime
    , SortOrder smallint
    , CodePage smallint
    , UnicodeLocaleId int
    , UnicodeComparisonStyle int
    , CompatibilityLevel tinyint
    , SoftwareVendorId int
    , SoftwareVersionMajor int
    , SoftwareVersionMinor int
    , SoftwareVersionBuild int
    , MachineName nvarchar(128)
    , Flags int
    , BindingID uniqueidentifier
    , RecoveryForkID uniqueidentifier
    , Collation nvarchar(128)
    , FamilyGUID uniqueidentifier
    , HasBulkLoggedData bit
    , IsSnapshot bit
    , IsReadOnly bit
    , IsSingleUser bit
    , HasBackupChecksums bit
    , IsDamaged bit
    , BeginsLogChain bit
    , HasIncompleteMetaData bit
    , IsForceOffline bit
    , IsCopyOnly bit
    , FirstRecoveryForkID uniqueidentifier
    , ForkPointLSN numeric(25, 0) null
    , RecoveryModel nvarchar(60)
    , DifferentialBaseLSN numeric(25, 0) null
    , DifferentialBaseGUID uniqueidentifier
    , BackupTypeDescription nvarchar(60)
    , BackupSetGUID uniqueidentifier null
);
 
insert @logbak exec ('restore headeronly from disk = ''' + @FileName + '''');
 
select     @LastFileLSN = LastLSN
from    @logbak;
 
-- Compare them
if @LastFileLSN < @LastDBLSN
begin
        -- Already got these transactions
end
else
begin
        -- Restore log
end

Idera discontinues SQLSafe Freeware Edition

2009-11-10T10:58:00Z

No longer available for download. Check it out here: http://www.idera.com/Products/Free-Tools/SQL-safe-Freeware-Edition/

Bummer! I'm using this extensively. Now you have to choose between the Enterprise edition and the Lite Edition. The Lite Edition is $295 per instance, which is not quite as attractive a price as free. I suppose it was too good to last. Luckily, I just got the budget approved to upgrade to 2008 R2 Standard, which has native backup compression. Can't wait!

Report Manager folder & file security

2009-11-02T14:40:00Z

Just found the answer to something that has been irritating me slightly for ages: how to give a user access to a report without giving them access to all the other reports in the folder.

We have folders that are based on the roles people perform in the company. We granted the appropriate domain groups access to their folders in the Browser role. That gives those people access to the reports in those folders because, by default, a report inherits its security from its parent folder. This works great until the Sales Manager says, "Can you give Student Joe access to the Sales Stats report so he can do a special job for me?" This report is in the Management folder which also contains a load of sensitive reports that Student Joe cannot be allowed to see, so you can't give him the Browser role on the folder. You can override the inherited security on the Sales Stats report by putting Student Joe in the Browser role at that level but he still can't get to it in the Report Manager because he has no rights on the Management folder. His only access is via a URL direct to the report.

We thought we were stuck with this and had got used to it. After all, people could get to what they needed so there wasn't much of a business case for sorting it out properly. But today I got another such request and the red mist siezed me. (Perhaps it's the weather?) I couldn't believe this was impossible. Surely they thought of this? And, thanks to the excellent Hitchhiker's Guide to SQL Server Reporting Services, I discovered that they did. They just didn't go out of their way to mention it.

Here are the steps:

On the Home page, click on Site Settings.
Click on Configure item-level role definitions.
Create a new role (say, View Folders Role) and assign it the View folders task.
Now you can assign a user or group the View Folders Role on a folder and they will be able to navigate to the folder and there they will see any reports you have given them access to.

Easy when you know how!

Something to check for on your Disaster Recovery plan

2009-10-12T07:46:00Z

We just had something happen that caught us by surprise. We were log shipping to a remote warm standby server. It was all running smoothly and we were ready for anything. Then the remote server went down - right down. Looks like we might have to rebuild it. Meanwhile, I've got transaction log backups piling up and nowhere to ship them to. Without a standby server that's on the same backup cycle as the live server we just don't have a DR plan that applies. Now I have to tell people I can't do those urgent things I promised to deliver because I have to sort this out. And I thought it was hard enough getting buy-in for setting this up in the first place!

When we drew up our plan we did briefly consider what would happen in this scenario. We were far too casual. We said to ourselves, "Well, we didn't have this capability at all until now and we've never had a disaster so it won't be such a problem to be without it again while we fix it. After all, you can only plan for so much and after that you just have to wing it." Wrong answer! The last thing you want to be doing when things are all screwed up is to start improvising. A standby server is just as likely to fail as a live server, maybe even more likely. After all, you keep a keen pro-active eye on your live servers - you are doing that, right? - but it's hard to justify spending too much time monitoring a standby server. As long as the replication/mirroring/log-shipping works, that's fine. Wrong! A standby server is just as important and warrants just as much care and feeding.

Don't let this happen to you. What does your plan say about the standby going down? How does it affect your routine DR jobs etc. How will you get a standby server back again? Do you need to alert the business that you may need some budget? How will you re-synch it with your live server so you can resume normal operations again? Knowing in principle is not enough. Write it down. Test it. Know it will work.

More fun with SQL 2008 and Windows 7

2009-10-09T07:21:00Z

I've just got round to trying out Reporting Services on my laptop, having rebuilt it with Windows 7. I'm fairly comfortable with Reporting on XP but I've clearly got some reading to do because there was some baffling security weirdness that I never would have fixed without help. Once again, a great product is let down by stuff that you have to be pretty advanced to understand. With the installation problems I had and this one, if I was a newbie I would probably by now have written off SQL Server up as a pile of crap and started looking at a competitor's product.

This is SQL 2008 Developer installed on Windows 7 Pro on a standalone laptop. I have no domain. I defaulted everything during installation: Report Server is in Native mode and the Reporting Services service is running under the Local System account. When I browsed to http://<machine-name>/Reports it asked me for my login and password. Odd. I thought it would default to Windows authentication and just let me in. Then, although I'm a member of the local Administrators group in Windows, I couldn't do anything in Report Manager. I could see the Home page but not the links to administer security etc. Only My Subscriptions. So I went into SQL Management Studio and connected to Reporting Services. I could see some stuff but I couldn't view details of anything. Those options were greyed out.

I read the Books Online stuff about authentication modes etc. but my brain started to hurt. I'm not a web security guru! So I asked the question on StackOverflow (http://stackoverflow.com/questions/1540800/sql-2008-reporting-services-i-have-no-rights) and got the answer. Thanks, Mozy.

To cut a long story short, when you go into IE in Windows 7 UAC ensures that, even though your account is an Administrator, once you log in you are no longer running with those privileges. So, when you get to the Report Manager you are a nobody. So you have to add http://<machine-name> to your trusted sites. Then you have to use Run As Administrator on IE to get into Report Manager and then add your own login as a System Administrator in Site Security. Finally you can actually try the technology out.

I just don't think I was trying to do anything exotic here, Microsoft. I'm a reasonably accomplished .NET developer, DBA, and Reporting administrator and developer in SQL 2005. And I've got completely stuck twice now using two of your flagship new products in their 'out of the box' configuration. There may be perfectly sound 'secure by default' reasons for these things but please make it easier to discover how to switch the stuff on. With PowerShell you can't even run your own scripts on your own machine without making some changes but that was quite well publicised and it's easy to find the instructions. But I haven't found it like that with SQL Server 2008. Maybe I missed some excellent advice about all this somewhere in Books Online. I don't think so but, if I did, that's the point: it was easily missable! Please don't assume that, just because it's a server product, it's going to be set up by geek gods who instinctively understand a wide range of network and security details. I suspect it usually won't be. And it certainly wasn't this time!

Backup compression on SQL 2005

2009-09-24T09:13:00Z

Last night, after a lot of preparation, I finally flicked the switch on the backup compression solution I've been implementing. The results were dramatic but in a good way:

backup jobs 60% faster
50% disk space saving
Reporting & BI build 30% faster
Reporting & BI ready 3 hours earlier than before

Each night my SQL Agent job runs a full backup of the live databases and copies the backup files to a file server folder from where they are later backed up to tape. The last step kicks off another job on my reporting server which restores from the file server's copy of the backup files and then rebuilds the data warehouse database and Analysis Services cubes. The whole process was only just finishing in time for the start of the business day. This morning it was all over by 04:15.

Backup compression substantially speeds up backups and restores by using more CPU and RAM and less disk I/Os. Because the disk is the bottleneck you get a faster run time and a smaller backup file. SQL 2005 doesn't have native backup compression, unlike SQL 2008, so you need a third party utility. I used Idera's SQLSafe Freeware Edition. I have no connection with Idera. I simply spotted the product when I was looking into how much compression might cost. The price was great ($0!), so I tried the product and liked it.

And it all worked first time!

Column length in SQL 2005 Analysis Services cube

2009-09-18T09:20:00Z

I've been dark for a while as I've been on holiday in Turkey on the 'Tourqoise Coast'. Great people, hot and sunny, amazing diving water, thoroughly recommended.

Anyway. I had a new one today with a cube. A few weeks ago, in the underlying database, a varchar column's max length was increased from 50 to 100. The cube continued to build fine until somebody created a new row with a value longer than 50 characters. The cube failed to build with an error about binding. Annoyingly, refreshing the data source view wasn't enough. I had to go into the dimension and actually update the properties there too.

Worth bearing in mind.