Azure Windows Server 2012 VMs – non en-US Locale

Just a quick scream out into the void…

You know when you build a new server, a real one, you get to choose what locale / regionalisation / language settings you want the server to be installed with?

How are you supposed to do that with Azure VMs built from the provided image library?

Unfortunately, it doesn’t appear to be as straightforward as it should be. I found this Feedback ticket on the Azure Forums: http://feedback.azure.com/forums/216843-virtual-machines/suggestions/6197573-set-locale-on-creating-vm

<Insert rant on middle-endian date formats and the lack of take-up of ISO-8601>

Please, all you non-US Azure VM users – go and vote it up!

Posted in SQLServerPedia Syndication | Tagged , | Leave a comment

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