Cannot change the SQL Server Tempdb log file size during the installation

Every SQL Server database has two type of files added; Data file and Log file and same goes for system databases as well. You can change the size of User-Defined Databases when they are created but not when System Databases are getting created by the Installation. However, with SQL Server 2016, we have a new page in the installation wizard that allows you to make some changes to the Tempdb.

As you see, you can change the number of files required for the tempdb, initial size of them and the increment for Auto Growth. Although file size can be changed as you want, it does not allow to change the Log Initial Size beyond 1,024 MB. If you set a larger value, it automatically sets to 1,024MB.

This does not mean that you can set a larger value to tempdb log file. Once the installation is done, you can change it later with the size you need. I assume that the reason for not allowing to set the size larger than 1,024MB during the installation is for saving the overall time for the installation.

SQL Server Brain Basher of the Week #044 - Reporting Services

Let's talk about something related to Reporting Services. You are asked to install and configure Reporting Services in one of the servers given. It is a new server, no instances of SQL Server has been installed. You are asked not to configure anything manually, install it with default configurations. You start the installation and select Reporting Services - Native in Feature Selection page and continue the wizard. You want to make sure that wizard configure your reporting services but you notice that the option Install and configure is disable.

What is the reason for this?

If you ask wizard to install files and configure reporting services for you, it needs to create two database for you. Now the issue is, it cannot find SQL Server database engine in the same instance as you are installing a new instance and you have not selected Database Engine Services in Feature Selection page. Therefor wizard cannot create database required for configuring Reporting Services. If you have already installed SQL Server with database engine services and you are updating the same instance by adding Reporting Services, or if you have selected Database engine services in the Feature Selection page, then you will see this option is enable and you can continue with the wizard. 

Installing SQL Server 2016 RC1 - Sharing issues

Just sharing my experiences with installing SQL Server 2016 RC1;

If you are planning to install SQL Server 2016 with Polybase support, make sure you have installed Oracle JRE 7 or highest, otherwise you will be facing this issue;

It can be downloaded at: http://www.oracle.com/technetwork/java/javase/downloads/index.html

In addition to that, you might get time-out  issues during installation, in my case, though it occurred several times, retry worked me and installation was successful.

SQL Server 2016 - Now installation recommends the number of files for tempdb

Last year, I wrote a post on number of data files on tempdb, whether we want multiple files, then how many files should be added, what would be the recommendation. Generally, we add 1 file per core or 0.25 file per core as if number of core increases. Here is the link: How to decide the number of data files required for tempdb?

We do not need to do extra work after installation now because SQL Server 2016 installation recommends it now.

As you see, a new tab has been added and it shows number of files need to be added based on number of cores available in the machine.

Installing SQL Server 2016 CTP 2.1 - Step by Step and Issues faced

Microsoft has made SQL Server 2016 CTP 2 available and it can be downloaded and installed for seeing new features. It is available for download at: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Since this is still CTP, you may get issues while installing and working with it, therefore it is always recommended to install this in a separate box. If you do not have a physical box, install it in a VM.

Here are the steps for installing SQL Server 2016 CTP 2.1. I have added only most relevant steps with issues I faced and workaround applied.

As usual, you need some prerequisites. Make sure following are installed with your machine (or VM) before launching installation.

Microsoft .NET Framework 3.5 Service Pack 1

https://www.microsoft.com/en-us/download/confirmation.aspx?id=22

Oracle JRE 7 Update 51 (64-bit)

http://download.cnet.com/Java-Runtime-Environment-JRE-64-Bit/3000-2378_4-75317067.html

Once installed, start with SQL Server installation setup;

1. Once the setup is launched, SQL Server Installation Center appears, select Installation tab and click on New SQL Server stand-alone installation or add features to an existing installation.

2. When you get Product Key page, DO NOT SELECT Specify a free edition. If you select it, installation might get stuck and you will not be able to continue.

Select Enter the product key radio button and click on Next. You do not need a key to continue.

3. Accept license terms and continue with License Terms page.

4. Next is Microsoft Update. It is always better to check for updates.

5. Continue with Install Setup Files.

6. Continue with Install Rules. Make sure that status of everything is passed.

7. Next is Setup Role. Select SQL Server Feature Installation and click Next.

8. Select your required features from Feature Selection. This is what I selected.

9. If all prerequisites are not installed, it will be reported with Feature Rules page. If no issues are reported, click on Next to continue.

10. Since it is a new installation, install it as the default instance. If need, it can be installed as a Named instance too.

11. Next is Server Configuration page that allows you to configure service accounts. Configure them as you want and continue.

12. Make sure Mixed Authentication is enabled with Database Engine Configuration page and if required, change folder path for databases with Data Directories tab. Add users who you need as administrators to the instance.

13. If Analysis Services is selected, Analysis Services Configuration page will appear. Select Server Mode and then add users who need administrative permission on Analysis Services if you have this page.

14. Select the Reporting Services Mode if you have selected Reporting Services as part of the installation.

15. Once everything is done, summary will be displayed, analyze it and continue.

16. Installation Progress page will show you the progress of the installation.

17. Once the installation is completed, final page will be shown with a message.

You can have a look on new things related to SQL Server 2016 with usual window: Management Studio. I have highlighted some new things appeared when opened it, you will find more.

At this moment, no AdventureWorks databases are available for 2016 but older version databases can be used for testing.

SQL Server Installation: Are Shared components getting upgraded with Side-by-side Upgrade?

As you know, some of the components such as Management Studio (SSMS) and Configuration Manager are shared components of SQL Server instances and they are upgraded when an in-place upgrade is performed. However, they are not supposed to get upgraded with Side-by-side upgrade unless a certain criteria is met.

In-place and Side-by-side upgrades
In-place upgrades refers an installation of newer version of SQL Server that replaces an existing instance of SQL Server completely, including shared components. The key advantage with this is lesser requirements for additional hardware resources and the key disadvantage is incapability of rolling back at a failure of an installation. Side-by-side upgrades refers an installation of newer version of SQL Server as a new instance without replacing the older version of SQL Server installed. This is the most safest way of upgrading a system because it reduces the risk associated with upgrades leaving the original system in place and intact. However, there are few disadvantages with this, such as copying user defined databases manually, configuring security and configuring jobs used manually.

Side-by-side upgrades does not suppose to replace shared components of older version installed. However a replacement can be happened if the major build number of old instance and the major build number of the new instance are identical. This condition is rare to be occurred but this has been seen with SQL Server 2008 and 2008 R2. If you perform an installation of SQL Server 2008 R2 (version is 10.50) as a side-by-side upgrade for SQL Server 2008 (version is 10.00), though it is not an in-place upgrade, new installation will replace all shared components of SQL Server 2008 instance.