How to setup a MS SQL server for non-DBA systems administrators – Part 1

It’s a fact that most MS SQL servers running in organizations are not built by DBAs. Small, medium and even larger organizations don’t hire professional DBAs. Even in environments where there are DBAs, it will happen quite often that databases servers were built by sysadmins for various reasons.

Microsoft did a great job as it’s easy to setup a SQL server, probably too easy and it will run “fine” without too much effort… for how long? And it could probably run faster with less pressure on your infrastructure.

This is the first of a series of articles dedicated to all my sysadmins friends who are building MS SQL servers to the best of their knowledge.

Before you start:

Choose SQL version (2012, 2014, 2016, 2017, 20XX?)

  • For me, latest is always the best!
  • However, you need to confirm what version is supported by the product that will use that SQL server.

Choose SQL Edition (Express, Developer, Standard, Enterprise)

You need to compare editions and choose the edition that will match your needs:

Developer: my default choice for all test and dev environments. Now free. Review the definition of non-production environments as there are some caveats – Is User Acceptance Testing Covered Under Developer Edition?.

Standard: my default choice for production unless there are some features that are absolutely necessary in the Enterprise edition. Price difference is huge between these two editions and most of time it’s worth extra effort to be able to stay with Standard.

Good news, in the past, I had to install Enterprise to use AlwaysOn clustering. This feature is now included in 2017 Standard edition. Thank you Microsoft!

Enterprise: when it is absolutely needed. List price of Enterprise is 14 256USD$/core compared to 3 717USD$/core for the Standard edition.

Important consideration for Virtual Machines: If you will be running SQL server in a VM cluster, you need to either have Software Assurance for your product or to license each physical host for SQL… The cheapest way to get around this is to use DRS (if running in VMWare) to lock the machine to a specific host but, you will lose capability of moving the VM to a different host (Ensuring virtual machines stay on a specific ESX/ESXi host).

Confirm required SQL Collation

  • Confirm with your editor or developer if there are some requirements for the collation of the machine you are preparing.
  • You will need this information in the setup.
  • Changing the collation of a server will require downtime.

AD Service accounts & Groups

Service accounts:

You will need service accounts created in your Active Directory at least for these services:

  • SQL Server
  • SQL Server Agent

If you are planning to use other features like Reporting Services, you should get specific service accounts for them too.

You should also be using different service accounts for all SQL instances & servers.

Since Windows 2012, you can also use Group Managed Service Accounts if you prefer (Using Group Managed Service Accounts with SQL Server).

Groups:

As a general practice, I will be using distinct Active Directory groups to delegate server roles to user logins.

In this case, I will use the AD group “g_delegation_sql01_sysadmin” to provide “sysadmin” server role to my admin user account for this specific server (sql01).

Here is a rough version of the security model I’m using:

  • Distinct account for Administrative tasks: “DBENJAMIO”.
  • This account is a member of the “DBA” group.
  • Delegation group for Sysadmin server role: “g_delegation_sql01_sysadmin”.
  • “DBA” group is a member of “g_delegation_sql01_sysadmin” group.

Server considerations

When you build, or ask, for your server, take into consideration following elements:

Disks configuration

I separate OS, programs, sql data files, sql log files, sql tempdb and sql backups on different drives.

N.B. For obvious reasons, on production SQL servers, backups should be sent to a different server.

The size of your drives will vary based on your databases size.

For TempDB, I start with a 70 GB drive at least.

Make sure your drives are formatted with the following blocksizes:

  • OS: 4 Kb
  • Programs: 4 Kb
  • SQL Data: 64 Kb
  • SQL Logs: 64 Kb
  • SQL Backups: 4 Kb

You can verify blocksize configuration by running following command:


Adjust page file

For SQL Server hosts, you don’t need to have a huge (1.5x server memory) page file as we will limit memory usage of MS SQL later. You only need to consider the OS and, if any (but there should not be), other applications running on the server. Use this calculation to determine page file configuration for SQL Server: reserved memory for OS * 1.5. In most cases, it will be 1.5 * 4 * 1024=6144 for the page file.

As a habit, I’m also moving the page file to the “programs” drive:

Antivirus

If antivirus is required on your SQL server, make sure all recommendations are applied to exclude files and processes used by SQL Server: How to choose antivirus software to run on computers that are running SQL Server.

Power mode

In Windows, make sure power plan is set to “High performance” instead of default “Balanced”.

If you have “balanced” power options on your server, you should probably ask yourself (or your windows sysadmin counterpart), if you should configure a GPO to force “High performance” plan for all servers.

If your are installing SQL Server on a physical host, you also need to review BIOS power options to make sure server is configured for maximum performance.

Let’s start the fun part!

Intall prerequisites

.Net 3.5

Since SQL Server 2016, .Net 3.5 is not a requirement anymore and you can skip this step.

  1. Mount your Windows server ISO file, let’s say to z:\ drive.
  2. Execute following powershell command:

Install SQL Server (painfully detailed)

For the purpose of this procedure, I will install SQL Server 2017, Developer edition.

  1. Launch installation:
  2. Select the Edition you want to install (for production it will be Standard or Enterprise) in the drop-down list, then click Next:
  3. Accept license terms, then click Next:
  4. Choose how you want to receive important updates.  I stick with Microsoft recommendation:
  5. The installation will then check for any available updates and for any problem it may encounter during the installation. Press next:

    N.B. I’ll address the firewall warning later in the installation.
  6. Choose the features you need to install.  Don’t select features that are not required.
    Change the target location from C:\ drive to D:\ drive for the 3 folders specified below.
    Then press Next:
  7. If you are planning to install multiple SQL instances on the same server, you can specify an instance name here.  For the purpose of this tutorial, I’ll install a default instance:
  8. Configure service accounts:

    1. Change “Startup Type” for SQL Server Agent from Manual to Automatic.
    2. Change “Account Name” for SQL Server Agent from “NT Service\SQLSERVERAGENT” for the domain service account account that was created for this purpose. Type the password in the “Password” field.
    3. Change “Account Name” for SQL Server Database Engine from “NT Service\MSSQLSERVER” for the domain service account account that was created for this purpose. Type the password in the “Password” field.
    4. Check “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service”.
    5. Move to the “Collation” tab.
  9. Configure Collation:

    • If needed, change the Collation for the one that was provided by the editor or developer of the database that will run on this server.
  10. Database Engine Configuration:
    1. “Server Configuration” tab:
      1. Only if you need SQL Server Authentication, change the authentication mode to “Mixed Mode”.
      2. Specify SQL Server Administrators.
        I usually create distinct delegation groups for each required SQL roles.  In this case, I created “g_delegation_sql01_sysadmin”:
    2. “Data Directories” tab:
      1. Change the path of “Data root directory” to point to your programs drive.
      2. Change the path of “User database directory” to point to your data drive.
      3. Change the path of “User database log directory” to point to your logs drive.
      4. Change the path of “Backup directory” to point to your backups drive.

    3. “TempDB” tab:

      A definitive improvement in 2017 is that you can edit TempDB files preferences during SQL Server installation. In previous versions, you will have to change these settings afterwards.

        1. Number of files.  Start with 8 files.
        2. Initial size (MB). I’m raising this to 8192 MB.
        3. Autogrowth (MB). 0 MB.

      Here is a good cheat sheet from Brent Ozar on How to Configure TempDB for SQL Server.

    4. “FILESTREAM” tab:
      • Unless absolutely necessary, leave this disabled.
  11. Review your configuration, then click Install:
  12. Hopefully, your installation will complete successfully:

SQL Server 2017, Developer edition is now successfully installed on my test machine.  This completes the first part of this series. In the next article, I’ll be addressing general best practices that must be configured on a production machine.



My Podcast Playlist

Podcasts are for me a great source of information and knowledge. While I’m commuting, I’m either on the phone or listening to poscasts about various subjects. I just went through my podcast playlist summer cleanup and, as technology evolves quickly, many of them were abandoned and not updated for years.

Here is the list of what I kept, sorted by subjects:

Databases & Data

  • SQL Server Pain Relief: Office Hours with Brent Ozar Unlimited Brent Ozar Unlimited helps you make SQL Server faster and more reliable. The team discusses SQL Server setup issues, indexes, queries, AlwaysOn, SAN tuning, virtualization, and more”.
  • SuperDataScience Kirill Eremenko is a Data Science coach and lifestyle entrepreneur. The goal of the Super Data Science podcast is to bring you the most inspiring Data Scientists and Analysts from around the World to help you build your successful career in Data Science. Data is growing exponentially and so are salaries of those who work in analytics. This podcast can help you learn how to skyrocket your analytics career. Big Data, visualization, predictive modeling, forecasting, analysis, business processes, statistics, R, Python, SQL programming, tableau, machine learning, hadoop, databases, data science MBAs, and all the analytcis tools and skills that will help you better understand how to crush it in Data Science.
  • voiceofthedba’s podcast A series of episodes that look at databases and the world from a data professional’s viewpoint. Written and recorded by Steve Jones, editor of SQLServerCentral and The Voice of the DBA.

Cloud computing & Containers

  • AWS Podcast Simon Elisha & Jeff Barr discuss various aspects of the Amazon Web Services (AWS) offering. Each podcast include AWS news, tech tips, and interviews with startups, AWS partners, and AWS employees.
  • Azure Friday Join Scott Hanselman every Friday as he engages one-on-one with the engineers who build the services that power Microsoft Azure as they demo capabilities, answer Scott’s questions, and share their insights.
  • Pivotal Podcasts Get all of Pivotal’s podcasts in one place. Covering cloud-native journeys to smart applications and modern development to team culture, listen to stories, conversations, opinions, and insights from leading technologists about the transformative power of software. Read show notes at https://content.pivotal.io/podcasts.
  • The Azure Podcast Cale Teeter, Evan Basalik, Russell Young & Sujit D’Mello work at Microsoft in the cloud and services area. They specialize in building and supporting enterprise apps and services that leverage the cloud.
  • The Cloudcast Aaron Delp and Brian Gracely host this award-winning weekly podcast to discuss the technology and organizational changes that are driving the convergence of today’s Data Centers and Cloud Computing. Topics include Cloud Computing | AWS | Azure | GCP | Serverless | DevOps | AppDev | SaaS | IoT. Guests are industry thought-leaders and leaders of companies changing the IT industry.
  • The Doppler The Doppler podcasts cover all things cloud while focusing on how to prepare the traditional enterprise to look beyond conventional computing. Cloud Technology Partners thought leaders, David Linthicum and Mike Kavis among others, talk about what’s new, what’s working, and have expert guests who provide the advice you need to be successful in the cloud. Read by over 5,000 IT leaders, The Doppler weekly email reports and quarterly print editions answer your critical cloud questions and keep you informed on the cloud trends that matter most. For more information please visit cloudtp.com/doppler read more.

DevOps

  • Arrested DevOps Arrested DevOps is hosted by Matt Stratton, Trevor Hess, and Bridget Kromhout. ADO is the podcast that helps you achieve understanding, develop good practices, and operate your team and organization for maximum DevOps awesomeness.
  • Continuous Discussions Continuous Discussions (#c9d9) is a community video podcast series discussing all-things Agile, DevOps and Continuous Delivery.
  • DevOps Cafe In this interview driven show, John Willis and Damon Edwards take a pragmatic look at the technology, tools, and business developments behind the emerging DevOps movement.
  • DevOps Chat DevOps Chats are “fireside” podcasts featuring DevOps.com & Security Boulevard editor in chief, Alan Shimel and leading luminaries and thought leaders in the DevOps & Security industry. They discuss relevant topics on DevOps, DevSecOps, Agile, Microservices, Containers and more. DevOps & Security Boulevard Chats are published on SoundCloud and iTunes, as well as being featured in posts on DevOps.com / SecurityBoulevard.com including a written transcript.
  • DevOps Radio The newest podcast series covering all things related to software delivery.
  • Software Defined Interviews Deep discussions about technology, enterprise IT, and the like
  • Software Defined Talk Kubernetes, serverless, cloud, DevOps, & coding. Get the lazy WTF each week, mostly.

Hyperconvergence, virtualization & infrastructure

  • Nutanix NEXT Community The Nutanix Next Community Podcast is a weekly podcast. It is an informal and technical look at topics around Nutanix, webscale, IT, and the online IT community. It will serve as a campfire and coffee shop for the extended Nutanix community.

IT Security

  • Brakeing Down Security Podcast A podcast all about the world of Security, Privacy, Compliance, and Regulatory issues that arise in today’s workplace. Co-hosts Bryan Brake, Brian Boettcher, and Amanda Berlin teach concepts that aspiring Information Security Professionals need to know, or refresh the memories of the seasoned veterans.
  • La French Connection Podcast international sur la sécurité et le hacking. Nouvelles et opinions du Québec et de l’Europe!
  • OWASP 247 OWASP 247 is a recorded series of discussions with project leads within OWASP. Each week, we talk about the new projects that have come on board, updates to existing projects and interesting bits of trivia that come across our desk.
  • Paul’s Security Weekly Paul’s Security Weekly is a chance for a bunch of us security nerds to get together and talk shop. The topics vary greatly and the atmosphere is relaxed and very conversational. This is a longer show, typically topping out at 2 hours for those with a long commute. If you’re into listening to a podcast on deep technical topics and lively security-related discussions, this one is for you!
  • Risky Business Risky Business is a weekly information security podcast featuring news and in-depth interviews with industry luminaries. Launched in February 2007, Risky Business is a must-listen digest for information security pros. With a running time of approximately 50-60 minutes, Risky Business is pacy; a security podcast without the waffle.
  • Security Now Steve Gibson, the man who coined the term spyware and created the first anti-spyware program, creator of Spinrite and ShieldsUP, discusses the hot topics in security today with Leo Laporte. Records live every Tuesday at 4:30pm Eastern / 1:30pm Pacific / 20:30 UTC
  • Smashing Security Join computer security industry veterans Graham Cluley and Carole Theriault as they chat about cybercrime, hacking and online privacy. Follow the podcast on Twitter at @SmashinSecurity. New episodes released every Thursday. Bonus “splinter” episodes when we feel like it…
  • The Social-Engineer Podcast The Social-Engineer Podcast is about humans. Understanding how we interact, communicate and relay information can help us protect, mitigate and understand social engineering attacks.

Life, career success & balance

  • Beyond the To Do List Award-nominated podcast featuring secrets from productive people, showing you how to complete good work and balance your life.
  • Career Tools It’s not enough to be good technically and get good results. The more successful you are, the more you need relationships. Career Tools podcasts help you be more effective in both areas. Get the respect and recognition you deserve.
  • HBR IdeaCast A weekly podcast featuring the leading thinkers in business and management from Harvard Business Review.
  • Lead to Win with Michael Hyatt Short-term wins are easy. Sustained achievement is another story. On Lead to Win join New York Times bestselling author Michael Hyatt and discover how current research combines with timeless wisdom for lasting success. From personal productivity to personal development, self-leadership to team leadership, and intentionality to influence, get the insights and tools you need to win at work and succeed at life.
  • Manager Tools There’s a Manager Tools podcast for dealing with every situation you find yourself in. Whether it’s a big deal (like succession planning) or an easy fix (agendas in meetings), we’ll tell you exactly how to proceed, in detail. From hiring to managing your admin and from performance reviews to body odor, there’s a cast for that.

Technology in general

  • a16z The a16z Podcast discusses tech and culture trends, news, and the future – especially as ‘software eats the world’. It features industry experts, business leaders, and other interesting thinkers and voices from around the world. Multiple episodes are released every week.
  • Gartner ThinkCast Get the best insights anytime, anywhere on the critical IT and business topics that impact your organization. The world’s leading experts will help you solve your greatest challenges and reveal what will top your future agenda.

Other

  • Freakonomics Radio Discover the hidden side of everything with Stephen J. Dubner, co-author of the Freakonomics books. Each week, Freakonomics Radio tells you things you always thought you knew (but didn’t) and things you never thought you wanted to know (but do) — from the economics of sleep to how to become great at just about anything. Dubner speaks with Nobel laureates and provocateurs, intellectuals and entrepreneurs, and various other underachievers. Special features include series like “The Secret Life of a C.E.O.” as well as a live game show, “Tell Me Something I Don’t Know.”

And no, I don’t listen to all the new content every week… I just go over the list of all new episodes and listen to those who catch my attention. I also need to meetion that I have been using the application “Pocket Casts” on all my devices (IOS, Android) to download and keep in sync my listening status.



Does Nutanix give up its roots?

Over the last few months I’ve been observing a lot of changes in Nutanix market strategy. While it’s probably for the greater good, I feel forgotten as a customer who chose their hyperconverged solution for technological reasons, but also to align with my employer financial model preferences.

First, I must admit that I’m biased towards Nutanix: I’ve been following them since 2013 and when I got an opportunity, I became a customer in 2016. At this time, we had been migrating our workloads to the cloud for few years and we were struck by a reality check: cloud was becoming expensive and was heavy in the OPEX vs CAPEX balance. Reviewing multiple scenarios, including hyperconverged players, I built a business case to return to traditional datacenters using Nutanix solution. The ROI was less than a year. The project was approved and moved forward. We delivered. Successfully.

We also got other benefits from that decision: we reduced more expenses by decommissioning remaining VMWare servers and going to AHV (which is also our technological preference as it’s KVM under the hood). One of our most important success (for us, IT people): we kept all of the agility we became so fond of by using AWS and Azure for few years.

Deploying Nutanix clusters takes merely few hours of work. As soon as we are done, we are able to instantiate workloads in the same way we would do it in the cloud. No need to think about storage, fiber fabrics, balancing loads, etc. We are also able to integrate our deployment scripts and monitoring solutions with Nutanix APIs. Keeping the environment up to date is a breeze (literally few clicks), especially if we think compared to traditional infrastructure (servers firmware, SANs, controllers, all the individual hard drives firmware craziness, fabrics, etc). Nutanix is a wonderful product.

The only missing part for me at this point was on the networking side: where were my security groups or Azure NSG in that hyperconverged awesomeness? As this was greatly improving segregation and overall security posture, it was lacking in the Nutanix solution. But, as the vendors were saying, it was on the roadmap…

After many acquisitions (PernixData, Calm.io and now other), after going public in September 2016, Nutanix continues to develop and evolve. They have been aggressively developing and adding innovative features like AFS, ABS, ACS and Calm. Sooner this year, they came up with multiple exciting products:

  • Flow: The so eagerly awaited solution for app-centric network migrosegmentation.
  • Beam: Multi-cloud cost optimization & compliance management service.
  • Era: Automated and simplified database management. This will be launched later in 2018.
  • Xi: Bridging the gab between public cloud services and on-premises Nutanix deployments.

To achieve their mission of making infrastructure invisible and elevating IT to focus on applications and services powering their business, Nutanix is moving forward as a software company, not as an IT Infrastructure company. Doing so, and presumably to satisfy their shareholders since they are now under public scrutiny, Nutanix is following their counterpart models: Microsoft, Google and so many more, in the “as a service” path. Most (if not all), of Nutanix recent products (Calm, Flow and others) will be delivered only in a subscription – pay-as-you-go – model.

While it totally makes sense for some services like Beam and Xi, I must admit that I would have preferred other options for Calm and Flow. It’s also difficult to forget vendors speeches not that far ago telling that Nutanix is always evolving their solution and that by buying the “Ultimate” package, I would get all the bells & whistles that will be available in the future. They were even adding that Nutanix was not following its counterparts (read VMWare) by selling their product under many editions with various features and multiple options. It’s clear that things changed in San Jose…

Not only do we have to pay extra money to extend our Nutanix capabilities with Flow and Calm, but we need to do so in OPEX expenses instead of CAPEX expenditure, which is even more disturbing for me. The CAPEX model was one of the main drivers in my business case to move from the cloud to Nutanix. In other words, at my actual company, by delivering new products under an OPEX model, Calm and Flow are not an option.

Don’t take me wrong, it’s great that Nutanix is extending their solutions to multiple hardware vendors and to subscription models: it will allow them to reach many potential clients who could have been blocked by the required CAPEX investment. My concern with this is what will happen to people like me, who work for companies where performance is evaluated based on EBITDA and who decided to leverage Nutanix in-house solution to limit OPEX expenses? Will they keep their CAPEX based model? For how long? What will be the limitations of these perpetual licenses and deployments?

While Nutanix is one of my favorite platforms, I’m having a hard time to figure out ways I could leverage a Nutanix deployment on a subscription basis. One of the few possibilities I may think to justify this is for the companies, who prefers OPEX and who have restrictions and cannot host their data in a public cloud provider datacenter. Going with a Nutanix subscription would allow them to have their in-house cloud. Otherwise, in a company that prefers OPEX and without data location constraints, I would probably architect around a public cloud solution, leaving behind any hardware and datacenter management while probably spending less money.

Overall, this move of Nutanix is really interesting as they are entering an interesting market. There is at least one major player in the same market: Microsoft. Azure Stack solution might be younger as an on-premises cloud solution and it might have a lot of limitations when compared to its public cloud counterpart. However, Azure platform is rich in features and Microsoft roadmap is aggressive, we know that they can deliver. I would literally love to evaluate Azure Stack against Nutanix to see how they compare in terms of features, scalability, stability and performance.

For now, I will evaluate alternatives to Flow like Illumio (maybe cheaper as license seems more flexible and less vendor lock-in) and I will probably continue to use Ansible as a configuration management solution instead of jumping in the Calm bandwagon.



I’m back

After more than two years of being offline, this blog is back.

I originally started it in 2006, when I was a DBA; at this time, I was talking about MS SQL and also about Open Source subjects. As my career was evolving, I moved to IT security and management responsibilities, my subjects of discussion followed the same path.

At the beginning of 2016, seeing that I didn’t posted anything in the last year, a result of some work life balance challenges: a young family at home and so many hours spent at work, I decided to shutdown my old WordPress blog.

I decided to bring this site back online as I’m still in love with technology and wanted to share about the various projects I’m working on everyday:

  • Open Source
  • IT Security
  • Devops
  • Cloud (Office 365, AWS, Azure, etc.)
  • Hyperconvergence
  • Containers
  • Management (maybe) 🙂

I hope you will enjoy to read me.