- What's up, everyone? Welcome to Josh Burns Tech. In today's video, we're
gonna be installing SQL Server 2019 using
a step-by-step process with Microsoft Best Practice
recommendations as well. Coming right up. I hope that each of you are
having a great December, enjoying the holidays with your families, all that great stuff. Listen, if it's your first time here, go ahead and hit the subscribe button. Stay up to date on all the
videos coming to the channel. The main focus is on tech, how-to videos and interviews as well. Let's go ahead and jump into the video. For the first step, we need
to download SQL Server 2019. Simply go to Google, type
in download SQL Server 2019, and then you're gonna click on basically the first link that appears, this first one that says SQL
Server 2019 from Microsoft. Just go ahead and click on that. Now you will be directed to the main SQL Server 2019 page
on Microsoft's website. Right dead center in the
screen, it says download now. This is gonna be SQL Server 2019, which is the Preview edition. Go ahead and click download now. Now you need to choose your
operating system or container. And for this tutorial, we're gonna be installing
SQL Server on Windows, so we're gonna select the
first option which is Windows. Next, click preview SQL
Server 2019 for Windows. Another thing to mention is that this tutorial is gonna work for you regardless if you're
using the evaluation copy, or when SQL Server, the
full version, is out. If you're installing the
Standard Edition, Enterprise, the installation steps
are gonna remain the same. The only thing that's gonna be different is where you're getting
your download files from. Before you can download
the evaluation copy, you need to fill out a simple form. On the form, fill in your
first name, last name, company name if applicable. If not, put something default in here. Company size. Again, just fill something in there. Job title, whatever applies to you. Your email address, phone number. Put something default in here if you don't want to get contacted all the time. And then your country. Once you have everything
filled out on the form, go ahead and click continue. Once you click on continue, the SQL Server 2019
Evaluation Copy Preview is gonna go ahead and download. Once it's finished downloading, navigate to where it's located at, right-click on the executable, and click Run as Administrator. Now we need to select
an installation type, and there's a few
different options for that. With a basic installation, this is only to get SQL
Server up and running fast. It's not gonna be
optimized for performance, so we're not gonna go with that option. And then you have the custom option, which is what we're gonna go with 'cause we're gonna install
SQL Server the way we want to, the way Microsoft recommends
it to be installed for high performance and efficiency. And then you also have an
option to download media. Now, this will let you
download the ISO file that you can save
somewhere on your computer and then you could run it later. Or you could run it
from a different server just by copying it to that machine. Go ahead and click the custom option. This is gonna ask you to specify the SQL Server media download target location. Whatever's in here by default
should be what you want to use so just go ahead and click install. After the installation
process is finished, the SQL Server installation
center will open up. Whenever it opens, go ahead and click on
the installation tab. Click on New SQL Server
Standalone Installation or Add Features to an
Existing Installation. Since we're installing
the evaluation copy, on the product key tab we're
gonna leave the first option which is the evaluation selected. If you were installing,
say, the Standard Edition, Enterprise, or one of the other editions after SQL Server 2019 is
fully released for public, then you would select the second option and then enter your product key. Leave the first option selected for the Evaluation Copy and click next. On the License Terms tab, make sure you select
the checkbox that says I accept the license terms
and privacy statement, then click next. On the Install Roles tab, you're almost always gonna get a warning for Windows Firewall. Just ignore that and go
ahead and click next. Now we need to select all the SQL Server features
that we want to install. I'm gonna go through this process and select some of the
main SQL Server features that I use whenever I install SQL Server. First, we're gonna select
Database Engine Services, which is the core service for SQL Server. Next, I'm gonna select Full-Text and Semantic
Extractions for Search. Now, in my experience, this is
really application dependent. Some applications I've worked with have required this option to be selected. I usually select it
regardless of the installation because I know that in a lot
of cases it's actually required so we're gonna go ahead and select that. Next, we're gonna select the PolyBase Query Service
for External Data feature. This will allow you to
create external tables within SQL Server for data
sources such as Oracle, MongoDB, Azure SQL Database,
Teradata, and many others. This will allow you to
actually query those tables for the external data sources
directly within SQL Server, so we're gonna make sure
we get that one selected. Next, I'm gonna install the Machine Learning Server standalone feature as well as R and Python, just because I plan to use these myself doing some development and testing. Unless you plan to use the
Machine Learning Server, I wouldn't recommend
installing the feature. You can always go back,
use the installation media, and add the feature in
if you choose to do so. I'm gonna go ahead and it,
just because I use it a lot. Like I said, if you don't
plan to use it anytime soon, leave it out for now, you
can always add it later. Make sure you select the Client
Tools Connectivity feature, which includes communication
between clients and servers. Next, I'm gonna select the
Integration Services feature. You don't have to select it, but if you plan to use
integration services, whether that be for ETL
or to create SSIS packages to run within SQL Server
Agent jobs automatically, then make sure that you select it. And for Integration Services, make sure that Scale Out Master and Scale Out Worker are both unchecked. I had those checked by accident. I just went back and unchecked those. Make sure that those are unchecked unless you specifically
need to do something with Scale Out features
for Integration Services. In a production environment
where I'm putting the Database Engine Core
Service on a server, I wouldn't put SSRS on that same machine for best practice
recommendations and efficiency. SSRS needs to be separated out, put on its own server
to run independently. You could combine it on a server where you're doing other type of business intelligence related things, such as if you're gonna
put SSAS on a server. You could put SSRS and SSAS together on the business
intelligence type of server, but I would not put it on the same server as your Database Engine Core Service, especially if you're
installing it for a server that's gonna run an application for your business, your company. Make sure you get those separated out. But for in this instance,
I'm not gonna put it on here. I might come back in later
and add in reporting services, but not at the moment. We're gonna leave the
Instance root directory in the default location on the C drive. I'm actually gonna show you how to move the database files and log
files to their own drive, which would be a best
practice recommendation, but we're gonna leave the
Instance root directory in the default location and move on, so we'll go ahead and click next. Next, on the Instance Configuration tab, if you want to use a named instance, this is where you would
actually specify the name. If not, just leave leave
the default instance ID, which is what I do in most cases, and just go ahead and select next. On the PolyBase Configuration tab, if you chose to install it, I'm gonna select the first option, which is a standalone
PolyBase-enabled instance. You could also select the second option if you wanted this server
to be a compute node. However, I'm gonna select the first option so it would be a
standalone and select next. On the Server Configuration tab, you're gonna specify the account name, password, and startup type for each SQL Server service that
you chose to install. Microsoft Best Practice recommendations for SQL Server recommend that you have a separate Active
Directory service account for each of your SQL Server services. The SQL Server database engine
would have its own service, the SQL Server Reporting Services service would have its own service, SSIS would have its
own service, et cetera. In this case, we're just
gonna use the default accounts because this is not connected to a domain with Active Directory. However, if you were on a domain, the best practice recommendation would be to create Active
Directory service accounts for each service that
you plan on installing. I also recommend setting the SQL Server Agent startup
type to automatic, so that if the server is rebooted or the SQL Server services are restarted, then the SQL Server Agent service will start up automatically. For the Browser, it doesn't
need to start up automatically, but I would set it to manual, so that if you want to start
it you can do it manually. And then make sure you select Grant Perform Volume
Maintenance Task privilege for the SQL Server Database
Engine and then click next. On the Database Engine Configuration tab for the authentication mode, I recommend using the Mixed Mode so that it will allow you to have SQL Server accounts and Windows accounts, or Active Directory
accounts, for instance. I would make sure you get that selected so that you will have the option to create SQL Server local accounts
whenever you need to. And then just go ahead and
enter a password for that. The password you just entered is gonna be for the SQL Server SA account, which is gonna have sysadmin SQL Server instance level access by default. The last option in the
server configuration tab will ask you to specify
SQL Server administrators which will be sysadmin level access. The SA account is already
gonna have that by default, which I mentioned previously. If you want to add your account that you're logged in with currently and you're using Windows in Mixed Mode, go ahead and click the
Add Current User button. This will take a second, but it will, there it goes, it brings
up my Windows user account, so my Windows user account
is also gonna be a sysadmin on the SQL Server instance level. Now, we need to click on
the Data Directories tab, and this is where I
was gonna give you some best practice recommendations
for your SQL Server files. The data root directory,
which typically is on the C drive in the
Program Files directory, which has your SQL Server
data folder, logs, backup. I'm gonna leave that on the C
drive in the default location. The files are gonna be split
out to different drives, which we're gonna do that in a moment. But for the SQL Server
data root directory, I usually leave that on the C drive. For the user database directory, this is gonna be any
database that you restore onto your SQL Server instance, if you create a new database, the dot MDF master data files are gonna be stored in this location. We're gonna go ahead and change that from the C drive to a different drive. Again, for best practice recommendations, you really want to
split out your MDF files to their own drive for
read and write performance. For the user database log directory, where your dot LDF log data files exist whenever you create a new
database or restore one, you can see that that directory location automatically changed from
the default C drive location to the D drive location that I switched the user database MDF file directory to. It's best practice to separate
your MDF and LDF files for read and write I/O performance. In this situation, I'll
only have the D drive rather than the C drive,
the default location, so I'm gonna leave both
of these on the D drive. But for best practice
performance recommendations, if you're configuring a server
specifically for SQL Server, I would add a separate drive
for your log data files to have them stored independently
from your MDF files. For your backup directory, again, it would be best
practice to have a drive dedicated specifically to
SQL Server database backups to reduce I/O that would
occur on your other drives. If you can't do this, at least
give it its own partition. So like, if you're using Remote Host, you have a big, carved
out array of storage, at least set up a partition
specifically dedicated for SQL Server backups. You can see in this instance, I went ahead and changed
the backup directory to the D drive in its own folder. Now we need to move on to TempDB, so go ahead and select TempDB. For any of you that worked with older versions of SQL Server, you know that there was a gap between, I believe, SQL Server 2012 and 2014, where these options
didn't exist for TempDB. You had to come into SQL
Server after the installation and modify these through TSQL. Most of these now are
configured great by default. There's only some of them that
I would recommend changing. The first ones would be the data directories and log directories. I'm gonna leave those in the D drive that we set up a moment ago. However, in a best practice situation, you would want a separate
drive for your TempDB files. Something that's really blazing fast, because TempDB is one
of the most important, if not the most important database, that's gonna be on your
SQL Server instance. Make sure whatever storage
that you're putting your TempDB files on is
extremely, extremely fast. And then lastly, I recommend
changing the initial size and the autogrowth for your TempDB files. The goal is to never have autogrowth kicking in for your TempDB files. It can cause them to grow
up to different sizes, and then SQL Server may take preference over some of the TempDB files over others. And that can cause issues
with things like SGAM. So again, for the
purpose of this tutorial, since I'm not putting my TempDB
files on their own drive, I'm gonna leave the rest of
these options by default. And then on filestream, you're not gonna need
to change anything here. There's probably gonna be a rare case if you ever needed to do
anything with filestream. We're pretty much done here,
we can go ahead and click next. Since I chose to install R and Python, the next is basically asking me to consent to installing Microsoft R Open. I'm going to go ahead and click
accept, and then click next. And also, since I close to install Python, I also have to consent to install Python. I'm gonna go ahead and
click accept for Python, then I'm gonna click next. And now we're ready to install SQL Server. On this screen you can see a summary of basically everything that we've selected throughout this process. You can go through and
double check it all. And when you're ready, go
ahead and click install. Now, this process will take some time. It's gonna go through and install
everything for SQL Server, so just give it some time and we'll come back to
it when it's finished. The installation process
has completed successfully, and now I need to restart
my computer as prompted. I'm gonna go ahead and do that. Now my computer has successfully
rebooted and we're ready to move on to the next steps
after the installation process. Before we move into the configuration portion of this tutorial, first we need to install SQL
Server Management Studio, which is now a separate download, which, again, was just
incorporated recently. And SQL Server 2016, I believe, was the first version
that that started with. We need to click on Install
SQL Server Management Tools back on the SQL Server
Installation Center window. This is gonna automatically open a download SQL Server
Management Studio, SSMS, web page on Microsoft's website. And now we need to scroll down to where the download links are. If we scroll down just briefly, you'll see the first option is for SSMS 18.0 Public Preview 5, which is geared toward
SQL Server 2019 preview. This is the one that we need to download 'cause it's specific to our installation. Whenever the full version of
SQL Server 2019 is released, the key point is to look at the descriptions of the downloads. That's gonna tell you which version that you need to download. We're gonna go ahead
and download SSMS 18.0. Once the download is
complete, go ahead and run it. And then click install when that SSMS installation window opens. After SSMS 18.0 has finished installing, go ahead and search for
it and then open it. When SSMS opens, it will automatically have
the correct server name in. And then for authentication, it's gonna have whatever Windows account that you're currently logged with. So this is the account that I set up that was in the SQL Server administrators if you remember from previously. We're gonna go ahead and click connect. Now we're successfully connected to our SQL Server 2019
instance through SSMS 18.0. Now I'm gonna cover some
Microsoft SQL Server best practices for configuration. First, we're gonna
right-click the instance and navigate down to
properties and select it. Click on the Memory tab. And on the Memory tab, you'll see that the maximum server memory in megabytes is set to an extremely large number. Now, the issue that can arise from this is related to memory contention. SQL Server can starve
your operating system, applications, anything
else running on your server from the memory that they
need to operate efficiently. The best practice
recommendation by Microsoft for SQL Server maximum server memory is to always leave at least
three gig for your OS. And then if you're in
a virtual environment, then you need to leave at least four gig. On my computer I have 32 gig of memory, I already have 12 being used, so I'm gonna leave at least
three to four gig for my OS. On my server, I'll dedicate
16 gig for SQL Server. I'm taking into account the 12 gig on my computer that I'm already consuming, giving SQL Server 16 gig, and then retaining four additional
gig of memory for my OS. I went ahead and changed
the maximum server memory to 16 gig for my computer. Click on the Database Settings tab. And one the Database Settings tab, first select compress backup. This will, by default,
compress all of your SQL Server database backups that you take, which will significantly
reduce the storage space that you need for your database backups. Make sure that's selected. Select backup checksum. When you take SQL Server backups, you want to make sure that you can actually use the backups
that you're taking. That's one of the main reasons that you're taking the back up is in case you need to restore it or you want to move it to a
different server, et cetera. Make sure that you select
backup checksum as well. And then more of an
informational type of thing, you can see that the data, log, and backup directories that we changed during the installation
process have taken effect, and you can see those different
directories listed here. Next, click on the Advanced tab. And on the Advanced tab, we're gonna change a couple of options. First, scroll down to Cost
Threshold for Parallelism. If you're familiar with
parallelism in SQL Server, you know that for a query that uses it it will open multiple threads, allowing the query to run in
parallel to execute faster. By default, Cost Threshold for
Parallelism is set to five. Now, this number is way
too low and definitely not a best practice
recommendation by Microsoft. For any given query, if an estimated subtree
cost for a certain process within that query was greater than five, then that query would run in parallel. That can cause a lot of
problems with CPU usage. One of the main issues that I
end up helping my clients with is related to SQL Server
consuming too much CPU, where I see CPU getting up to
anywhere from 80% or higher. A lot of the times, it's
due to parallelism being incorrectly configured
for that specific server. If Cost Threshold for Parallelism is set to a value as low as five, what will happen is SQL Server will use Max Degree of Parallelism for
queries that don't need it. These are small queries, and they won't benefit from using MAXDOP. As a baseline, I start out with Cost Threshold for Parallelism set to 50 and then modify as needed. Sometimes adjust it a little bit higher or a little bit lower. You just have to see what works
best for your environment. But as a best practice recommendation, I recommend starting at 50. I'm gonna go ahead and
change that right now to 50. Next, we need to modify the
Max Degree of Parallelism, which is by far the most important. Max Degree of Parallelism setting will limit the number of processors that can be used in a
parallel plan execution. With the default setting of zero, SQL Server can use all
of your processor cores if a query needs to run in parallel. The issue with this is
obviously CPU usage. If all your CPU cores are being used when queries are running in parallel, you're going to have high CPU usage. Again, like I said, this
is one of the things that I help a lot of my clients out with. And when I troubleshoot
their environments, a lot of the times I find that the problem is the CXPACKET wait type, which is caused by parallel
processes blocking each other. To avoid the CXPACKET
wait type in SQL Server, where the different processes are waiting to get information back from each other and end up blocking each
other causing high CPU, the way to avoid that is to modify the Max Degree of Parallelism setting to equal or be lesser than the number of CPU cores on your server. For the Microsoft Best
Practice recommendations for Max Degree of Parallelism, start out at whatever number
equals your CPU cores. My computer has a four core CPU, so I'm gonna set Max Degree
of Parallelism equal to four. Now, if you still start experiencing the CXPACKET wait type and high CPU due to Max Degree of Parallelism, adjust the setting as needed. Reduce it down from whatever it's set to. In my case, it's set to four. I would reduce it down to
two, see how that runs. You just need to play around with it and see what's best for your environment. And even tune your queries. Look at the queries that
are running in parallel. See what you can do to
reduce the estimated subtree cost for those different portions of the execution plan
to reduce it as well. Now I go ahead and click okay. Now, we just changed a lot
of configuration settings, so the best thing to do would be to restart the SQL Server services. Either open up your
services for your server, or find SQL Server Configuration Manager, which is what I recommend,
and then open it. And then go through and restart
your SQL Server services. I'm gonna restart the Main
Database Engine service, restart your SQL Server Agent service. And those are the main two. The other services don't
really need to be restarted. You can go through and do that
on your own if you prefer to. Just make sure you restart
the Database Engine service and the SQL Server Agent service. And the last initial configuration change that I would recommend is within Facets. Right-click your instance, scroll down to Facets and select it. Once the Facets open, click the dropdown and scroll down to Surface Area Configuration. This will give you a lot of Surface Area configuration features
that you can enable. The main ones that I would enable initially are Database Mail. If you don't have an Exchange Server set up on your domain, you can use Gmail. There's a long process to go through that. I may even create a YouTube
video on that, eventually. But Database Mail is something I would use so that if you create
automated SQL Server Agent jobs that you can be alerted if one of those were to fail through an email. And the other option that I would recommend enabling is the Remote DAC. The Remote DAC, Dedicated
Administrator Connection, will allow you to connect to SQL Server when it doesn't respond
to regular connections. It gives you a way to still
access your SQL Server environment if you have
something go majorly wrong where you can't connect to it
through SSMS, for instance. Now we have successfully
installed SQL Server 2019, installed SQL Server
Management Studio 18.0, and ran through some best
practice configuration changes by Microsoft for SQL Server. So that's it. I hope this video helped
you get SQL Server installed with Microsoft best practices. Be sure to leave a comment below. Let me know, did this video help you out? And also, let me know
if you had any issues with the installation process. I would love to respond
and help you with those. As always, thank you for watching. Be sure to hit the subscribe button for more videos just like this. Until next time.
**** Section Times in Video ****
Download SQL Server 2019 0:30
Install SQL Server 2019 2:11
Download SSMS 13:45
Install SQL Server Management Studio 14:19
Configure SQL Server 2019 (Best Practices) 14:52
This is a nice intro, but i'd be more interested in a tutorial on automating/scripting the installs, and how to update those installs so SQL server is consistent across system
Is 2019 out yet? I have new servers going up was planning for 2017 since last I looked it was not ga.
Nice video, Josh.
So at my org, our test/dev VMs are typically 2 procs with one core each, and 4 GB of RAM. Yes, those are pathetic specs, but I've lost that battle. Anyhow, would you recommend 50 & 2 for CTFP and MDOP? And what would you use for max memory?
And a minor nit-noid for you; after you set the CTFP to 50, there's a quick cut before you move on to the MDOP and the CTFP is back to 5. Small thing that made me go hmmm...
I liked the vid, thank you!