Installing Linux (CentOS 7) on a VM.

All these days…I’ve seen my fellow Oracle/MySQL DBAs working on NIX platforms, but I haven’t really got a chance to work Full time on these machines being a SQL Server DBA. Well, with Microsoft’s recent announcement(SQL coming to Linux), I’ve decided and committed to explore Linux in depth.  “MySQL” and “Linux” are my go to technologies for my 2016 Road Map besides VMWare! I know that’s too much…but hey, there’s nothing wrong in setting goals for ourselves 🙂

So, why did I choose CentOS? Well, It’s simple….It’s free to download and a replica of RHEL(Red Hat Enterprise Linux). From my understanding, All the commands which run on CentOS should run in RHEL. Once we get the familiarity with these Operating Systems, working on any Linux/Unix machines should not be difficult. So, if you want to get started to Linux, my recommendation is to begin with CentOS.

Screenshot Tour of installing CentOS 7 on Oracle Virtual box:

Prerequisites:

Download CentOS 7.0(ISO) from their website(Just Bing it or google it).

Download Oracle VirtualBox and install on your machine. (I prefer installing it on a VM rather than creating a dual boot machine with Windows and Linux, YMMV).

Step1:

1

Step 2:

2

Step 3:

3

Step 4:

4

Step 5:

5

Step 6:

6

Step 7:

7

Step 8:

8

Step 9:

9

Step 10:

10

Step 11:

11

In the above step, you can leave it to Minimal Install if you are sure that you absolutely don’t need a GUI. I selected KDE plasma for GUI…(We can load/start OS without GUI if needed, that’s probably what I would do after couple of days)

Step 12:

12

13

Once done, it asked me to reboot the VM. Now am ready 🙂

Note: I haven’t enabled networking and didn’t installed few things. well, if I see something wrong with my setup and if it isn’t working as expected….well, I can destroy my VM and build a new one as needed in just few minutes! That’s the beauty of a Virtual machine.

SQL Server on Linux – Too good to be True!

Microsoft just announced that they are going to support SQL Server on Linux! Yup, that’s true. They also say private preview of SQL Server is already available starting today for customers who are interested. It’s hard to believe me right…Well head over to this blog post and see it for yourself.

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

If what I am seeing is really true, well, for all the guys who says” SQL Server is not secure(We good DBA’s know how to keep it secure)…It only runs on Windows…blah blah” better stop saying that from now on 🙂

For an early look and to sign up for this head over to https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx and sign up.

I really hope this is not some prank! Any ways…it’s time to brush up my Linux Skills….Voilaaa.

Update – SQL Server 2016 very first Release Candidate(RC 0 ) has been announced as well today.  I sense RTM is coming soon 🙂 Happy SQLing!!!

Installing SQL Server 2016 – Is it any different?

Howdy…it’s been a long time I’ve written something related to SQL Server here in my blog. So here I am, I took some time out of my holidays to download and Install  SQL Server 2016 CTP 3.0 in my laptop. Let me share my thoughts on what has been changed/improved with setup experience.

One thing I’ve noticed is during “Features selection” phase, there’s a new feature called “Polybase Query Service for External Data” which requires Oracle JRE to be installed before in hand manually. See below…

3

If you choose Polybase Service without installing JRE, you get below error.

4

You can download JRE from the link shown below:

5

After I Installed JRE, I passed that screen without any errors.

7

So what is Polybase service? At a high level it’s an interface which lets us query Big data from SQL server using SQL Statements.

Moving on, in the section where we typically provide Service accounts, I noticed a wonderful enhancement. See below…

9

This takes care of Instant File Initialization(IFI) of all your data files for your databases 🙂 That’s fantastic….!

Moving on, under DB Engine configuration page, there is a new tab for tempdb. So, now SQL Server setup is smart enough to identify number of CPU cores you’ve got and will create tempdb files accordingly. You can choose initial size and growth settings for tempdb data and log files per your requirements.

10

This is an excellent enhancement IMHO. I’ve seen hundreds of servers where tempdb has been misconfigured in my experience.

Since I’ve selected Ploybase service initially, it has prompted me to configure polybase service per below screenshot.

11

After 10 mins or so….my installation completed successfully and below is what I see from my SSMS:

12

  • SQL Server version: 13.0.700 ( This is SQL Server 2016 CTP 3.0)
  • DB version is 839. But interestingly I noticed master database has been created in 836 version and upgraded to 839! Normally we see this when a database has been upgraded from older version to a newer version.
  • Every database has a new node called “External resources”
  • Three DW databases have been created by SQL Server since I’ve selected this Instance will serve as head node for my polybase configuration.
  • Each database has a new property category called “Query Store”. See below…

13

Query store is one of my favorite features microsoft announced for SQL 2016 besides temporal tables. At a very high level Temporal tables feature allows us to do time travel without any third party tools and Query store is a game changer in performance tuning area for DBAs and Developers. I will come up with blog posts on these topics once I explore these features in detail.

SQL Server 2012 SP2 CU7 – @@Version

Today I was testing CU7 for SQL Server 2012 SP2 and as usual after installing CU on our test server, as a practice I was reviewing the Error Log and interestingly it mentioned CU7 during the startup! Let me show you what am saying…See below screenshot on a server running SQL Server 2012 SP1+CU4. Till date SQL Server error log never revealed CU# in the start up entries…It just says the Service pack level which you’ve got.

1

Now with the CU7 for SQL 2012 SP2, that’s no longer the case 🙂 See below screenshot:

2

Even @@version from T-SQL is revealing the CU installed.

3

SSMS 2016 CTP 2.1 Web Setup – Installation Error.

Recently, Microsoft released SQL Server 2016 CTP for public and today I decided to Just install SQL Server 2016 CTP 2.1 Management Studio on my Windows 7 machine by using “SSMS-Web-Setup.exe”. (I tried to install full blown engine using ISO, but it says my OS is unsupported). Anyways…coming to the issue, I downloaded the file and placed it under my default downloads folder and ran the setup file as an admin. After few minutes, It failed generating huge log file, which is basically referencing to other .msi files present in my downloads folder and complaining that elements are not found. I previously downloaded SQLDOM, few Reporting services related msi’s etc which are located in my downloads folder. So, basically SSMS 2016 web Setup has conflicts with those old files which are present in the same folder.

Fix: Created a new folder and place the “SSMS-Web-Setup.exe” file in it and run the setup. That’s it guys…It worked just fine.

2016_ssms

Actually, there is a connect item as well opened for this issue.

SQL Server 2016 announced :)

Not sure if you guys have got a chance to grab updates from Microsoft Ignite event(Previously called TechEd), So here is a news for you. Microsoft CEO Satya Nadella announced SQL Server 2016 day before yesterday. It’s not yet available to download for public, but they are saying it(public preview) will be available soon 🙂

Looks like they have lot of enhancements in encryption(Always Encrypted…sounds very similar to AlwaysON) area and cloud integration/BI/Polybase areas.

For data sheet and other details please check
http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/default.aspx
and
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

Am all set for getting my hands dirty playing with Public preview release…

 

How to connect to Azure VM SQL Server from SSMS?

In one of my previous posts, I’ve shown how to connect to Azure SQL DB(DaaS) from your local SSMS. In this post, let’s see how you can connect to SQL Server Instance which is running inside your Azure Virtual machine.

I’ve created a Azure VM(sreetestvm.cloudapp.net) with SQL Server 2014. I RDP’d onto the server and see below for how it looks.

1

But at this point I wasn’t able to connect to this SQL Server Instance remotely from my local SSMS on my laptop. So, what should we do to be able to connect to this instance?

1. Create an End Point from your VM dashboard to allow TCP port 1433.

2

2. Enable Mixed mode authentication on your SQL Server Instance and make sure TCP/IP protocol is enabled from SQL Server Config manager.

3. Create a firewall rule(Inbound) to allow port 1433 on your VM.

3

I’ve created a SQL login named “sreeremote” on this instance and voilaaa…now am able to connect to this Instance remotely from my SSMS 🙂

4

Hope this helps…