Abbreviations for SQL Server Components when Installing with PowerShell

I have (finally) been introduced to installing SQL Server with PowerShell and it has made those installations much easier.  Each of my installs; however, may require different SQL Server components so one of the items in my configuration file I change is the Features= section of the INI file.  To help keep all this information in one place, here is a list of items you can choose to install.  Remember, most of these will require other parameters to be set–this is only for the Features= section.  Each feature should be separated by a comma.

SQLInstalFeatureNames

SQL Server Component Install Options

This list specifies the items you can choose to install.

Database engine = SQLENGINE
Replication = REPLICATION
Full-text and semantic extractions for search = FULLTEXT
Data quality services = DQ
Analysis services = AS
Reporting services – native = RS
Reporting services – sharepoint = RS_SHP
Reporting services add-in for sharepoint products = RS_SHPWFE
Data quality client = DQC
SQL Server data tools = BIDS
Client tools connectivity = CONN
Integration services = IS
Client tools backwards compatibility = BC
Client tools SDK = SDK
Documentation components = BOL
Management tools – basic = SSMS
Management tools – advanced = ADV_SSMS
Distributed replay controller = DREPLAY_CTLR
Distributed replay client = DREPLAY_CLT
SQL client connectivity SDK = SNAC_SDK
Master data services = MDS

Happy installing. 🙂

Changes to the Identity Column

A recent upgrade to SQL 2012 reminded me of an issue most will face as they upgrade to the new version.  With the advent of sequences in SQL Server 2012, a change was made to the way identities are issued.  The short story is each time you restart the instances, your identities will increase by 1,000.  This is really only a big deal if

  1. You are close to the limit of an integer (2147483647) or
  2. These values are shown to the end users in your system and they wonder why there was a big spike.

Ahasan Habib has a good write-up of the issue, so I won’t recreate all his steps; however, the good news is there is a workaround for this.  Like always, make sure you test before you implement the change and hopefully you won’t have to answer the question–why is there a big gap in the numbers?  🙂

Unable To Login to SQL Server Error 18456

I installed SQL Server on a machine the other day, I connected from my desktop, finished my normal server setup routine and life went on. Later, I connected to the machine via Remote Desktop, fired up SSMS and got this error message.
LoginFailure18456

With an accompanying error in the log
Login failed for user ‘MyAccount’. [CLIENT: ]
Error: 18456, Severity: 14, State: 11.

Very strange, I thought to myself. Turns out I had added my account to the local administrator’s group and not created a Login on the SQL Server. (It also appears I missed a step on my server checklist, as I normally remove the BuiltinAdministrators group, but who is keeping score?)

I create the Login, and specified the group, and I was good to go.

CREATE LOGIN [DomainAccount] FROM WINDOWS;
EXEC sp_addsrvrolemember [DomainAccount], ‘sysadmin’;

Failure to calculate the default value

I was installing SQL Server SP1 on a one node cluster (long story) and I got this error “There was a failure to calculate the default value of setting PatchResult.” It turns out when I launched SQLServer2012SP1-KB2674319-x64-ENU.exe, it put the temporary folder–you know xcdswer-wersdf-weradf on a drive in the cluster. The patch restarted the sql server service rendering the drive from which the service pack was trying to install unavailable. I copied the temp folder to the local c: drive and I was able to install with no problems.