SQL Server 2012 Extended events for error_reported with some merge replication noise removed

One of the main problems with the XE error_reported event is that it includes informational data that most people would not define as an error. Here is my version, which merges various internet versions I have seen with my own additions and is pretty simple. Many of the items filtered have a severity of 10 but I prefer to filter each error type individually having seen them appear in the trace file and made a conscious decision that the error can be ignored.  It might be a good start if you don't have anything else.

With extended events I always save 10 files of 100 meg as you can copy a 100 meg file over relatively low bandwidth comms if the need arises and 1 gig is not very onerous on storage. The files from this script would take several years to rollover as very few genuine errors are now logged.

As with all scripts use on your own servers at your own discretion. It is pretty low impact unless you have lots of errors in which case you will have more or different noise to my applications or some errors that need fixing! I don't need track_causality switched on with this trace but you might do depending on what you are doing. There are many bespoke replication 'noise' and minor errors filtered out which you might be an important error in your system so check before use.


CREATE EVENT SESSION [NM_Error] ON SERVER 
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([error_number]<>(14108) AND [error_number]<>(20532) AND [error_number]<>(14149) AND [error_number]<>(20556) AND [error_number]<>(20554) AND [error_number]<>(20567) AND [error_number]<>(20568) AND [error_number]<>(3262) AND [error_number]<>(14226) AND [error_number]<>(17177) AND [error_number]<>(14150) AND [error_number]<>(14554) AND [error_number]<>(3197) AND [error_number]<>(3198) AND [error_number]<>(2528) AND [error_number]<>(18264) AND [error_number]<>(3211) AND [error_number]<>(3014) AND [error_number]<>(4035) AND [error_number]<>(5701) AND [error_number]<>(5703) AND [error_number]<>(18265) AND [error_number]<>(14205) AND [error_number]<>(14213) AND [error_number]<>(14214) AND [error_number]<>(14215) AND [error_number]<>(14216) AND [error_number]<>(14549) AND [error_number]<>(14558) AND [error_number]<>(14559) AND [error_number]<>(14560) AND [error_number]<>(14561) AND [error_number]<>(14562) AND [error_number]<>(14563) AND [error_number]<>(14564) AND [error_number]<>(14565) AND [error_number]<>(14566) AND [error_number]<>(14567) AND [error_number]<>(14568) AND [error_number]<>(14569) AND [error_number]<>(14570) AND [error_number]<>(14635) AND [error_number]<>(8153) AND [error_number]<>(14638) AND [error_number]<=(50000))) 
ADD TARGET package0.event_file(SET filename=N'd:\sqlxe\NM_Error',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [NM_Error] ON SERVER STATE=START

Azure backup Start-OBBackup does not reliably emit errors

I noticed that the Windows Azure Online backup command does not always emit an error when the backup does not successfully complete in say a Try / Catch block.

This was most apparent when the job was cancelled/failed at an early stage during the backup.

As such it is easier to look through the output of the backup command and make sure it contains the text "The backup operation completed successfully". The output only contains this string if the backup completes successfully.

eg

$SuccessString = "The backup operation completed successfully"

$OnlineBackupOutput = Get-OBPolicy | Start-OBBackup
$OnlineBackupOutputString = $OnlineBackupOutput | Out-String

Then you can check
if ($OnlineBackupOutputString -match $SuccessString)
# ok
else
error condition...

Windows Azure Backup Setup via Powershell

This a follow on article from

http://maneffa-it.blogspot.co.uk/2012/05/new-features-in-windows-server-2012-for.html

Windows Azure Backup Vault is a great way to get an incremental and compressed off site backup. In my testing of the preview over several months and 100 gb  it 'just worked', and was superior to all the other 3rd party mechanisms I tried.

Here is the PowerShell needed to set up a Windows Azure Backup policy. It is mostly self explanatory, and assumes you have already saved a certificate into the store on the local machine.

You will need to create and upload a service certificate - here is an example to create a certificate, make sure you use a recent version of makecert as some older versions I had lying about did not like the option for certificate length, but failed silently!

makecert.exe -r -pe -n CN=mybackup -ss my -sr localmachine -eku 1.3.6.1.5.5.7.3.2 -len 2048 -e 04/24/2016 mybackup.cer

Now login to the Azure portal and upload this new certificate to your Backup vault. You are now ready to set up the backup.

Firstly set up the server using the certificate and your chosen encryption password.
$cert = Get-OBCertificateListFromLocalStore
$cert

CertificateThumbprint : <thumprint>
IssuedTo              : backup
IssuedBy              : backup
ExpirationDate        : 24/04/2016 00:00:00
IntendedPurpose       : Client Authentication

$item = Get-OBRecoveryService -Certificate $cert[0]
Start-OBRegistration  -RecoveryService $item[0]

ConvertTo-SecureString -String very_long_password_goes_here -AsPlainText -Force | Set-OBMachineSetting

Now set up the backup. here we are setting a backup of the directory d:\azure_backup, which I am robocopying files into during the day and the backup takes place each night at 2 am. The files are relatively small and static so robocopy ensures we only copy updated versions.

$mon = [System.DayOfWeek]::Monday
$tue = [System.DayOfWeek]::Tuesday
$wed = [System.DayOfWeek]::Wednesday
$thu = [System.DayOfWeek]::Thursday
$fri = [System.DayOfWeek]::Friday
$sat = [System.DayOfWeek]::Saturday
$sun = [System.DayOfWeek]::Sunday

$policy = New-OBPolicy
$filespec = New-OBFileSpec -FileSpec d:\azure_backup
$sched = New-OBSchedule -DaysofWeek Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday -TimesofDay 02:00
$ret = New-OBRetentionPolicy -RetentionDays 30
Add-OBFileSpec -Policy $policy -FileSpec $filespec
Set-OBSchedule -policy $policy -schedule $sched
Set-OBRetentionPolicy -policy $policy -retentionpolicy $ret
Set-OBPolicy $policy

This next line sets the machine to throttle the uplink bandwidth to 10 meg during working hours and 50 meg outside working hours. This server does not use a proxy, although you can specify a proxy if that is how your system is set up. Remember most ADSL type connections have very limited uplink bandwidth compared to this fibre connection so if you are using ADSL make sure you do not swamp the uplink bandwidth as this will dramatically affect downloading as well!

Set-OBMachineSetting -WorkDay $mon, $tue, $wed, $thu, $fri, $sat, $sun -StartWorkHour "7:00:00" -EndWorkHour "22:00:00" -WorkHourBandwidth (10000*1024) -NonWorkHourBandwidth (50000*1024)

The first backup will take a long time, but subsequent backups will be much shorter.

Upgraded Server 2008 to 2008R2 Hyper-V backup failure

This setup is Server 2008 UPGRADED to Server 2008R2 + SP1 + updates. Backing up perfectly using WSB admin utility.
Hyper-V was installed and the VMs put on their own volume.
Backup started failing only when this volume was backed up. The error initially showed up as:

VSSAdmin list writers showed Hyper-V writer problem.
The source error was one I had never seen before:
No snapshots to revert were found for virtual machine….
After searching around the answer was to enable Automount on new volumes...

After that the backup started working perfectly again…
OS upgrades often leave these gotchas behind so it can be more time effective in the longer run for a fresh install.

Windows 8 users confused over desktop / start menu

In the past few weeks I have helped users with new Windows 8 laptop. In every case they had used used Windows before and were very confused where the start menu had ‘gone to’. In one case they were accused of ‘doing something’ to the laptop when they showed it to a relative as the relative thought they had broken Windows. The more I see Windows 8 the decision to remove the traditional start menu is just wrong on non touch devices. The uncomfortable jump between the desktop and the new touch oriented start menu does not feel correct. Why the old start menu was removed for Windows 8 RTM is still a mystery for me. The whole thing could be fixed so easily with a few lines of code that presumably and deliberately was never written by MS.
If touch screen then go to new start menu
Otherwise
Go to desktop and add Windows 7 Start Menu
Then allow more experienced users to select either of the above as default behaviour. The default behaviour may need to change if the user uses lots of Windows 8 Apps instead of traditional Apps.
The final icing on the cake was I asked a Windows user of 10 years+ to turn the laptop off, and they couldn't work out how to do it. I showed them that the charms bar, ALT F4 and CTRL-ALT-DEL could be used, but they were still amazed at the lack of usability. So here is a vote to do something about it. On my own laptop I have settled on the Classic Start Menu, whilst on my development Server running Server 2012 I have left the original behaviour to always remind myself how it works out of the box. It is not all bad as pressing the start key and typing the program you want to run still works the same, although I don’t see any advantage over Windows 7 Start Menu. If you use the Surface Touch Screen the new system works well, except non touch devices will be in the majority for some time to come. So here is a vote to change the behaviour of Windows 8 start menu on non touch devices. In a couple of years it will be less of a problem as touch screen devices will be ubiquitous. MS no doubt has already decided against this plan, although if business take up of Windows 8 is low they may be forced to rethink...

Restore of physical Dell Server 2008R2 with Perc H700 to VM fails with stop 7b inaccessible boot device

I have come across this several times in the last year and decided to document the answer myself so I can find it more quickly next time! The best answer is to be found here if you want full details:
http://www.minasi.com/forum/topic.asp?TOPIC_ID=31980

The scenario is:
Take a windows backup of a Dell (and probably other manufacturers) Server 2008R2 with a PERC (or equivalent) disk controller:

eg wbadmin start backup -backuptarget:\\remoteserver\remoteshare -include:c:,d:,e:  -allcritical -vssfull

This creates a VHD(s).

Then go to Hyper-V and restore this backup to a new VM.

Lo and behold when you boot the VM you will get a stop 7b - inaccesible boot device.

Then mess around looking for the answer for half an hour.

The answer is simple.
Mount the VHD
Load the registry of your newly restored server.
Go to:
 HKLM\System\ControlSet001\Services\Intelide and change Start to 0
 HKLM\System\ControlSet001\Services\Pciide and change Start to 3

Restart - it boots perfectly. Why the bare metal restore cannot sort out the above automatically I just cannot fathom. This type of problem has been going on for ages and plagued moving XP/2003 disks to different hardware. The bizarre thing is that on NT4 you could manually 'preload' another driver (I admit the number of drivers was very limited!) and move to different hardware more simply - so much for plug and play! I should point out that using disk2vhd always works perfectly in the above scenario so it must have code to workaround the problem, although it does have the advantage of guaranteeing the hardware that will be presented to to the VM!

I have not tried to see if it is 'fixed' on Server 2012, but it probably isn't.

Beware SQL Developer/Enterprise feature add column on Standard edition

I always add new columns to SQL Server 2012 on large tables out of hours due to the extended table locks required. Factor in merge replication which I use heavily and is usually impossible to recover from schema failure (mitigated potentially by sp_markpendingschemachange) and I usually take a full backup of publisher and all subscribers (which are fixed servers, not mobile clients making it simple) before making schema changes.

A developer said they didn't understand the problem as schema changes on their 'dev' database only took a second. I had to explain that the Production database was on SQL Server 2012 Standard Edition and they were using SQL Server 2012 Developer Edition, and that Developer Edition is the same as Enterprise Edition, including the feature that allows Enterprise Edition to add columns almost instantly for most standard schema changes. here is an extract from SQL Server 2012 BOL.


Adding NOT NULL Columns as an Online Operation

In SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table.

A standard new column line syntax for a merge replicated table might be:
alter table mytable add mycolumn int constraint de_myconstraint default 0 not null
where the new column is defined in 1 DDL operation.

This could easily catch out unwitting users who try out a schema change on their 'dev' database, finds it completes subsecond and then try the same on Standard Edition and wait around for a potentially long time waiting for the schema locks to be dropped.
This Developer Edition vs Standard Edition problem has continued to get worse over the years and the feature difference is now very large.
If only MS would put a trace flag or other feature to allow Developer edition to behave as Standard Edition or other editions things would be much simpler all round.