Install SQL Server – Step by Step Guide (Best Practices)

– 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