Monday, 15 January 2018

Renaming a local windows login after SQL Server Computer Name Change

I had an interesting issue last week. I had to rename non-domain (Workgroup) SQL Server Virtual Machine in Azure. The server was running the Database Engine, Reporting Services and Integration Services

I actually run through a blog post I wrote the best of 8 years ago titled Renaming a Standalone Instance of SQL Server and used the sp_dropserver sp_addserver scripts mentioned in that post. It all worked fine.

I also run through this article which looks specifically SSRS -Rename a Report Server Computer  

Again all worked fine. 

Everything was good except in the Security logins folder of the SQL Server instance.  I had logins listed as OldServerName\Windows_login. 

The machine/VM was not on a domain, so the logins were local windows accounts. I wanted them to show the new server name. I looked up how I can rename login a BOL and ALTER LOGIN allowed me to rename the Windows Logins.

I run the following script for each of the Windows Logins that needed renaming with the old server name reference and they were update correctly. 

ALTER LOGIN "OldServerName\Windows_Login"
WITH NAME="NewServerName\Windows_Login"

I thought I'd write a short post, as I spent about 20 minutes working out how to do this...Its not something I have to do very regularly.  

Wednesday, 10 January 2018

SCOM: SQL Instance and Databases exclusion

This  post is by Chris Gibson

I recently wanted to exclude some temporary SQL databases from being discovered by our SCOM environment.  These databases are created during a backup process and are always named _Clone”.  I’d previously excluded SQL Express instances by their name (Maps, Live, MSEE) and it worked a treat.  However, this was done by their full name and not a partial name using a wildcard.

If you take the SQL 2014 discovery for databases, (MSSQL 2014: Discover Databases for a Database Engine), for examples and override it.  You will see “you can use the wildcard * to exclude all databases” in the details pane.  Why this would be included when you could just disable the discovery all together I’m not sure, but it seems almost criminal that you can’t disable discovery of databases or instances (the discoveries have the same options as this one) via a partial name.  Something like “*_Clone” or "%_Clone" which would solve my problem and i’m sure its something other people would find useful?

So, rather than just ranting I have raised a user voice request to get this functionality changed.  Microsoft takes notice of these suggestions and if popular enough and technically possible, they can make there way into the next release of a product (or an MP in this case).  So please take a moment to check out the below link and give it the full 3 votes

Monday, 8 January 2018

Patching your SQL Servers - Meltdown and Spectre

The latest security issue with the name "Meltdown and Spectre" affecting Intel processors  was made public last week. This security issue affects a lot of systems and Operating Systems it is not a Microsoft specific issue. I think I read somewhere that ALL Mac devices are affected. Whilst details are relatively scares at time of writing Microsoft did release a blog post about how to best protect SQL Server…

In the post Microsoft talk about the different scenarios that you might be using to run SQL Server from bare metal through to a public "cloud" offering. I’ll refer you to the post to read the scenarios in detail. 

The table below shows the scenario number and highlights which scenario suggest patching you SQL Servers. This is taken directly from the Microsoft support article above and I suggest you refer to and read the Microsoft article above for any updates to the guidance.  

I wanted to use the table of scenarios in the post to highlight that every scenario bar one suggests patching your SQL Servers in some way. The one that doesn’t is scenario two where it refers to Azure where Microsoft have done this already.

Scenario 1
SQL Server runs on "bare metal" (no virtual machines)
Deploy updated OS and SQL patches after normal pre-production validation testing.
Scenario 2
SQL Server runs on a virtual machine in a public hosting environment.

For Azure: No VM image update necessary (see KB 4073235 for details). For other public hosts: refer to their guidance.
Scenario 3
SQL Server runs on a virtual machine in a private hosting environment.
Apply patch to host OS or isolate SQL Server on dedicated physical hardware.
Refer to Windows OS guidance on whether microcode changes should be enabled
Scenario 4
SQL Server runs on a physical or virtual machine and is not isolated from other application logic running on the same machine or is using extensibility interfaces in SQL Server with untrusted code.
Apply OS patches as described in Scenario 3.
Apply SQL Server patches, when available.
If running with untrusted code on the same machine, enable the microcode changes as described in the Windows OS guidance.

Restrict use of extensibility interfaces to block untrusted code from executing on the machine (see below).
Scenario 5
SQL Server 2017 runs on a Linux OS (independent of whether extensibility interfaces are being used).
Apply Linux OS patches.
Apply Linux SQL Server patches.
Consult with your Linux OS vendor about whether and how to enable the firmware changes

As an aside the performance advisory in the article that is worth is read too, I’ll quote directly form the article 

“ the time of publication, Microsoft has not yet validated SQL Server performance with all microcode patches, nor has it validated performance in all Linux environments. Customers are advised to evaluate the performance of their specific application when applying patches.”
It may affect performance...

From my experience, patches are usually applied in reasonable time frame. Even if they can sometimes be a pain. Whilst most people will plan in patching for things like Patch Tuesdays– not everyone is as quick to upgrade their older systems. The older versions of SQL Server work fine and keep getting through the work. With plenty of other things to be getting on with upgrading the databases server or operating system do fall to the bottom of to do lists.

In the article, if you look at the “Supported Systems Affected” section you can see that is every version from SQL Server 2008 through to SQL Server 2017 on Linux if affected. 

I’m the process of running a survey on twitter asking what the oldest version of  SQL Server people have running in Production. The majority of people who have responded thus far have picked  SQL Server 2000 or SQL Server 2005 as their oldest version. As these versions of SQL Server are no longer supported, patches and fixes for the vulnerability won’t necessarily apply to these. This might be what you need to get business buy-in for upgrading your older versions of SQL Server to later and greater versions – as everyone can see the danger in the vulnerabilities remaining in the system for a long period of time.

If you need any help or advice about what is mentioned in the Microsoft article or if you want help with a SQL Server Upgrade to newer version of SQL Server check out our SQL Server consulting page and feel free to contact us, or book a free consultation we can have chat about your requirement

Wednesday, 3 January 2018

What is the oldest version of SQL Server you are running?

I have a  twitter poll running asking "What is the oldest version of SQL Server you are running?", to use as evidence in a talk I have planned for later in the year. 

I’d like to know what is the oldest version of SQL Server  you have running in a production environment?

If you can head on over to twitter and quickly take the poll that would be much appreciated.

I’ll report  back on the results in a future post and possible talk.

Saturday, 30 December 2017

New Year: 2017 Year in Review

Welcome to and our final post of the year.

SQL Server 2017 - In Review

It’s the end of what has been interesting  and successful year here on both the SQL Server consulting and SQL Server training side of the business.

On the consulting front we have been involved in many projects, possibly too many to mention here, but these are the highlights. We started year involved in a large database migration from SQL Server 2008 to SQL Server 2014. Around 20 instances. This  included designing, architecting and deploying several SQL Server Availability Groups to ensure disaster recovery for our clients critical databases. As part of this  we have also migrated SSIS packages and some reports on SSRS too up to SQL Server 2014.

We have been involved in a large Microsoft Dynamics CRM implementation – we were brought in to look at performance issues that  certain customised components were experiencing and we have ended up not only fixing the performance issue but also helping with, building up and supporting a 4 node availability multi-subnet availability group, disaster recovery planning and general troubleshooting.

For another  client we’ve been involved in a large data centre implementation. Building SQL Server 2016 Availability groups to support infrastructure application like System Center Operations Manager (SCOM) and Virtual Machine Manager (VMM) for Hyper –V

At the tail end of the year we have been migrating  several small scale SQL Server instances to Azure SQL Databases. This has been very interesting project to be involved with and as more and more businesses see the benefit of the cloud offering available by the various cloud suppliers I suspect we’ll be getting involved in more of this  type work.

On the training side of things, we haven't delivered as many courses as years gone by but its still kept us pretty busy:

We have delivered
  • 2  SQL Server Databases Administration courses
  • Several Enterprise Architecture TOGAF certification courses
  • 2 SQL Server querying and programming courses

These have been on the public schedule or delivered onsite with the customer.

Thrown into all this SQL Server 2017 has been released and we have been busy making sure our skills are up to date to work with that going forward.

What does SQL Server 2018 have in store?

Well I can’t predict the future but the signs are good. We have a reasonable amount of work in the pipeline. We have a SQL Server Security hardening assignment with a key client to start the year off, a potential SQL Server 2016 migration for a Dynamics environment and also some general consultancy around performance and general setup of SQL Server for software house client of ours

Training wise we’ll be offering the full Microsoft official Curriculum of SQL Server courses including the newly released

  • Updating your skills to SQL Server 2017  (2 Days)
  • SQL Server on Linux (2 Day Courses)

All of our courses can be delivered on site at your business or on the public schedule. If you are interested. Get in Touch

We’ll also be offering the Microsoft official Curriculum of SQL Server courses on demand so you can take them at a date and time to suit yourself. We’ll also be providing you with some instructor time, giving you the ability to ask questions, get help with the labs, or get help with a specific problem you have back at work...Just as if you were in a real life classroom. You won't be left on your own, unless you want to be. Check out the training page in the new year for more details on this  or  Get in Touch if you want more details now.

If we can help you with a SQL Server issues, or if you would like to discuss SQL Server training. Contact Us for a free consultation.

2018 is likely to be a very exciting year for me both personally and professionally. I’m very excited for it to begin.  

So all that leaves me to do is wish you and you’re a very Happy and prosperous New Year!

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...