SQL Server 2016 First Installation is in 2015

Would you believe that first SQL Server 2016 production installation done in 2015. Yes it is. The first customer adopted SQL Server 2016 in production a couple days after it was released CTP2.  CTP2 is the first production-ready build for SQL Server 2016 not just a beta version.

If you have not yet installed SQL Server 2016, it is good time now.

Read more about SQL Server 2016 here

Configuring Files for TempDB database

First article on SQL Server 2016

http://www.sql-server-performance.com/2015/configuring-files-for-tempdb-database/

PolyBase Technology, Instance Feature or Common Feature?

PolyBase is a new feature added to the SQL Server 2016. This feature includes PolyBase technology that enables truly integrated querying across Hadoop non-relational data and SQL Server relational data using standard T-SQL statements.

While installing PolyBase, it is listed under the instance feature.

However, when this feature is selected, in the next screen you will get this error screen.

This means this is not a instance feature.

What is LocalDB?

It is a new version of SQL Server Express dedicated to developers to help them avoid a full installation of other editions of SQL Server.

Benefits.
Small installer. The 32-bit version has 28.2 MB and the 64-bit version has 33.7 .
Simplified. It does do not require configuration or administration.
Run as a low privileged user.
Simple installation.
Offers the same T-SQL language as SQL Server Express. It supports stored procedures,  geometry and geography data types, triggers, views.

Limitations.
It does not support Windows XP, Windows Server 2003, Window 2000.
It does not support WOW. LocalDB doesn't support installing 32-bit version on 64-bit Windows.
Allows only local connections. Only Named Pipes connections.
Only SQL Server 2012 Management Studio (on a computer updated with .NET Framework 4.0.2) can be used to manage LocalDB. SQL Server Management Studio of previous versions of SQL Server cannot be used.
Visual Studio 2010 RTM does not support LocalDB.

Read more at http://www.sqlcoffee.com/SQLServer2012_0004.htm

SQL Server 2012 RTM Cumulative Update 1

Microsoft has announced the release of SQL Server 2012 RTM Cumulative Update 1. SQL Server 2012 RTM Cumulative Update 1 incorporates 92 issues reported by 44 unique customers.

 

Successful release of Cumulative Update 1 is a testament to the commitment of the many teams within the SQL BU, who are finding solutions to provide a better customer experience.   The coordination, determination and execution between all the SQL BU teams was instrumental in continuing the SQL Sustained Engineering team’s long standing track record for on time release of Cumulative Updates.

The associated cumulative KB article has also been published.  Customers are directed to contact CSS to get the CU build or obtain the hotfix package through the new self-service feature by clicking on the “Hotfix Download Available” button found at the top of the KB article.

·        Public KB Article

·        Hotfix Download Location

·        SQL Server 2012 RTM CU1 Fix List

·        CU Website

Sample Database Modifications in SQL Server 2012

From SQL Server 2008 onwards, users have to download sample databases separately. This step was taken as security measure. However, it is still notice that these sample databases still exists in many production instances. So it is recommended to not to install these sample databases to production instances as a security measure.

In this book, most of the samples if not all are referring to these sample databases. So it is advisable to download these sample databases from download.codeplex.com.

There are two sample databases dedicated to SQL Server 2012 RC0. AdventureWorks2008R2_Data.mdf and AdventureWorksDWDenali_Data.mdf are those files. So as in the previous version of SQL Server, you won’t get an installer to install sample database. Instead, you need to attach these mdf files to your SQL Server instance.

You can attach these databases only to the SQL Server 2012 RC0. There is no schema nor data changes are made to the AdventureWorks2008R2 with compared to previous sample database. Only change is the removal of the FILESTREAM property from the Product.Document table. Do not confuse with the naming of this mdf since it is still named as 2008R2.

Though there is neither schema nor data changes in AdventureWorks2008R2, there are few changes on the DW end. New fact table called FactProductInventory introduced to hold inventory fact table. Also, DimDate table was filled to match latest dates.

Service Accounts in SQL Server 2012

It is recommended to have different user accounts for each SQL Server services. For example, SQL Server service needs to have a separate account from SQL Server Agent Service.

However, practically many DBAs use same account for all the services. With SQL Server 2012, by default you will get different accounts while installing as shown in following image.

New Express Edition : LocalDB

New to the Express family, LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Visual Studio, other Database Development tools, or embedded with an application that needs local databases.

Try it here today!

xp_msver

xp_msver is a extended stored procedure, to get some valuable information in SQL Server.

EXEC master.dbo.xp_msver

Will return,

Most important parameters that I can think of is, ProductVersion (not a big deal since you can get this from ServerProperty function), ProcessorCount, Physical Memory.

Next question is, why you would need this?

Let’s say, you need to set the Maximum Degree of the Parallelism (MAXDOP) as a function of number of Processor count. For example, some DBAs want to have MAXDOP to, ProcessorCount – 2.

 

-

Similarly you can set the memory allocation as well.

Download relevant script from here

SQL Server 2008 R2 Installation Issue

I was not able to install SQL Server 2008 R2 for a while. I tried this in various environments like Virtual PC (VPC), VMWare  with Windows 2008 with and without service packs, but was failing.

While installing  it gave me a error saying there is some issue with this package and it continued to installed.  At the end of installation, it is saying all the services except analysis service failed to installed. That is, it has failed to installed Database services, Replication and Reporting services and it will successfully installed all the client components.

All the while, I was running the ISO file (en_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522665.iso)which I downloaded from MSDN.

At last I extracted the ISO file into a folder and executed the setup.exe from there. Guess what, it worked.