Tag: Installing

SQL

Enabling IFI on Setup in SQL Server 2016

SQL Server 2016 has added a couple of nice new options to the setup experience. First they added the ability to have multiple tempdb files on install, a nice time saver for later. And now, with CTP 3.0 they have added the ability to enable IFI on install.

What is IFI, and how do I get it?

IFI stands for instant file initialization, and if you are not aware, enabling this allows SQL Server to grow data files almost instantaneously. Without this enabled the data file space has to be claimed and then filled with zeroes, something that is a long and arduous tasks, especially on slower spinning media type storage.

This is only relevant to data files, for security and integrity reasons the log files still need to be zeroed out.

Enabling IFI is actually quite a simple task, you just need to add the SQL service account to the Perform Volume Maintenance Tasks section of the Local Security Policy and then restart the SQL Server service.

What does SQL 2016 do differently?

Prior to SQL Server 2016 (CTP 3.0) you would need to manually add the SQL account to the Perform Volume Maintenance Tasks (PVMT) section of the Local Security Policy (secpol). Now you can have the installer take care of this for you. That really helps with not forgetting to do it later, which can cause some serious performance problems down the road.

Continue reading “Enabling IFI on Setup in SQL Server 2016” →

Installation, SQL

Issues With Slipstreaming Service Pack 2 on to SQL 2008 R2

One of the great things Microsoft introduced a few years ago was the ability to slipstream service packs on to the install media of SQL 2008 and 2008 R2. It was a bit of a painful experience to do, but the results were great as it made deployment of standardized builds a breeze, and when you also added cumulative updates to the mix it became ridiculously easy to ensure every new SQL instance was at the right revision of the product.

Slipstreaming has gone by the wayside with SQL Server 2012. Now, instead of extracting the service pack and doing a bunch of file work you just have to stick the service pack into a folder and add the PCUSOURCE information in your default configuration.ini file.

 

How to slipstream a service pack into SQL 2008 & 2008 R2

The manual process of updating the original source media is not the worst thing in the world, but it’s not intuitive either. Peter Saddow over at Microsoft posted something last year that clearly outline the steps involved to make this happen. You can find the full details on how over at http://blogs.msdn.com/b/petersad/archive/2011/07/13/how-to-slipstream-sql-server-2008-r2-and-a-sql-server-2008-r2-service-pack-1-sp1.aspx

I used those steps for creating slipstreamed versions of SQL 2008 R2 with Service Pack1 and SQL 2008 R2 with Service Pack 2.

 

Testing slipstreamed service pack 2

I’ve been using the slipstream of service pack 1 for quite a while now and have never encountered an issue. When SQL Server 2008 R2 Service Pack 2 came out a little while ago I worked to get it deployed on my preprod and production machines, then decided a couple of weeks ago that it was time to slipstream my installation media so that this would not need to happen for future deployments. We have a lot of QA and test deployments of SQL Server coming up, so I felt it a good use of my time.

I followed Peter’s steps and built myself some new installation media that included service pack 2. Being the good DBA that I am I got a couple of virtual machine spun up so that I could perform some installation testing. I wanted to be sure that all the components were installed at the relevant levels and that my configuration files did not need any tweaking.

The install of just SQL Server 2008 R2 w/SP2 along with replication, full-text search and the client tools went fine. There were no problems with the install and all of the components were at the service pack 2 level when I checked. This being good I moved on to an install that included Reporting Services. This is where I started encountering problems.

 

Installation errors

All of the pre-installation steps went without a hitch, and the components seemed to install without any problems, but then it attempted to start the Reporting Services service, at which point things went sideways.

The install was unable to bring the service online and the installation failed. The SQL Server engine and tools all installed successfully, I was able to access those, but no matter what I could not get Reporting Services to start. I took a while to go through the install logs and found the error:

Parameter 0 : SQL Server 2008 R2@RTM@
Parameter 2 : Microsoft.SqlServer.Configuration.Sco.Service.StartService
Parameter 3 : Microsoft.SqlServer.Configuration.Sco.ScoException@1211@1
Parameter 4 : System.ComponentModel.Win32Exception@-2147467259
Parameter 5 : SqlRSConfigAction_install_Startup
Parameter 7 : ReportServer
Parameter 8 : ReportServer
Final Parameter Values
Parameter 0 : SQL Server 2008 R2@RTM@
Parameter 2 : 0xDC112D1C
Parameter 3 : 0xD3BEBD98@1211@1
Parameter 4 : 0xDC80C325
Parameter 5 : SqlRSConfigAction_install_Startup
Parameter 7 : 0x22C8A7B3
Parameter 8 : 0x22C8A7B3

 

Strangely it appears as though Reporting Services is running at the RTM level, and this causes a problem as everything else is at the service pack 2 revision.

I wondered if I had done something wrong, so I tried to slipstream the service pack again, just in case I missed something. I got the same result.

Then I tried putting it over the top of service pack 1 slipstreamed media, but was met with the same result.

No matter what I tried I could not get it to work. Even worse,  I could not apply service pack 2 to the failed Reporting Services installation in an attempt to bring it online. At this point I decided it must be a bug and gave up.

 

Filing a connect item

After multiple attempts with different media on different servers and under different conditions I was no closer to getting Reporting Services installed. Not being able to figure out a way to get it on there (short of deploying media with service pack 1 and then updating it to service pack 2) I decided to use Microsoft Connect to file it as a bug.

Connect ID 771260 is open for this. I fully expect it not to get fixed, as there are only a couple of years left on the support lifecycle, but it’s important that these things are at least brought to the attention of the product team.

 

What you can do

If you slipstream your SQL installs and have encountered this issue please go and upvote the connect item (and say that you can reproduce it). If you slipstream and have not come across this please let me know.

SQL, Windows

Be Careful Installing Windows Features

Did you know that installing certain Windows features could impact the way that SQL gets installed on your server? Me either.

When performing some installs recently I came across a problem whereby I was not able to change the shared tools location. The option was greyed out. This didn’t make any sense to me. The install was happening on a new server, there had been no previous SQL installs on the machine and so no problems with installed components preventing those kinds of problems.

 

Digging through the filesystem I found a C:Program FilesMicrosoft SQL Server folder. That didn’t make any sense to me at all, I’d not installed anything.

I could not even find anything that might be using this folder when looking through the installed software and yet I couldn’t delete the folder as there were files in use.

After much head scratching I finally found the problem.

 

I had installed the Windows System Resource Manager feature to help manage resources, which is very useful when running more than one instance of SQL on a machine, or if you have multiple software installs and want to keep control of your CPU and memory. This feature uses (and so automatically installs) another feature called the Windows Internal Database which it says is a relational data store.

 

By the looks of things the Windows Internal Database is based around the SQL 2005 engine, this gets installed into C:Program FilesMicrosoft SQL Server.

When performing the new SQL install it went out and identified that SQL components were already installed and forced installation of the shared tools into that same location.

 

After removing the Windows System Resource Manager and Windows Internal Database features I was able to move the shared tools install to another location.

Be very aware of what features and roles might be install on your machines, it might bite you when you least expect it.