Documentation with Red Gate’s SQL Doc

Ah, documentation – how do we hate thee?

If you’re anything like me, you’re continually frustrated by the lack of adequate documentation about the databases you have to work with, but don’t have the time (or inclination) to write it yourself. Fortunately, there are tools available to help with that. Today, in order to help out a colleague who was trying to figure something out, I fired up Red Gate’s SQL Doc to provide him with a full set of documentation for a problematic database.

Of course, it is possible to build this documentation yourself, by painstakingly going through every object in your database (or collection of databases); however, this could take days, and be unreliable as there’s always the possibility of missing something, even in a relatively small database.

Launching SQL Doc

SQL Doc Launch from SSMS
Nice and easy – it’s there on the SSMS Object Explorer right-click menu. Here, you can see I’ve started at the database level, which is probably where most people start. It’s also on the menu shown for individual database objects. Unfortunately, it’s not available at anything above a single database – you can’t select a group of related databases, for example, or even the whole server.

 

 

 

Once SQL Doc has loaded, you can select which objects to document, and (I like tihs bit) set the MS_Description field for each object at this time:

SQLDoc writing back

SSMS Table Properties after SQLDoc write-back

We’re just a couple of clicks away from actually having some documentation:

SQLDoc Output Options

Output Options

There are various output options, two for interactive browsing, and two for printing or distribution.

  • HTML – a set of HTML files, all hyperlinked and ready to roll
  • DOCX – A Microsoft Word document.  You have some control over the page size
  • CHM – A Compiled Help file (this needs the Microsoft Help Workshop – fortunately, SQL Doc will take you to the right place to get this
  • PDF – again, you have control over the page size.

The “Generate timestamp” option does just that – adds the date/time to the end of the filename (or directory name, in the case of the HTML option). Here’s what the HTML output looks like
SQLDoc Output with MS_Description

SQLDoc Dependencies

So far, so good. And it is good. However…

Dynamic SQL in objects

If your database design makes extensive use of dynamic SQL in its stored procedures, then the documentation generated won’t have the appropriate links in it:

SQLDoc No Dynamic SQL

I have reported this back to Red Gate. I’m not the only one to have mentioned this, apparently, and I can understand that it might not be the simplest feature to add! Still, fingers crossed…

Permissions

So, what about the permissions required by the tool? Again, top marks to Red Gate – they have documented the required permissions on their help pages, which is more than can be said for the last lot I talked about!

The Beancounting

To run SQL Doc and generate as much documentation as you could possibly hope for takes about five minutes. By my reckoning, generating that level of documentation would take an hour an object, at least, so for any non-trivial database, it’s a *lot* of tedious, error-prone manual work. At the time of writing (April 2014), SQL Doc costs about UK£225 / US$370. How much do you pay the guys who write your documentation? I’m betting that that money wouldn’t buy two days of time from your in-house documentation gurus, let alone your DBA. Not sure if it’s for you? There’s a 14 day free trial so you can find out.

Small Print

Disclosure: I am a Friend of Red Gate, which means they provide me with software to try out; however, they haven’t asked me to write this review, and neither do they directly pay me. I write about their software because I want to, and I like it (the software).

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment

Error Msg 60400, LS::initializeFiles User Exception

Today’s error message, brought to me by SQL Server / Quest Litespeed for SQL Server:

Msg 60400, Level 16, State 1, Line 0
(LS::initializeFiles) user exception

Looking at the Transaction Log backup file that it was trying to restore at the time, it’s quite easy to see what the problem was – the file was 512 bytes. Not big enough to be a legitimate backup.

Fortunately, the next Transaction Log backup in the chain was good. However, a more informative error message would have been nice!

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment

Kindle Fire HD 7″ – Initial Reaction

While I was writing about the Kindle DX the other day, I was waiting for my new Kindle Fire HD to arrive. And I’ve now had the chance to have a quick play with it, and have some initial feedback / findings.

Memory

Bad things first: I bought the 16GB model – I know that you don’t get all the storage you think you’re going to get with these things, but still I was a little surprised / disappointed to see it reported in Windows as a 12.3GB device (77% of the capacity described). Seriously – if you’re looking at getting one of these – spend the extra £20 and get the extra memory. Mind you, that’s still a lot more memory than the DX has.

That’s the only bad thing – so far.

Library

My library has synchronised across the DX and the HD; a nice touch is that the collections I set up on one device have also migrated across. However, that has only applied to books bought from Kindle, rather than those loaded as PDFs. Not overly surprising, given the different storage formats.

Screen

Obviously, the screen is smaller than that of my DX but the pixel density is higher, as the resolutions are so similar.

Kindle DX Kindle Fire HD 7″
Screen Size 9.7″ 7″
Resolution 1200 x 824 1200 x 800

Poor quality of the images below is entirely down to my environment and using the camera on the phone.

Reading PDFs

This is why I get these devices – for reading. And the bulk of my library is PDFs from various people – Red Gate has a good selection of freebies, as does Microsoft; the other suppliers to my library (O’Reilly, Apress, Manning). O’Reilly does have a neat trick, though – it puts stuff into my Dropbox folder – when are the others going to do that? Hmm?

Anyway. Back to the quality / readability tests. The book I’m reading for these screenshots is Glenn Berry’s book on SQL Server Hardware, from Red Gate.

Looking at page 9 of the PDF, the text on both devices is readable; obviously, the DX being Monochrome has a harder time displaying code colours, and this makes it a bit harder to read

page 9 on Kindle DX

page 9 on Kindle DX

page 9 on Kindle Fire HD 7"

page 9 on Kindle Fire HD 7″

And now, looking at the picture on p22, we can again see that the DX is hampered by the lack of colour; however, the Fire also seems to reproduce the image more legibly.

page 22 on Kindle DX

page 22 on Kindle DX

Page 22 on Kindle Fire HD 7"

Page 22 on Kindle Fire HD 7″

Side by side, to get comparison of the different sizes of these devices:

Kindles DX & Fire HD 7" side by side

Kindles HD 7″ & DX side by side

Summary

Clearer images, brighter pages. The brightness could make it more tiring to read, and will chew through the battery more quickly; however, there isn’t much to choose in term of legibility between these two devices.

Big benefit of the new one: space. Lots of it to store lots of books.

And with that, I’m going to get back to my weekend.

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment

Note to self re: restoring backups of read-only databases

Restoring a full backup of a Read-Only database: fine.
Then restoring a diff backup of that same database: won’t work.

I’ve just wasted best part of a day chasing that one down. What’s worse is I should have remembered, as I’ve seen this before fairly recently (well, in the last year or so)…

I’ve not been well, y’know.

Edit

To add to the fun, this was using some third-party backup software, rather than SQL Server’s native backups. There was a sort of error message, but it was of the form “I’m sorry, Dave, I can’t do that”:

Cannot apply the backup on device LiteSpeed for SQL Server backup file to database '<foo>'.

Yeah. Helpful. In LiteSpeed’s defence, it’s a message generated by SQL Server.

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment

Kindle DX – update

I was just asked by a colleague if I would recommend the Kindle DX.

I wrote about the Kindle DX a couple of years ago, shortly after I got mine. In the two years since, the collection of books on it has grown somewhat – several hundred books and white papers, a mix of technical & fiction. Imagine the size of box needed to carry that lot around! I may have to start pruning, as I’m running a bit short of space now…

I see that it is now down to US$200 – about half the price I paid, which makes it a bit of a bargain. Of course, I haven’t yet tried it head-to-head with a newer Kindle, but I have just ordered a 7″ Fire HD, and will be giving it the once-over before passing it on to the family for them to abuse…

Just one thing I regret – ordering the external power supply. It’s a standard USB job, and, if you’re like me, you’ll have dozens of them already. Save your cable money, spend it on books instead.

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment

45 Database Performance Tips for Developers

Just a quick post to point out that Red Gate has just put out an e-book of 45 Database Performance Tips for Developers. It’s free. And to sweeten the deal, they’re bundling it with a 14 day free trial licence for their SQL Prompt product – a most excellent tool, and one that I under-use.

There are tips on ORM, basic T-SQL, indexing and database design. Each tip is just a few lines, in big print, so it won’t take long to read. It will, however, give you lots of ideas of things to do to make your apps better.

My favourite, and one that I’ve used extensively in the past, is number 8:

For small sets of data that are infrequently updated such as lookup values, build a method of caching them in memory on your application server rather than constantly querying them in the database.

I remember using this idea in a previous role. We had a utility application that made frequent round trips while processing through a list of jobs. These round trips were selecting very basic reference data (Process IDs, document type codes, workflow rules, that sort of thing); the problem is that it was doing all these checks for each document individually, and that caused half a dozen database round trips for each document. By re-writing the application to cache this data (which, after all, didn’t change day-to-day), I was able to take a 2.5 hour process down to about a minute, meaning that the work queues could be prioritised much more effectively.

I suspect that the whole application could have been transferred to a single server-side scheduled task, but that idea was a step too far…

[disclosure - I'm a "Friend of Red Gate", but I've not been asked to post this]

Posted in SQLServerPedia Syndication | Tagged , , , , , | 1 Comment

sqlenginedbstartconfigaction_install_configrc_cpu64 returned false

Some fun & games today.  I was building a nice new SQL Server 2008 R2 VM (yes, I know it’s 2013, but this counts as a big upgrade where I am at the moment).  90% or so of the way through the installation, I got the following error:

Access denied.

Wait, what?  Is that all I get?  “Access denied”?  Not very helpful.

Looking through the summary log, find this:

  Feature:                       Database Engine Services
  Status:                        Failed: see logs for details
  MSI status:                    Passed
  Configuration status:          Failed: see details below
  Configuration error code:      0xDC80C325
  Configuration error description: Access is denied
  Configuration log:             C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20131031_160530\Detail.txt

Dodgy formatting and all…

And in the detail log:

2013-10-31 16:40:00 SQLEngine: : Checking Engine checkpoint 'GetSqlServerProcessHandle'
2013-10-31 16:40:00 SQLEngine: --SqlServerServiceSCM: Waiting for nt event 'Global\sqlserverRecComplete' to be created
2013-10-31 16:40:00 SQLEngine: --SqlServerServiceSCM: Waiting for nt event 'Global\sqlserverRecComplete' or sql process handle to be signaled
2013-10-31 16:40:00 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2013-10-31 16:40:00 Slp: Access is denied
2013-10-31 16:40:00 Slp: The configuration failure category of current exception is ConfigurationFailure
2013-10-31 16:40:00 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2013-10-31 16:40:00 Slp: System.ComponentModel.Win32Exception: Access is denied
2013-10-31 16:40:00 Slp:    at System.Diagnostics.ProcessManager.OpenProcess(Int32 processId, Int32 access, Boolean throwIfExited)
2013-10-31 16:40:00 Slp:    at System.Diagnostics.Process.GetProcessHandle(Int32 access, Boolean throwIfExited)
2013-10-31 16:40:00 Slp:    at System.Diagnostics.Process.OpenProcessHandle()
2013-10-31 16:40:00 Slp:    at System.Diagnostics.Process.get_Handle()
2013-10-31 16:40:00 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceBase.WaitSqlServerStart(Process processSql)
2013-10-31 16:40:00 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.ConfigSQLServerSystemDatabases(EffectiveProperties properties, Boolean isConfiguringTemplateDBs, Boolean useInstallInputs)
2013-10-31 16:40:00 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.DoCommonDBStartConfig(ConfigActionTiming timing)
2013-10-31 16:40:00 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
2013-10-31 16:40:00 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
2013-10-31 16:40:00 Slp: Exception: System.ComponentModel.Win32Exception.
2013-10-31 16:40:00 Slp: Source: System.
2013-10-31 16:40:00 Slp: Message: Access is denied.

Identifying possible causes

Microsoft gives us this helpful document for How to identify SQL Server 2008 setup issues in the setup log file.

So.  Possible causes.  There’s nothing official out there from Microsoft, but a search on sqlenginedbstartconfigaction_install_configrc_cpu64 did show a few possible causes:

  • Checked / Debug Build of Windows.  No, not applicable in this case – it’s a proper build.
  • MSXML build problem.  Doesn’t feel right, particularly given some of the detail in the setup error logs. Decided not to pursue this
  • Insufficient permissions.  I originally discounted this, as I was set up as a member of the local administrator group.  However, this *was* the right thing.

Insufficient Permissions

Searching for possible failure reasons here lead to the following documents from Microsoft:

For a more detailed view of why the Debug right is required, see http://blogs.msdn.com/b/joaol/archive/2008/12/02/sql-server-2008-installation-aborts-due-an-access-denied.aspx

But wait – I’m local admin. Why haven’t I got these rights already?

I don’t know. Ask your sysadmin / Group Policy Administrator.

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment