Few things to cover…

Firstly, thank you for stopping by, taking time to view my blog, read my posts and hopefully take something away from them.

If you do happen to like the posts on here, then please do say so; retweet, Facebook share, LinkedIn, whatever, it would be great to get more of my content out there, and more of you on here!

Also If you have any post requests or tech questions, please send those over too, Twitter is possibly the best for that @jaward916

Secondly, apologies for the lack of posts during February. Over the Christmas break I had some good ideas which I made lots of notes for then came up with the 4 posts during Januray, however the ideas have dried up (already) and family related things have meant less free weekends. The weekday’s are taken up with the job, typically Sundays are when I get “me” time to do some techy stuff for my benfit rather than for customers!

Finally…

Whilst I don’t have a nice full topic to write up for this week (I promise there are some ideas bouncing around in my head) what I do have is a snippet of 2018 so far in my world of tech/code/software etc.

During January I spent many hours getting to grips with a new major release of the software I work with, as I’m a little bit of a nerd, a lot of those hours were spent in my own time, drilling down into things, working scenarios out, deplyoment strategies etc. What I ended up with by last week was a full test scenario, remeniscient of a real world deployment. Effectivley emulating what a customer would have. The really cool thing is this allows me to very quickly test out scenarios, when a customer reports something “not working” I can run it through my servers and give them an answer same day along the lines of (usually) “try this, I think you’ve done X in the wrong place”. This is in no way a bypass to my wonderful colleagues in Support, but more of a way to assist the customer with getting their deployment up and running. I don’t generally delve into the applications, I’m not that kind of consultant. What I do is design deployments and implement them, I get the back end of a system up and running. The latest version included quite a few new technical enhancements, so getting experienced with them is an essential part of me being able to do my job!

February hasseen a few more interesting engagements for me, site visits all over the place (on top of delivering 2 training courses during January), with some more lined up, possibly even abroad.

What I am being asked to do now is anlyse, review, and in some cases redesign or reimplement deployments. Not because what they have was done wrong to start with, far from it, but more to help them become future-proof, employ best practices and become more agile as the world around us is changing, and the software adjusts to match. There’s no wheel reinvention, just a set of new tyres here and a bit of air there.

I write on here a lot about SQL Server as it is the underlying DB server platform for all systems I support. Another area of SQL that has always interested me is SSRS (SQL Server Reporting Services), basically a very smart, sometimes fiddly report generation toolset. What I have been able to do over the last few weeks is take some reports, rip them apart, analyse a few minor but irritating issues and develop solutoins to those problems. The strange thing is that I’ve not been trained in SSRS, or had the change do do anything with it prior ot this. I just saw an issue, delved straight into the SSRS builder and worked it out, for myself. I forgot I had those abillites and it’s been refreshing to remember how good I used to be solving new problems.

I’m thinking some SSRS tips in a post may be some decent content in the future, think I’ll build the scrapbook up on those!

 

That’s it for an update, I’ve also updated the About page on this site to reflect the last 2 years!

 

Tip of the Week 2 – SQL Versions & EOL

Have you ever wondered whether you’re definitely on the latest patch of SQL?

I found a site a few years ago which I use at least once a week, every week.

In SSMS when you view a server’s properties you will see a version number e.g. 11.0.5058.0 which to most of us doesn’t mean a lot. On the face of it you can tell its v11 of the software, and you suspect it’s not the GA release due to the 5058 part, but in reality how does this translate to the year and service pack?

The answer is: http://sqlserverbuilds.blogspot.co.uk/ which provides a nice little table as below.

The best thing is that this site seems to be kept up to date all the time and further down the page you will see all sub-versions/ Cumulative updates and release dates, with hyperlinks to release notes.

I find this info very useful when debugging performance issues, especially with 2008R2/2012 so I can check the customer is on the latest patch of the version they have. Also with Microsoft’s changes to extended life of these products over the last few years, some patches are supported and others aren’t. To check what is/isn’t supported it’s worth browsing (and bookmarking) this link: https://support.microsoft.com/en-us/lifecycle/search/

The Product Lifecycle page at Microsoft is fully searchable for all MS products, and will tell you which patches are supported and which ones may already be out of support. e.g. for SQL Server 2012 you’re fine if on SP4, for SP3 you only have 8 months left, SP2 down is already out of support:

If you ask me we should all be on SQL 2016, but I fully understand the reasons we hold back, whether due to licensing costs, downtime issues, 3rd party support or other issues.

SQL Server Tip of the Week – Domain Migration

I thought I would try a new feature, and hopefully get a schedule of tips I want to share with the world.

In the last week or so the subject of Domain Migration has come up for the millionth time in my career so far (slight exaggeration)

Switching domains is easy, log in on one domain, go to the system settings in Windows and change to the other domain, with a reboot committing the change fully. This is great, but what about the applications?

SQL is possibly the most popular DB platform out there, and up until the last couple of months has been restricted to the MS Windows platform. As a Microsoft product it ties in heavily to the operating system and Active Directory. Typically most DBAs will configure there SQL installation with dual authentication, Windows for binding Windows Accounts and SQL Authentication for local based security. The majority of applications these days will utilise Windows Authentication as this make the management of security policies such as password expiration and removal of users a little simpler, and generally management is from one place, i.e Active Directory. The only issue here is if you’re doing a manual Domain Migration (i.e no ADMT style tools) then as soon as you change the domain the SQL server sits on, the applications will no longer work and the users will not be able to connect. This is where the SA user is god, as it is local to the SQL installation not the domain/Windows element.

My advice for a manual SQL Server domain migration is simple:

  1. Ensure you have dual auth enabled
  2. Ensure you know your SA password (if not, create a new SA style user and note the password you use)
  3. Change SQL to use builtin service accounts (not a domain service account) before the move – can be changed afterwards to domain users under new domain.
  4. Change ownership of all DBs to SA
  5. Change ownership of all Task Agent jobs to SA

These should be done before switching domain!

For steps 4 and 5 I have the following scripts which I have used a few times now, and they do exactly as described, changing the ownership:

Change DBs ownership to sa:

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''

Change Job ownership to sa:

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END

CLOSE My_Cursor

DEALLOCATE My_Cursor

 

One additional step once the migration is done and the server is back up on the new domain, would be to login as sa, and add in the security groups/users from the new domain that need access to the server. I typically add the Domain Admins as SQL sysadmin users. To achieve this for the current domain you can run:

USE [master]
GO
declare @DomainName nvarchar(32)
set @DomainName = (SELECT DEFAULT_DOMAIN()[DomainName])
declare @DomainAdmin nvarchar(64)
set @DomainAdmin = @DomainName + '\Domain Admins'
print @DomainAdmin
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\NETWORK SERVICE', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = @DomainAdmin, @rolename = N'sysadmin'
print @DomainAdmin + ' Has been Added as sysadmin'
GO

Incidentally this was one of my early attempts at writing SQL scripts without googling around! – not this also adds SYSTEM and NETWORK SERVICE (local builtin users) as sysadmins.

Upgrading SQL Express 2005 to SQL Standard 2008

We have been using the Helm Control Panel for some time now, but thought it would be better to upgrade to the full version of SQL Server.

This was possibly a mistake.

First of all you cant seem to just upgrade to SQL Standard 2008 from SQL Express 2005. So here is my way round.

Backup your HELM4DB using Express management studio and copy this backup to somewhere safe!!!!

Next uninstall all SQL Express products from Add remove programs and reboot.

Once rebooted you need to install SQL Server 2008 Standard or enterprise. I will be using Standard.

***Please note if you are using a 64 bit machine with windows 64 bit os you need to run setup as follows or the install will not work!!***

Launch a command prompt and cd to the setup directory and run the following:>

setup.exe /Action=Install /INSTALLSHAREDWOWDIR=xxxxxxxxxxxx

where xxxxx is the folder where you want it to be installed.

The above should now let you install. If you still get an error change the folders to Microsft SQL Server1 which worked for me.

Once the install is complete if you didn’t install SP! you will now need to or the Management Studio will not work!

Once you can load the Management Studio restore the Helm4DB Backup you took and check the configuration in the Helm Config Tool.

All Done!