Microsoft SQL 2016 Unattended Install

Continuing my 2017 series on unattended Microsoft SQL installs, this post will cover SQL 2016 on Windows Server 2012 R2. If you want the unattended install for SQL 2012, check out my post here or for SQL 2014, go here. I’m using the same drive configuration for SQL 2016, as I did for 2014. These drives are:

C: OS
D: SQL Binaries
K: SQL Databases
L: SQL Logs
T: TempDB
Z: CD-ROM

So let’s get started on getting SQL 2016 installed via an unattended script. New to SQL 2016 is the removal of the .Net Framework 3.5 dependency. However, you must be running SQL 2016 CU2 or later per this KB article to resolve a database mail issue. You can easily install the baseline SQL 2016 via this unattended install, then download and install the latest CU or Service Pack afterwards. I’m assuming you have your various drives mounted, formatted, and ready to go. Next, download the two files below and save them to the root of your D drive.

SQL-2016-base.ini
SQL-2016-base.cmd

1. Open the SQL-2016-base.ini file with your favorite editor and modify all of the paths. They are sprinkled throughout the file, so be sure to check every line. New to SQL 2016 is the ability to customize TempDB files during the installation process. This allows you to follow SQL best practices and optimize the performance of SQL out of the gate. This is a great improvement. As you can see from the snippet below, there are various TempDB parameters you can tweak. All environments are different, so adjust these parameters as necessary.
2017-03-01_13-39-242. Save the ini. If your CD-ROM is not using the Z: drive, open the SQL-2016-base.cmd file and change the path as needed. If you aren’t using a D drive and have these scripts elsewhere, change the path to the ini file.

3. Open a command prompt and run the SQL-2016-base.cmd file with an argument of the group which you want to be added to the SQL studio to administer the instance. Use the format domain\group. If you just want a local group, you can use BuiltIn\Group. Note: This is a change from SQL 2012/2014 where you could use MachineName\Group.

2017-03-01_13-44-124. Sit back and relax, and give it 10 minutes or more. You will see the SQL installer GUI flash through various screens in an automated fashion. If the install was successful, at the end you should see the following status. Setup result 3010 means you need to reboot the VM. Reboot the VM now. If you don’t reboot with the setup code 3010, then the next piece we install will fail.

2017-03-01_13-45-265. Unlike previous versions of SQL, the main installer no longer has the SQL Studio components. So you can’t perform a single unattended install and come away with a working SQL Studio configuration. So, go back to the mounted SQL 2016 ISO and click on Setup.

6. In the left pane click Installation. In the right pane, click on Install SQL Server Management Tools. This will not launch the tools installer, but will rather redirect you to a Microsoft webpage here to download the latest version of the SQL management studio.

2017-03-01_13-48-41

7. After the download has completed, I find it best to launch it the old fashion way and use the GUI. Click through the wizard and wait for the install to complete. If you do want to launch it via the command line, you can use the following switches:

SSMS-Setup-ENU.exe /install /passive /norestart /log log.txt

However, there is no visual indication of what is going on and the command appears to instantly complete. So the only way you know what’s going on is by looking at the log and waiting for the install to complete. Frankly, I’d rather watch a GUI than monitoring a log file.

And there you go! Due to Microsoft changes in the installer, installing SQL 2016 is slightly harder due to the separate SQL Management Tools installer. But, even so, that’s only an extra 5 minutes of work.

Microsoft SQL 2014 Unattended Installation

Continuing my 2017 series on unattended Microsoft SQL installs, this post will cover SQL 2014 on Windows Server 2012 R2. If you want the unattended install for SQL 2012, check out my post here. I’m using the same drive configuration for SQL 2014, as I did for 2012. These drives are:

C: OS
D: SQL Binaries
K: SQL Databases
L: SQL Logs
T: TempDB
Z: CD-ROM

So let’s get started on getting SQL 2014 installed via an unattended script. First, SQL 2014 requires .NetFramework 3.5. Unfortunately, in the basic Windows Server 2012 R2 install, this is an optional feature that is not installed. Frankly I think the easiest way is via the GUI, although you can use PowerShell as well.

  1. Open the Add Roles and Features wizard.
  2. Click through the wizard until you get to the Features section.
  3. Expand .Net Framework 3.5 Features and tick the box next to .NET Framework 3.5. 2017-02-28_14-52-04
  4. Click Next, then enter the path to the .Net Framework 3.5 binaries. At this point, mount your Windows Server 2012 R2 ISO to the VM, then use the path CD-Drive:\sources\sxs2017-02-28_14-53-34
  5. Wait for the installation to complete, and just to be safe, reboot the VM.

At this point .Net Framework 3.5 is installed, and I’m assuming you have your various drives mounted, formatted, and ready to go. Next, download the two files below and save them to the root of your D drive.

SQL-2014-base.ini
SQL-2014-base.cmd

Open the SQL-2014-base.ini file with your favorite editor and modify all of the paths. They are sprinkled throughout the file, so be sure to check every line. Save the ini. If your CD-ROM is not using the Z: drive, open the SQL-2014-base.cmd file and change the path as needed. If you aren’t using a D drive and have these scripts elsewhere, change the path to the ini file.

6. Open a command prompt and run the SQL-2014-base.cmd file with an argument of the group which you want to be added to the SQL studio to administer the instance. Use the format domain\group. If you just want a local group, you can use MachineName\Group.

2017-03-01_12-41-26   7. Sit back and relax, and give it 10 minutes or more. You will see the SQL installer GUI flash through various screens in an automated fashion. If the install was successful, at the end you should see:

2017-02-28_16-02-58

And there you go! If you are installing multiple SQL servers using the same configuration, I highly recommend the unattended method.

Microsoft SQL 2012 Unattended Installation

A few years ago I wrote a blog post detailing an unattended SQL 2012 installation process. That article needs a bit of updating, so I’m creating a fresh post. I will also follow up on this post with SQL 2014 and SQL 2016. This time around I’m using Windows Server 2012 R2, and giving you direct download links to my example files. Customers were telling me that cut/paste from the blog resulted in strange quotes in some languages, which had to be replaced with regular quotes. This unattended script will install a basic SQL 2012 server. If you wish to install additional components, then you can modify the .ini file. Before you rush through the installation, you will need to modify the .ini file which you download. At the minimum, change the paths to your desired locations. In my script I’ve used multiple drives:

C: OS
D: SQL Binaries
K: SQL Databases
L: SQL Logs
T: TempDB
Z: CD-ROM

So let’s get started on getting SQL 2012 installed via an unattended script. First, SQL 2012 requires .NetFramework 3.5. Unfortunately, in the basic Windows Server 2012 R2 install, this is an optional feature that is not installed. Frankly I think the easiest way is via the GUI, although you can use PowerShell as well.

  1. Open the Add Roles and Features wizard.
  2. Click through the wizard until you get to the Features section.
  3. Expand .Net Framework 3.5 Features and tick the box next to .NET Framework 3.5. 2017-02-28_14-52-04
  4. Click Next, then enter the path to the .Net Framework 3.5 binaries. At this point, mount your Windows Server 2012 R2 ISO to the VM, then use the path CD-Drive:\sources\sxs2017-02-28_14-53-34
  5. Wait for the installation to complete, and just to be safe, reboot the VM.

At this point .Net Framework 3.5 is installed, and I’m assuming you have your various drives mounted, formatted, and ready to go. Next, download the two files below and save them to the root of your D drive.

SQL-2012-base.ini
SQL-2012-base.cmd

Open the SQL-2012-base.ini file with your favorite editor and modify all of the paths. They are sprinkled throughout the file, so be sure to check every line. Save the ini. If your CD-ROM is not using the Z: drive, open the SQL-2012-base.cmd file and change the path as needed. If you aren’t using a D drive and have these scripts elsewhere, change the path to the ini file.

6. Open a command prompt and run the SQL-2012-base.cmd file with an argument of the group which you want to be added to the SQL studio to administer the instance. Use the format domain\group. If you just want a local group, you can use MachineName\Group. 2017-02-28_15-26-36   7. Sit back and relax, and give it 10 minutes or more. You will see the SQL installer GUI flash through various screens in an automated fashion. If the install was successful, at the end you should see:

2017-02-28_16-02-58

And there you go! If you are installing multiple SQL servers using the same configuration, I highly recommend the unattended method.

Channel 9 Ignite 2015 Session Downloader

As you know if you’ve been following my blog this week, Ignite 2015 took place in Chicago with hundreds of great sessions. In fact, this year all but one or two sessions were spot on. You can easily download all of the great Channel 9 recordings using the PowerShell script you can download here. The conference just ended today, so it might take a few days before Channel 9 gets all of the recordings up. Happy downloading!

Ignite 2015: Encryption, Certificates and PKI

Session: BRK3130

Note: This was a great beginner level session for those not familiar with encryption, certificates or PKI. If you are in that boat, I would urge you to find the session video and watch the whole presentation. If you are a security professional and already know about these topics, then the content is probably too basic. I didn’t capture all the content below, but just took down some highlights what was covered.

Why am I here? Thanks to the NSA. Thanks to Edward Snowden. SharePoint, Lync, Exchange all  need to be secure.

Shows screens of RDP SSL warnings, and browser SSL warnings.

Are you still using passwords? Phishing and fraud, password fatigue, pass the hash attacks

IoT (Internet of things) is adding new concerns of authentication (connected cars, medical, industrial sensors)

Non-repudiation – Ability to bind a human to a digital document

Privacy – Hot topic over the last 2 years due to NSA and Snowden. Challenges are not new.

Encryption – Encryption at rest, in transit, challenges: weak algorithms

Encryption at rest – Bitlocker, EFS, SQL TDE

Encryption in transit – SSL/TLS, IPsec, Office 365 message encryption

Azure RMS – AD RMS for On-Premises. Protect documents from Birth to end of life. Protection regardless of location.

Speaker goes over symmetrical, asymmetrical encryption, hardware security modules (HSM) technologies such as AES and shows how they work.

What is hashing? Uniquely identify a stream of data. It’s a one way function.IMAG0425

Use the tool IIS Crypto to disable/enable and change the order that ciphers are use. FREE.

Good ideas: Remove RC4, reorder suites, Update to 2012 R2, research ECC vs. RSA

Talks about Certificate Authorities, certificates, and their basic properties. Also discusses path of trust, and where to find certificates in Windows.

CA Lifetime planning: End certs – 2 years, intermediate CA – 4 years, root CA – 8 years. Renew certificates when 50% of their life has expired.

S/MIME – For Email encryption and digital signatures

Ignite 2015: Windows Hello

Session: BRK2324

  • Shared secrets are easily breached
  • Passwords are easily replayed and phished
  • See previous “Microsoft Passport” session I blogged about for more info
  • Security without convenience is dead in the water
  • Keys are ideally generated in hardware TPM, software as last resort
  • Single unlock gesture provides access to multiple credentials
  • Browser support via JS/Webcrypto APIs to create and use Passport users

Windows Hello

  • Supports biometric authentication
  • Convenient device logon and strong user authentication
  • Enterprise level security and access to high impact data and resources via Microsoft passport
  • Consistent inbox user enrollment

Biometric Steps

  • Enrollment Steps – Face, iris, and fingerprint share the same design
  • Usage – Authentication
  • Recovery – User can delete enrollment data. Stored strictly on local device.

Enrollment – Find a face, discover landmarks, detect head orientation, build & secure vector based template

Recovery – After 5 failures it falls back to PIN or another auth method. After 32 failures the TPM is locked.

There’s an option to improve face recognition where it will take additional data points

It can also use fingerprints and will use between 21 and 40 points, all stored locally on the device

Only supports a single face mapped to a single account. No multiple faces for a single account.

Authentication vs. Identification

  • Not every biometic modality is created equal
  • False acceptance rate
  • False rejection rate
  • Liveness and anti-spoofing – Can detect dead fingers and high res photos
  • Windows hello demonstrates false rejection rate of 1/100000
  • Windows Hello False rejection rate is 2-4%
  • Windows Hello requires liveness detection and anti-spoofing
  • Microsoft has captured 13K faces for a representative sample

Microsoft Hello Camera can work without visible light. It operates on IR. Speaker demod showing a picture and phone to the camera and it did not work.

Microsoft goal is to make biometics non-susceptible to spoofing, offline attacks, etc.

 

 

Ignite 2015: Benchmarking SQL AlwaysOn

Session: BRK3557: Baselining and Benchmarking AlwaysOn Availability Groups

In this session the speaker went through what SQL AlwaysOn availability groups is, and why the customer wanted to use it. Then he went through how he setup his testing, RAID levels, and listed the SQL perform stats that he monitored during the benchmarking. The speaker used a scripted run of SQLIO to perform his benchmark tests. He covered SQL IO sizes, number of threads, and how to scale up to simulate the customer’s environment.

He went into a long discussion about max threads, and how the type of query affects how many threads are spawned. SQL has a max number of worker threads, so understanding how many threads you are spawning when doing at-scale testing is important. He also tuned the cost threshold for parallelism to control the number of spawned threads.

In  the end, he was successful in performing at-scale benchmarks and the customer’s system was implemented successfully. Be sure to check out the session recording for all of the gory details.

 

 

Ignite 2015: Remotely managing Nano Server

Session: BRK3455

Note: This session had very densely packed slides and lots of demos. So I’ve changed things up and just included screenshots for this write up. If you want to run Nano I encourage you to check out the video recording to see all of the demos.

Voice of the customer: Reboots impact my business; Server images are too big; Infrastructure requires too many resources; Security impact

Demos that Server Manager GUI “just works” against Windows Server Nano 2016

Remotely Managing Nano Server:

IMAG0420IMAG0421

IMAG0422

IMAG0423

 

 

 

 

 

 

 

 

 

Ignite 2015: Stretching failover clusters in WS2016

Session: BRK3487

Note: This session was jam packed with slides, text, and diagrams. The speaker was also flying through the material, so it was impossible to attempt to keep up. The session was very good, and quite technical. So if you deal with clustering in your daily job, check out the session recording for a boatload of good info.

  • Stretch clusters can achieve low RPO and RTO
  • Disaster avoidance is the new trend
  • Considerations when stretching clusters: Networking, storage

Recommendations: Adjust intra-node heartbeat thresholds; understand

Cloud Witness in Windows Server 2016

  • Leverages Azure as arbitration point
  • Quorum configuration achieved without an extra site
  • Writes a single blob per cluster
  • Costs on Azure is extremely low…in terms of pennies
  • Newly recommended quorum option

Storage Considerations

  • Storage replica is a brand new feature in WS2016
  • Block-level, volume-based synchronous & async using SMB 3.1.1
  • Any Windows volume, any fixed disk storage, any storage fabric
  • Baked into Windows..no need for third party storage

Hyper-V and General use file server are the main use cases for the tech preview. Not for SoFS.

Requirements & Recommendations

  • Datacenter edition & Azure stack SKUs only
  • Requires Active Directory (no schema updates, just Kerberos)
  • >1Gb network between servers
  • Disks: Must be GPT not MBR.
  • Free space on logs on NTFS/ReFS volume
  • Disk physical sector sizes must be the same (e.g. can’t mix 512e & 4K)
  • Network latency: 5ms round trip
  • Reality: 30-50Km apart
  • Network bandwidth is based on IO of the app and IOPS
  • Log volumes recommended on Flash (SSD, NVMe, etc.)
  • These are *strong* recommendations
  • Supports running inside a VM

Ignite 2015: VMM Overview & Roadmap

Session: BRK2473

Note: This session was 50% about what’s new in VMM 2012 R2, with 15-20 minutes on what’s new in VMM 2016. My take away is that MS is trying to listen to customers and make the product easier to use. But don’t expect any radical changes in VMM (which I think are needed) …just specific feature updates to keep up with the Hyper-V platform. They didn’t stay very long on the VMM 2016 slide, so I didn’t capture everything. See the session recording if you want the full scoop.

SCVMM 2012

Update Rollup 6 was just released – New functionality added

VMM team is now shipping new features in URs, versus having to wait for an entire new release

Microsoft made a point of including user and automated feedback into the design of VMM, and bug fixes.

New Improvements in UR5/UR6:

  • DHCP extension update
  • New Linux OS versions added
  • Maintenance mode behavior fixed
  • Improved performance over WAN links
  • Quicker VM deletion
  • SQL 2014 support
  • Integrate SAN remote replication with ASR
  • New management of vSphere 5.5
  • Added Azure & AWS connectivity & VM support
  • ..many other on the list

Want to get early drops? http://aka.ms/joford

VMM 2016:

  • Ease of use – workflow for host and storage cluster creation; simplified logical switch creation and deployment; Flexible bare mental provisioning; Improved diff disk managment
  • Security and Infrastruture – Deploy guarded hosts, manage guarded hosts, protect tenant secrets, improved state consistency
  • Expanded fabric management – Storage replication automation using Azure site recovery; Scale-out file server with SAN storage automation; storage QoS policy management.
© 2017 - Sitemap