Changing the SQL Server Collation After Installation for SQL Server 2012 or 2014 without re-installation

So I was asked recently to setup a test environment for one of our CRM applications. So I went ahead and instead of copying the existing Virtual Machine I decided to create a new one from scratch. Installed SQL Server 2012 and the required application files. As I went to install the database onto the SQL server I had realised that I selected the wrong server database collation during installation (I chose SQL_Latin1_General_CI_AI). I’ve known in the past with 2008 / 2008 R2 I needed to uninstall and reinstall to change the Server Collation. This is no longer the case with SQL Server 2012 or 2014.

After going over the documentation of the software, I needed a Server Collation of SQL_Latin1_General_CP1_CI_AI. I’ll make a couple of recommendations with this method, if you already have databases mounted, back them up, then remove them (as this speeds up the process a fair bit if you have DBs with heaps of rows and indexes). Not sure how supported this is by Microsoft, I’m doing this in an environment where even though it is live data, it isn’t critical.

Anyway, moving on. Firstly, shutdown any SQL Server services that are running and then fire up an Administrative command prompt window and go to the SQL Server Binn directory. Once there, we will issue the following command (you can replace the collation with which ever you need)

sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q "SQL_Latin1_General_CP1_CI_AI"

The command above, launches a single user admin mode to connect to the SQL Server -m, enables Trace mode using -t. We specify the instance with -S and set the collation with -q. This will run through the motions and then notify you that the operation has completed and that no user action is required (so close off the window). Start up any SQL services we killed off earlier and the databases will now have the correct Collation (restore if required).

How to remove the Open File Security Warning Prompt during Driver Deployment or User Login when using MDT or SCCM

Security Warning with DriversDuring a deployment of Windows or even after Windows is deployed you see an Open File – Security Warning prompt when a .EXE runs (similar to the one on the right).

This happens because when you download an .EXE, .ZIP, or .CAB Internet Explorer (as well as Firefox and Chrome) saves what is called a Zone Identifier, telling the Operating System the file came from the internet and not a trusted zone.  In certain deployments this can happen several times when a user logs in to load all of the applications into the system tray, some of them being igfxtray.exe, apmsgfwd.exe, apntex.exe, apoint.exe, gfxui.exe, hidfind.exe, hkcmd.exe, igfxpers.exe.

To get around this we need to remove the ZoneIdentifier, we can use a small utility from SysInternals to do it.  Download Streams, copy it to your MDT or SCCM Server and run the following changing the path to a location containing your drivers;

streams.exe -s -d "E:\MDTDeploymentShare\Out-Of-Box Drivers"

Make sure you run that in an Administrative command prompt and accept the license agreement on the first run. Hope that helps.

Getting a machine product name and serial number using WMIC for use with MDT or SCCM

wmic csproduct get name outputQuick post today, I organise my driver folders into manufacturer and model and then use a task sequence to pick machine specific drivers. Getting the exact model name helps and one day to do that is via a WMI query. It’s a command I try to use quite a bit (and tend to forget the command half the time).

wmic csproduct get name

The result should give you the exact name returned by WMI and allow you to use it as a variable in your task sequence. Then, just plug in your model you are targeting into an MDT or SCCM task sequence condition. For an SCCM example;

SELECT * FROM Win32_ComputerSystem WHERE Model LIKE "%OptiPlex 9020%"

Another useful one is;

wmic bios get serialnumber

This one shows the machine serial number and if you’re not using SCCM can be useful for warranty claims.