Back from the Future!

We like to live on the edge, so we don't mind trying out the latest version of SQL in production do we? Sure, why not, SQL Server 2017 is stable and it's an enterprise class database platform (didn't used to be able to say that :-( ). Well what happens when you reach the maximum database size and have to move a database from the cutting edge SQL 2017 Express edition (slightly sarcastic here) to a Standard or Enterprise server and the only other servers available are 2016? Oops.

I discovered the problem when trying to restore a backup from the 2017 instance onto a 2016 instance. SQL coughed up this error message:

------------------------------
Restore of database '*******' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 14.00.1000. That version is incompatible with this server, which is running version 13.00.4001. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)
------------------------------

"Ok, that's inconvenient" I thought, "what now?"

So I googled (as you do in situations like this) and came across this nice article https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/ which explained how to export the entire database via script...nice! Only problem was that the script it produced was too large to open in Notepad or SQL Management Studio (5.8GB) and I needed to amend the CREATE DATABASE statement as well as execute it.

Well, to amend the script this article proved rather useful https://www.petri.com/tip-opening-large-files-windows . I downloaded EmEditor and fixed the script so that the database files were created in the right path. Then, I logged on to the SQL 2016 server, opened a command prompt and executed the massive script using SQLCMD:

C:\>SQLCMD -S .\MyInstance -E -i "C:\...\MyMassiveScript.sql"

Easier than I expected really, it all just worked...which is not a common scenario :-)

Later dudes!

PS: the script will take ages to execute so be patient. It is, without a doubt, the worst performing method of inserting huge amounts of data into a SQL database, i.e. one record at a time.

When does a SQL Table think it's a View?

Try this one out:

1. Create a SQL database
2. Create a couple of tables
3. Create a view that queries those tables and make sure to include a calculated column
4. Create an empty table from the view using SELECT INTO...WHERE 1=0
5. Drop the view
6. Rename the empty table to the name of the dropped view
7. Try and insert some data into the table
8. Scratch your head...

Yup, SQL still thinks the object with the same name is a view and because it includes a calculated column it won't let you insert data into it.

To solve the issue, execute the sp_refreshview proc using the name of the new table.

What a SISS-up!

Have you ever had the situation where you have set up automated deployment of a SSIS package and you upgrade the package and the SQL Server it is deployed to, then suddenly your packages start behaving weirdly?

Well believe it or not it can happen that you are able to deploy a SQL Server 2016 package to a SQL Server 2016 server using a 2014 SSIS deployment wizard and get no exceptions during deployment. Then your packages start misbehaving, like simply missing out script tasks and completing successfully. I kid you not!

So, there lesson here is that just because it deployed successfully using the wrong wizard, don't assume it will work correctly.

Oracle, CredSSP, what?? I just want to connect to my servers!

Hi all,

Two days ago I was doing some general maintenance using the new Windows Admin Centre server that I've just deployed to my test environment when all of a sudden, I encounter a WinRM error when attempting to connect to servers within my estate.

At first, I thought it was a bug with WAC but keeping an open mind, I attempted to establish a remote session via PowerShell to the same server and again... a WinRM error?

Now the sweat starts to form as I'm thinking that there may be a bigger problem with my estate.  I then attempted to establish an RDP connection to my servers and was met by this error:

Very Odd?  I then checked using another system which worked as expected so at least at this point the problem is with my PC and not my servers/network.

Next step, what has changed on my system in the past 24 hours?  Of course, windows updates...

I check for any newly installed windows updates and found KB4103727 had been deployed to my machine.

A quick look at the release notes from MS and this update does contain a fix relating to RDP and CredSSP. (https://support.microsoft.com/en-gb/help/4103727/windows-10-update-kb4103727)

I removed the update and rebooted my PC which seemed to fix the issue.  RDP, etsn via PowerShell and WAC all started to work as before.

Great? almost....Except that very same update contains a large number of security fixes.  So a little more diggin'

Adding the following keys and DWORD to the registry of the source computer seems to fix the issue without removing the update.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP\Parameters]
"AllowEncryptionOracle"=dword:00000002

If the keys are missing, simply add them and remember, always backup before making any changes to the registry of a computer.

I've now left my PC with the amended registry and so far, so good.  If that changes I'll update you all.

Enjoy :)

I am a Defector!

Ever worked with SQL Server Master Job Servers? A great idea really - have a single server that pushes out job definitions to loads of other instances. The problem comes when you try and 'defect' a server and SQL just says 'MSX defect failed for JobServer', without any explanation.

Ok, so there are some obvious things to check, like whether or not the master server still exists and is online, whether there are any security issues between the two instances and so on. If you get to the stage that we were in where the master server doesn't know about the target server and the target server can't defect...well, this is what I did on the target server instance to resolve the issue:

1. Try force defection the standard way
EXEC dbo.sp_msx_defect @forced_defection = 1

if that fails...

2. Clear the MSXServerName registry key value

EXECUTE master.dbo.xp_instance_regwrite 

                N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'MSXServerName',

N'REG_SZ',

N''

3. Delete the MSXPollInterval registry key value

        EXECUTE master.dbo.xp_instance_regdeletevalue

                N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'MSXPollInterval'

4. Remove the entry from sqlagent_info

        DELETE FROM msdb.dbo.sqlagent_info

        WHERE (attribute = N'DateEnlisted')

5. Delete any master jobs

        EXECUTE msdb.dbo.sp_delete_all_msx_jobs @current_msx_server, @jobs_deleted OUTPUT

6. Restart SQL Agent

7. Delete the MSXOperator SQL Agent Operator

        EXECUTE msdb.dbo.sp_delete_operator @name = N'MSXOperator'

This worked for me but usual caveats apply.

Later dudes!

Size does matter!

Well, it does to WinRM. That is what I learned today. I have been using DSC to provision SQL Server instances for over a year now and never before had I encountered this particular error:

The WinRM client sent a request to the remote WS-Management service and was notified that the request size exceeded the configured MaxEnvelopeSize quota.
    + CategoryInfo          : LimitsExceeded: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : HRESULT 0x80338111
    + PSComputerName        : **************

Naturally, I googled and the solution is rather simple. Just check what the winrm setting is for the target machine and increase the size.

winrm g winrm/config
winrm set winrm/config @{MaxEnvelopeSizekb="8192"}

What was interesting though is why this was the first time I was getting the error?? It turns out that the MOF files I was sending to the server were slightly bigger than normal because I was configuring four instances of SQL rather than the previous three. The file was shade under 500kb, which when wrapped in an envelope must have exceeded the default envelope size of 500kb.

Simples...

SSDT Causes a Split!

We use SQL Server Data Tools almost daily and have done so since 2005 when Microsoft release Visual Studio for Database Professionals. It was the first tool to put SQL databases under source control using a fully integrated IDE. Not only that, it also validated our code during Build by deploying it to a SQL Express instance that was deployed as part of the tool. For the first time we could start treating database code in the same way as our .Net assembly code and the like.

So you can imagine the look on my face when making a change to a SQL Server 2016 database project yesterday when SSDT decided to throw an exception because I used the STRING_SPLIT() function in my code. After all these years of trusting the tool to keep me right, it was now telling me lies because it didn't recognize the function. I had tested my code in Management Studio and knew that it was definitely valid so I assumed that SSDT was not fully compliant with the latest SQL version.

I checked my project properties to ensure that the Target platform was set to SQL Server 2016 and Compatibility level set to 130. I did another build just to make sure but sure enough, the exception persisted:

Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[STRING_SPLIT].[value] or [STRING_SPLIT].[value].

I googled, naturally, and found this: https://connect.microsoft.com/SQLServer/feedback/details/2906200/ssdt-failing-to-compile-if-string-split-is-used

Which to help you out just basically says 'yup, we know it's a problem' and then at the bottom it says they have fixed it in the latest release of SSDT. Phew! I downloaded the latest version for VS 2015 and whaddayaknow, it works!

Later Dudes!