SQL Server 2016 – New Feature – Stretch DB

This applies to the CTP – it might not apply to any RTM version

Have you been following the SQL Server 2016 at Microsoft.com site? I really haven’t had much time to pay attention to this recently, but this one thing in the list of benefits caught my eye.

SQL 2016 Benefits

Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application change

Oh, this sounds like it could be useful for just about everyone with a reasonable transaction history to look after for whatever reason – get the data out of your main OLTP database, but still have it accessible for reports, auditing or compliance. Just about every client I’ve ever worked for could benefit from this.

And look – there’s a white paper to download!

SQL 2016 Whitepapers

I’m a sucker for a good whitepaper, so I downloaded and had a little read of the SQL Server 2016 Hyper-scale Cloud technical white paper, as that seemed to be the right one.

Oh, man, this is good stuff. Pages 10-12 of the white paper talk big numbers – a billion row table, 1TB table, 5TB databases, tiered storage. All good, sensible arguments for looking into data archiving. Indeed, this feels a bit magic bulletish.

With SQL Server 2016 Stretch Databases, you can stretch an on-premises table to Azure transparently for near-infinite capacity with low TCO.

The headlines are that things like backup, restore, index maintenance etc, is done only on the local database, the warm/hot data, and not on the stuff that’s been migrated up to the cloud (the cold, archived data). Massive potential speed improvements could result here for the OLTP and maintenance stuff; but anything that uses the cloud data is going to be (not surprisingly) a bit slower.

There are moderate performance reductions when accessing the cold data

However…

The MSDN documentation for the Stretch Database makes this seem less like the magic bullet – well, once you start digging a little.

Limitations

There are various limitations around this functionality. The ones that drew my eye were that you weren’t allowed to have foreign keys referencing the table that’s being stretched into the cloud. That’s probably not an issue for transaction history records.

Every time you run the wizard to enable Stretch for a database, the wizard creates a new Azure SQL Database server. By default, the number of servers that you can create per subscription is limited.

By default, the wizard creates a SQL Database server with the Standard service tier and the S3 performance level

Now, I’ve been doing some digging in this area recently, and that didn’t seem to me to be so promising. And indeed it isn’t. According to the Azure SQL Database Service Tiers and Performance Levels documentation, the S3 tier has a maximum size for the database of 250GB. (The highest level, Premium/P3, gives 500GB.) Oh, and the maximum size for a blob in storage is also 500GB.

From the white paper talking about multi-terabyte / near infinite archives to the maximum size of a Azure SQL Database being 250GB (or 500, I know…) – something seems to me to be missing, and I (as yet) can’t see what it is.

Yes, this is only a CTP of SQL Server 2016, and things might change in the Azure architecture. But that’s a big shortfall.

Posted in SQLServerPedia Syndication | Tagged , , | Leave a comment

New-AzureVM : BadRequest: The virtual network <foo> does not exist.

Just a quickie. It’s been a long week, and I’ve been trying to get my head around using PowerShell to automate deployments in Azure.

However, I did come across a little glitch in the way that this is supposed to work.

Here’s the syntax I was using:

New-AzureVMConfig -Name $myVMName -InstanceSize $myInstanceSize -ImageName $image.ImageName `
|Add-AzureProvisioningConfig -AdminUserName $myAdminName -Password $myAdminPassword -WindowsDomain -JoinDomain $myDomainFQDN -Domain $myDomainAdminUserDomain -DomainUserName$myDomainAdminUserName -DomainPassword $myDomainAdminPassword -MachineObjectOU $myDomainMachineOU `
|Set-AzureSubnet -SubnetNames $myVLAN `
|Set-AzureStaticVNetIP -IPAddress $myVMIPAddress `
|New-AzureVM -ServiceName $myVMName -Location $myLocation -WaitForBoot -VNetName$myVirtualNetwork

When I got the error message:

VERBOSE: 09:42:11 - Begin Operation: New-AzureVM - Create Deployment with VM TRTestSQLAuto
New-AzureVM : BadRequest: The virtual network SSDevPortal does not exist.
At line:6 char:10
+ |New-AzureVM -ServiceName $myVMName -Location $myLocation -WaitForBoot - ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [New-AzureVM], CloudException
+ FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.ServiceManagement.IaaS.PersistentVMs.NewAzureVMCommand

Now, the problem here is that when I look at the virtual network in the Admin Preview Portal, I see that it is there with the right name.

Virtual Networks

So, what to do? Just to check that I’d managed to get the spelling right, and there wasn’t a space or something, I exported the network config and looked at the XML:

Get-AzureVNetConfig -ExportToFile "C:\Users\TRushton\Desktop\VNetConfig.xml"

And there it is, buried in the NetworkConfiguration\VirtualNetworkConfiguration\VirtualNetworkSites area, the VirtualNetworkSite in question:

Network Config XML Excerpt

So, the name in the configuration is not the name that's showing in the Virtual Network window. No wonder it's not working...

Correct the code, and away we go. Job done. But no idea why there's a discrepancy.

Posted in SQLServerPedia Syndication | Tagged , , , | 2 Comments

SQL Server 2012 Silent Install problems

Just been working on a silent install script for SQL 2012, and had a few odd errors.

Unfortunately, I don’t have direct access to the server, just to my notes from the install.

Error: “‘<null or empty string>’ is not a valid value for setting ‘ACTION'”
This appears to be related to a corrupted INI file. Though I couldn’t see what the corruption actually *was*. Putting in a fresh copy of the same file seemed to fix that.

Problem with accessing C:\SQLFULL_ENU\1033_ENU_LP\x64\Setup\sqlsupport_msi\PFiles\SqlServr\110\Setup\qfdhsf2y\e4grzzmx (or, rather, an RTF in a path that looked like this but had different last two directories). Check the edition you have, as it seems to differ from edition to edition. Not sure why the installer would care, though.

Error: “The /UIMode setting cannot be used in conjunction with /Q or /QS.”
Problem in the configuration file. The file was built from a normal installation, and we hadn’t commented out the UIMODE line, which conflicts with the /Q parameter. Oops.

Error: “The configuration file ‘”C:\SQL2012Media\SQLConfv3.ini /ACTION=RUNRULES /RULES=GlobalRules’ does not exist. Specify a valid configuration file”
This was a real pain to figure out. Turns out that the installer line we were using:
Setup.exe /Q /Action=INSTALL /IACCEPT... /CONFIGURATIONFILE="C:\SQL2012Media\SQLConfv3.ini"
should have been
Setup.exe /Q /Action=INSTALL /IACCEPT... /CONFIGURATIONFILE=C:\SQL2012Media\SQLConfv3.ini
Yes. Putting the name of the config file in quotes failed to get this thing running.

And NetFX3 wasn’t there either.

Posted in SQLServerPedia Syndication | Tagged , , | Leave a comment

SQL Server 2012 installation – Error while enabling Windows Feature NetFx3

Just a quickie, as I didn’t have the time to take screenshots.

Installing SQL Server 2012 on a new Windows Server 2012 VM. Part-way through the installation, I got the following error message:

TITLE: Microsoft SQL Server 2012 Service Pack 1 Setup
——————————

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : 1726 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see http://go.microsoft.com/fwlink/?linkid=227143

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.3128.0&EvtType=0xBCCF9A5E%25401428%25401

——————————
BUTTONS:

OK
——————————

My first reaction was to try a spot of Powershell to install the feature:

install-windowsfeature NetFx3

Except that, apparently, there’s no such feature. Hmm.

A quick search led me to “The World According to Mitch”, which has a useful post “Installing NetFx3 on Windows Server 2012″ that shows this very error (complete with screenshot, if you really need to see it).

The solution that Mitch provides is quite simple: use Deployment Image Servicing and Management to fix.

dism /online /enable-feature /featurename:netfx3 /all

I had left the error on screen while trying the fix, so I was able to continue the installation; of course, the bulk of the features being installed failed, but it’s quick to redo.

Posted in SQLServerPedia Syndication | Tagged , , , , | Leave a comment

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