SQL Server 2019 for Database Administrators (beginners course) | Artemakis Artemiou | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

SQL Server 2019 for Database Administrators (beginners course)

teacher avatar Artemakis Artemiou, Awarded Database Expert, Trainer,Author.

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

8 Lessons (1h 8m)
    • 1. Introduction

    • 2. Downloading and Installing SQL Server 2019

    • 3. Downloading and Installing SQL Server Management Studio 18

    • 4. Enhanced Security Features in SQL Server 2019

    • 5. High Availability Enhancements in SQL Server 2019

    • 6. How to Install SQL Server 2019 on Ubuntu Linux

    • 7. How to Access SQL Server 2019 on Ubuntu Linux using Azure Data Studio

    • 8. What Have you Learned in this Course

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

In this class, you will learn what are the new features and enhancements in SQL Server 2019. This new release of SQL Server, has a large number of interesting new features, and in this class, via a combination of lectures and live demonstrations, you will learn more about them and how you can use them.

This way, you will acquire new skills on SQL Server, that will help you work with this new release more efficiently.

More analytically, in this class you will learn:

  • About SQL Server 2019 and SSMS 18.0
    • SQL Server Evolution
    • Downloading and Installing SQL Server 2019
    • Downloading and Installing SQL Server Management Studio v18.0
  • Performance
    • Performance Enhancements
  • Security and Compliance 
    • Security and Compliance Enhancements
  • High Availability
    • High Availability Enhancements
  • Development
    • Development-Related Enhancements
  • Other Enhancements

After you complete the class, there is a class project which will help you test the skills you've learned in this class.

Meet Your Teacher

Teacher Profile Image

Artemakis Artemiou

Awarded Database Expert, Trainer,Author.


Hi there! I'm Artemakis. I'm a Senior SQL Server and Software Architect, a professional Author and Speaker, and a former Microsoft Data Platform MVP (2009-2018). I have over 15 years of experience in the IT industry in various roles and I'm also a certified SQL Server Engineer.

Moreover, I'm the founder of SQLNetHub and TechHowTos. I'm the creator of the well-known software tools Snippets Generator, DBA Security Advisor and In-Memory OLTP Simulator. I'm also an author of many eBooks on SQL Server.

I currently serve as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).

I'm here at Skillshare, to share my expertise with you, in a simple and understandable way. My classes include ... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: Hi friends, welcome to my online course, sewer Server 2013 for database administrators. My name is has the Manchester meal and I'll be your instructor in this course. A few words about me. I'm a senior sewer server and software architect. I have over 15 years of experience with sewer server Internet. I'd have been a Microsoft data platform MVP for nine years and professional author, blogger and speaker sacrifice, your server engineer and founder of Eshkol net hub. Now, who should attend this course? This course is beneficial for SQL Server database administrators that want to learn useful tips for administering Sequel Server 2018 or so. Technology enthusiasts that want to learn more about sewer Server 2018, database administration will definitely find this course useful. Now, what will you learn in this course? Note that this is not a deep type course. It's just a basic cost to show how you can start working with super server from the database administrator aspect. Therefore, via this course, you will learn how to download and install sewer Server 2018. How to download and install sewer Sever Management Studio. John 18, you will learn about their security enhancements and SQL Server 2018, as well as the high availability in husbands. Moreover, you will learn how to install sewer Server 2013 on Ubuntu Linux, as well as how to access SQL Server 2013 on Ubuntu Linux using Azure Data Studio. Now about the course requirements, you need to have basic sewer server database administration knowledge in prior versions of sewer server, as well as the basic principles. Great, let's begin the course. 2. Downloading and Installing SQL Server 2019: In this lecture, we're going to talk about the requirements for installing seawalls have a 2019, as well as we're going to see a live demonstration of installing seawalls, Sarah 2018 on a windows and the MOV M. During the installation, I will be providing useful tips that have to do with the best practices when installing seawalls server. Now both the system requirements for installing SQL Server 2018, now that they're commanded RAM amount is one gigabyte for the Express editions of SQL Server, as well as four gigabytes for all other additions. The recommended positive speed and type is to gigahertz or higher. And of course, it is recommended to use a 64-bit architecture. Now about the operating systems, as mentioned in the previous lecture, since version 2017, sewer server is cross-platform. So you can choose from a wide range of operating systems for installing SQL Server. And the requirements regarding these operating systems are the below. So you can install sewer Server 2018 either on Windows Server 2016 or later, or Windows ten on Red Hat Enterprise Linux 7.3 or later on SUSE Linux Enterprise server version 12 with SP2, SP3 for our S35 on Ubuntu Linux, 16.04 or 18.04 LPS. And since these requirements are not studied and they change as we move along, which is recommended to check off the below link to stay up to date with their requirements for installing seawalls server. Now it's time to proceed with a live demonstration where we are going to download and install Sequel Server 2013 on Windows. And important note at this point, in case you are downloading any SQL Server Developer Edition. Please note that even though it is a full featured free edition, it is licensed for use only S and development and test database in a non-production environment. In this live demonstration, I will be using SQL Server 2018 Developer Edition for more information and download link for SQL Server 2013, you can visit the below link. So here we are on our test and environment. This is a Windows VM and I'm going to download SQL Server 2018 Developer edition. So I search for sewers are now loads and I end up to the official Sequel Server website via which I can download Suez her tools 1018. So here you can see you can't download the tire version or you can download a free specialization. You can see that the Developer Edition is a full-featured free edition licensed for use as a development and test database in a production environment. And this is exactly what these environment, a test environment for sewer server. So I'm going to download the developer edition. So I click on download and you can see the xi's, the installer. So I'm going to save these in my Downloads folder. So let's append the photon. Now. Let's close the browser. And let's run us administrators. So we ran is taller. And this is a dialogue that allow us to make an optional recasting SQL Server 2013 developed Cartesian. So you have three options. The first option is to perform and basic installation for SQL Server 2018 developers and database with default configuration. Then there is the option for customer Installation type. So this allows you to make different preferences. And a third option is to download the media by clicking on the option to download the ISO file in order to be able to use it at a later time. In this case, I'm going to download the ISO file. So here you select the language selective Yolanda, an ISO file or COP file. I will use an ISO file and I will save it in my downloads folder. So I click on Download. And now the ISO file for sewer Server 2018 Developer Edition is being downloaded. Note that, that is a note here that the sewer server 2018 is also available for Linux. So yeah, you can follow that link and download to assemble for Linux as well. Now the integrity of our ISO of high, it's been checked and we get the message that download was successful. So now let's open the folder where we downloaded the ISO file. Let's close the wizard. And you can see these xi's, our ISO file. So now let's double-click on each in order to mount the ISO. And we come around, IS administered or the set are executable and use that to start with the actual process of installing SQL Server 2013 developed partition on our test Virtual Machine. So this is the installation center of sequel server. We go to the installation top and we select no sequel server, standalone installation or add features to an existing installation. So here you specify the addition to be used. As said earlier, we will be using the developer teaspoon of sugar Server 2018. We click on next. Let's maximize this window. So this is the license terms. So if you agree, you accept and proceed. Some global rules are being checked for distillation. Here you have the option to use Microsoft objects to check for updates, pyre to start the actual installation of SQL Server. So since I have just downloaded sewer server, I won't be using these option. So now my install setup files are getting ready for this Malaysia. Okay. So the next step now is the feature selection. This is the most important step in distillation because here you select what you want to install. So the first thing is to check them to debris engine services. If you remember from previous lecture, it is the main set of research sewer sever. And this is water costs that databases and all the database objects. And this is actually the service onto which you are connecting to using the sequel server client tools. So we'll be using the database engine services. Here you also have other options, for example, to use sewer server application, machine-learning, full-text, search, data quality, et cetera. We won't be using these for this course. So we'll just be installing deliveries, engine services, as well as client tools connectivity. Here you select the location for the instance root directory, for the shared feature directory and shared future directory 32-bits. We leave these as defaults and we continue. Here you have the option if you are going to install and default instance or an instance of SQL server. By installing default resistance, we will be connecting to see what server using the machine name or IP you would choose here named instance. We will specify a name for our sewer server instance, and we would be able to connect using the machine name or IP backslash and they're named instance. We specified, since this is a simple demo and I plan to just installed a single SQL Server instance on this picture machine, I will be using the default instance option. This is the step where you specify the service accounts to be used for your SQL Server services, as well as the correlation. In this case, since this is a test VM, I won't be using any domain accounts or any other Windows counts. So I will be using the default ones that would be created with the installation of sewer server. However, the Best Practices recommend that if you are installing sewer server on domain, it is recommended to use a simple domain user as the service account for the sewer service service. Similarity. You can use another domain user for the SQL Server Agent and so on. Now the correlation specifies the default correlation to be used by our sewer server instance. And that means that what types of characters sewer server would be able to support by default, even though this is an instance level setting, you can specify different correlations for your user databases. Of course, in case you are using the temporary system database of sequel server that is ten dB and you perform character operations. You might get collation coffee errors. So in that case, we will need to use the correlate option. However, note that by default, sewer server supports the Latin characters and with collision, you can extend these supportability. Two other character sets as well. For example here, selecting Greek. Also specify if the correlation would be case sensitive or case insensitive, accent sensitive or insensitive, and so on. So the collision option fooling depends on your needs, on their location and on the type of data you want to store into SQL server. In this case, I will leave the default one, that is escrow Latin one general CPU on case insensitive, accent sensitive. So let's continue. And now this is the step for configuring a database engine. So here you have some tabs. The first tablet server configuration. You specify the authentication mode. For the authentication mode, you have two options. You can either use the Windows authentication mode or mixed-mode. Windows indication mode means that you can only connect to SQL Server using Windows on domain logins. So in that case, you wouldn't be able to create a sequel login that has a username and password connect to SQL server. In order to do that, you need to check mixed mode. However, note that security best practices or comment that you only use Windows indication mode. Of course, this also depends on the type of applications or sequel server instance would support. And that means that your applications should be able to support Windows authentication covered, don't worry, because if you select Windows application and you need to change it afterwards, you can still do that via SQL Server Management Studio or Asia those studio. So in that case, you need to be also carefully in order not to lock yourself out of the SQL Server instance. So you need to specify at least one Sequel Server Administrator. In this case, I will just add my current user. You've got an domain, you cannot, and domain group that has the permission based on their policies to access suicide as a system administrator. Because here you specify the SQL server 20. You can see my machine name is demo VM and my users demo. So now I have specified myself as the Server Administrator for sewer server. The next step is to select the data directories superstar for best practices, recommend that you use different directories for your user database, data and log files and different directories for ten dB in order to achieve parallelism. In that case, because I just have a single disk on my VM, I will be using the sheet drive. Of course, as I mentioned before, this is not recommended for production environments. You need to be able to choose different drives for real data and log files and ten dB. And this brings us to the third tab, which is termed to be configuration. Here you get some recommended vehicles for configuring tend to be based on the hardware onto which you are historic Suez server. So the condition here is to have two files for ten dB and data then shall size to be eight megabytes. The auto grows to be 64 megabytes. So that means that each time ten dB will require more space. It we autographed by 64 megabytes. The initial size for the temp DB log phi is recommended to be eight megabytes, again, autographed by 64 megabytes. And the lock directory is these falter on the C drive as well. So here you can configure temp DB. Max stands for maximum degree of parallelism and instructs sequel server about their level of parallelism is to be used when running queries. This is the recommended value for this setting because it detects too CPU cores on these computer, on this VM. And this is the recommendation. Also you get here, recommendation for memory. So the recommended amount is to have minimum 0 megabytes and maximum almost one gigabyte. Or leave default is to use all the available memory on the machine lasts the file stream option. The tool enabled phi stream for a transact SQL axis, but we won't be using the settings, so we click on next and proceed. Now as you get a summary of their preferences you made for installing seawalls server. So this is the last chance to check your options and make sure that you have made the correct options for installing sewer server. Either there's an issue, you can go back and change. The option you need to change. If everything's okay, you click on install and the installation of SQL Server begins. And why later we can see that the installation of SQL Server has been successfully completed. So let's click on close. And now the last step in this demo is to validate that the sequel server services are up and running, actually the database engine service. So there are two ways of doing that. The best way is to append the Windows services manager. So for example, we start run and execute services MSC. And here you search for SQL Server. And this is the sequence, have a service sewer server, MS has crept server. And you can see that the status is running and the startup time is automatic. There is another set of risks. Who were server agent? It is set to my girl, if you remember, during installation the startup type was set to minor. You could change that from an installation wizard or you counsel change this from here. You can right-click here and start the service, for example. And you can see that this service is also up and running. Now, there is another way of managing sewer server services and SQL Server Configuration Manager. So we call two Microsoft Sequel Server 2018 program group. And here you can see the CBO server 2018 Configuration Manager tool. So we launched the tool. And here you can see the sewer service services are available for administration. So you can see that she was whoever is up and running and sewer server agent is running because we started the service from Windows services manager. Now, if we want to change the startup type, we append the product is oversee whatsoever, agents, service. And we go to the service hub start mode. And here you set it to automatically apply the settings. And you can see that this dot mode change. Now you can manage many things that have to do with civil service services from here. For example, if you append a she was sort of a data retention properties. See that there are many tabs here besides the service tab and their logo on top where you said the identity to be used for starting a service. That is a tab for file stream, there is another term for advanced options, startup parameters, and always on availability groups. Of course, the majority of these settings are advanced settings and we won't be covering these in these cars. So we'll click OK and we are ready to proceed with the next lecture. We will return to our test environment, the late term, in order to continue a series of demos that we will be using these database engine we have just installed. 3. Downloading and Installing SQL Server Management Studio 18: In this lecture, we're going to talk about SQL Server Management Studio, also known as SMS version 18. To this anyway, I went to see the requirements for installing SSRIs veterinary team, as well as we're going to see a live demonstration of installing as SMS on the demo VM we previously installed SQL Server 2018. So let's compare as SMS rationing T virtues as SMS version 17.9. As his time as a veterinary team is a more modern SMS, LME provide more information about that. For example, SMS veterinary teen is based on the new Visual Studio 2017 isolated shell. And that means that it combines features, offices MS and Visual Studio 2017. So we could say that SMS veterinary teen, you see more visual studio like addition of SMS because of what we earlier mentioned. And that is the fact that it uses the Visual Studio 2013 isolated shall. Also it has a small download size, which is about half the size of the previous versions or SMS, and that's about and downloadable installer, 500 megabytes also know that there is isolation from sewer server engine, so there are no shared components between SMS and secure server engine. It provides the microwave Asia feature and this is the Database Migration Assistant and Asia database migration service integrated together in order to provide an even simpler migration experience to the Asia cloud. Moreover, SMS version 18 is fully aware of sewer Server 2018 and it's, you know, features and that means up to date wizards and dialogues that help you take advantage of the new features shift with SQL Server 2018. Now about the system requirements for installing SMS version a t, Note that it only supports Windows. The cross-platform choice for your SQL Server on-premises or in the cloud. That CAGR as krill. It is another free tool provided by Microsoft known as Asia Data Studio, but for SSI special routine, as said earlier, is only supposed to Windows. And to this end it supports Windows 4.1 and Windows ten, as well as Windows Server 2008, R2 or later. And all these operating systems must be the 64-bit edition. Also note that regarding Windows ten S SMS version routine requires Version 1607 or later. Moreover, as SMS version 18 requires the trans framework 4.7.2 or greater. And you can learn more about SMS and its installation requirements on the below link. Now let's proceed with our next demo. We are going to download and install LSMS veterinary team. Now about more information and then download the link of SMS. You can visit the below link. So here we are back to our test environment where we're ready to download and install. She was Sever Management Studio, also known as SMS. So this is the official webpage for SMS. From where you can download the latest version. So these, He's the download link. Let's click on it. And let's save the file to our downloads folder. So the download has started, depending on your internet speed. It might take a while. So then download has been completed. We are ready to open the folder and access the installer of SMS. Let's close our browser now. Ok. So the file is SMS setup EN Yo. Let's right click on it. The roundness Administrator. Let's close the Explorer window. Here. You can change that occasionally, like I will leave the default and we'll just click on install and distillation begins. The installation has just been completed, and now a restart of our Windows VM is required in order to complete the installation. So let's restart our VM. Okay? Our VM was restarted. So now, in order to find SMS will go to all programs and we navigate to Microsoft Sequel Server 2t 18. And if we expand here, you can find Microsoft SQL Server Management Studio 18 right here. In this program. You can also find database engine tuning advisor 18, which is a tool, a calm squeeze as SMS and it can be used for tuning SQL Server Analysis, Services, deployment wizard, routine, and sewers have a profiler 18 which can be used for profiling all the t sequel statements and store procedures and everything that ramps on SQL Server for troubleshooting purposes. So let's start our server management studio. We won't be performing and database operations now, we will start doing that from the next lecture. But let's just connect to the SQL Server database engine just to see how easy T. Now, if you remember, when we install sewer server, I have added myself that these main demo user as sewer server administrator. So I will just login with these user using Windows authentication. So this is the login screen. And via this dialogue, we kind of connect to SQL Server, database engine or Analysis Services, Reporting Services, Integration services, or even Asia SSR is integrational runtime, of course, that assumes that we have installed these services as well. But in this course, we have just installed the database engine of SQL Server, which we will be using for performing different database operations. So this is the server name. If you remember, when we installed sewer server, we installed Td s and default instance. And that means that we can't connect to see what the server by either using the machine's name or IP or even DOD for local host for example. And I'm using Windows authentication. That means that my current Windows user will be used by sewer server for authentication. So let's click on Connect. And you can see that I have just successfully connected to SQL Server. 4. Enhanced Security Features in SQL Server 2019: In this lecture, we're going to talk about the security and compliance enhancements in sewer Server 20181 of the most significant enhancements when it comes to security and compliance, if she were Server 2013 is a capability for always encrypted with secure enclaves. What is this feature? Know that always encrypted was first introduced in SQL Server 2016. It was designed to protect sensitive data. Since with the 2016 religion Sequel Server, many security compliance features were shaped based on the always encrypted capability. Clients encrypt sensitive data inside client applications and never reveal the encryption keys to the database engine. That means, for example, that DBAs can not see the data. This is an excellent separation between duties, entities, foundational level, often data protection. Now always encrypted with secure enclaves. Address previous limitations by allowing computations on plain text data inside a secure enclave, that's a container on the server side. So these enhancements built on top of the always encryption technology, it allows you to better protect your data from unauthorized view or access. Now, let the discovery and classification is another significant feature when it comes to data security in SQL Server. And it allows you to classify columns in your database that contains sensitive information. This is conformed via SQL Server Management Studio. Now that this feature was originally included in LSMS 17.5, it is very easy to use this feature. So you just right-click on database, you go to tasks and then select classified data. And from Gary, you are guided through the dialogue in north to apply the data classification you need for your data. Note that this feature can be used to check your compliance to stay tutorial and regulatory requirements. These feature is supported enhanced in SQL Server 2015. Another great feature, others in Sequel Server 2013 ease their certificate management in SQL Server Configuration Manager. So with this feature, you can now import directly into SQL Server and manage SSL and TLS certificates. So by now you did that using Windows certificate store and you add it the necessary permissions to the necessary service account there in order for sequel server to be able to, to view and use the certificate. But with these enhancement, you can skip these relatively complex procedure. And you can go directly into SQL Server Configuration Manager and import the certificate to be used for encrypting data in motion. We're going to see a live demonstration with these as well. So from within sewer Server Configuration Manager, you can view and validated certificates installed in a SQL Server instance. You can report on certificates close to exploration. And you can also deploy certificates across machines, participating in always-on availability groups. And you do that from the node, halting the primary replica. Moreover, you can't deploy certificates across machines participating in a failover cluster instances and you perform this action from the active node. Note that besides deploying an SSL, TLS certificates for cellular machine with C whatsoever installed. It was a complex procedure to do for failover clusters. So with this capability, you no longer need to perform any complex processes for applying SSL and TLS encryption for your sewer server instances. So no more editing in the registries and no more any other complex tasks. You just use Configuration Manager, who you've heard certificate from there. And you also deploy certificate. The rest of the machines participating to the failover cluster, or they always, whenever a patron groups setup. So this is a screenshot of how you do that in sewer Server Configuration Manager. So you append a tool, you go to protocols for your SQL Server instance. You go to certificate tab, we select Import, and from there you import the certificate. Last but not least, vulnerability assessment. Another great feature in SQL Server Management Studio. It is especially tool embedded in SMS, which can help you track and the remediate potential database vulnerabilities that might be caused by misconfigurations. So this feature was Fest in SMS 17.4. So you perform the vulnerability assessment from within SMS. In order to do that, you right-click on a database, you select tusks, and then a vulnerability assessment. And from there, you archive it for the rest of the process. Note that this feature is supported housing Sequel Server 2013, and we are going to see a live demonstration for these feature as well. This is a screenshot of this tool within SMS. So you can see, I'm right-clicking on database, go to Tasks, CELAC vulnerability assessment, and select scanned for vulnerabilities. Now it's time to pursue another demo. And in this demo, we're going to see the three features earlier presented. So we're going to see in data and discovery and specification, certificate management is super Server Configuration Manager and vulnerability assessment. Let's proceed to the demo. Some buckthorn test environment. And first we're going to see the data discovering classification tool. So this is Management Studio. I'm using the latest version currently available. And I go to the sample DB database, right-click there, go to tasks, and then select and data discovery and classification. Then you have these options you can select to classify your data, generate the report and other features. For example, selling formation protection policy, file export information protection policy, and the reset information protection policy to default. Let's have to generate a report for the sampled in bean database. So you can see our report does not really include anything, just measures the number of columns, number of tables, and so on. And it does not report anything yet because we need to. Classify our data. So let's close this window and let's take a look at the tables. Here you can say have four tables. For example, if we append a TBL test table, you can see it has two columns, id and code. Now let's try to classify our database and more specifically these table by specifying the coal column, halt some sensitive data. So now we go to tasks. They then discovering classification and UCLA classifying data. You can see that we have 0 classified columns. So that means that we haven't yet classified anything in our database. So we click on not classification. And now you select the table and the rest of the required information for classifying your data. So I'm selecting the DIYbio schema table TBL test call code. And here you also need to set two more values, information type and sensitivity level. For information type, you are presented with these types of information. For example, if it is networking, contact info, credentials, can kind of banking, financial name, other etc. For this example, I will just select the financial information type and sensitivity level you can select from this list. So you can see the available sensitivity level values or public general confidential, confidential GDPR, highly confidential and highly confidential GDPR. Also, you have the option to select a not applicable. In this case, I will select confidential. So I'm writing these classification and I'm saving these settings. You can add more classifications that you like. And then if do view the report, you can see that now the report mentions that I have one classified column in one of their five total tables in the database. So you can't go below and you can see that the classified column is the cold column in table TBL test, which has financial formation tie for example. And this has really leveraged, He said, to confidential. So if you have really large databases with hundreds of tables, it is very useful to perform these classification because it will help you keep track what you have in your own database when it comes to sensitive data. Great. Now let's proceed to the second part of the demo where we're going to add data emotional encryption, that is SSL, TLS, keep Sean for our SQL Server instance. But before doing that, let's first confirm that currently the connections to our sewer civil resistance are unencrypted. So a way to check if the connections to our sewer civil resistance are encrypted is to run these T's request creeped. So we select the session ID and encrypt option from the DM Exact connections catalog. So let's execute. You can see that we have five connections through our sewer server instance. And the option is currently set to false. So all these are unencrypted connections. So now let's try to enable encryption for all connections that come to our sewer server instance to the center, we're going to use the SQL Server Configuration Manager. Because as I said earlier, in SQL Server 2018, using its configuration manager, it is feasible to import the certificates that act from there. So I will be loved Configuration Manager. And for the purposes of this demo, I have generated a self-signed certificates. Of course, in order to properly use certificates for encrypting connections to sequel server, you need a certificate to be generated from the certificate authority. But for purposes of simplicity and fun demonstration purposes, I generated the certificates by myself for the current virtual machine. So we seem super Server Configuration Manager. I navigate to protocols for MySQL server IIS task. You can see here it is protocols for MSS Chris server. Since Theses and default instance of sewer server. Now, if I go to properties protocols forums is Chris server. That's by right-clicking on the protocols of these N3, I select the certificate tab. And from here you can see I now have the option to import a certificate. So I click on import these obviously certificate types you can use for importing resistivity as to sequel server, you can see here the option only port for this node is selected because this is a standalone sequel server machine. I go to browse and I navigate to SHE temp. So you can see here, this is my certificate because the name of the machine is in them a VM. This is a certificate that generated. And this is important because these input process checks the F q Dn that matches the subject alternative name included in the certificate. So I said ever certificates by double-clicking on it or just clinging or pen. And then I click on Next. Now that the certificate is password protected, so I need to enter the password I used for protecting my justifications. I click on next. I kept a warning that this is a self-signed certificate. But as I said earlier, the certificate just for demonstration purposes. So I will just ignore this warning, but you should never ignore such warning on prophecy were server environments, for example, production environments, or even on non-production environments. So I click on next. And you can see that my STV get wasn't posted. I click on Finish. And also I now need to go to the flux top. And from here I would be forcing and creep Sean soy set this flag to yes. I apply here. Click OK. And now I just need to restart my SQL server instance and also for the changes to take effect. So SQL Server is stopped. Let's start again. Great. And now if we navigate back to SMS, we will just run this query again and use it to check at this time if the connections to our SQL Server instance are encrypted, so they encrypt option normally should be set to true. Grades, we needed to click on execute twice because the connection was interrupted due to their service restart. So now you can see that all connections to our sewer server instance are encrypted. So we managed to enable encryption for data in motion for all connections Tarceva server instance directly from SQL Server Configuration Manager without requiring to use directly the windows certificate store or any registry settings and so on. Now, if we go back to sequence, have a configuration manager, we will be able to see, again protocols, Poverty's certificates. You can see these xi's our certificates. You can click View. And from here, you can see the different formation about the certificate, for example, will go to the tails, go to subject alternative name. You can see that the c is the name of our machine. From here you can check the expiration dates. Contrary to their friendly name. You can clear the certificate, you import another certificates and so on. So you can do all that. The whole certificate management for a sewer server instance directly from Sequel Server Configuration Manager. Great. Now let's proceed to the third part of this live demonstration where we are going to see their vulnerability assessment tool in SQL Server Management Studio. So again, I won't be using supple nimby. So I right-click and go to tasks and you can see we have the option vulnerability assessment. So now I select scanned for vulnerabilities. So this is the location where my report will be saved. Click OK. And now the vulnerability scanning is in progress. You see this is the report I get. So I have 35 checks that past and for the Failed. Now these four checks have to do with transparent data encryption if she's medium-risk, its force as a category in data protection. And they have three other rules rather than pass that have to do with permissions and other set to, for example, public database and so on. So now I will just create an orphan user and the sampled mean database. And let's see if their vulnerability assessment tool finds these as well. So I will navigate to the SQL Server instance, security and went to creator again here. I'm going to name there again, test sample nimby. And I'm going to give the beyond her access to the samples in the database. So now this is okay because I have my login and to have a corresponding users. So the database user tests sampled in B, in the database sapling B, it is currently mapped to the corresponding looking. So if I refresh here, you can see rather tests sampled and be user. It is mapped to the login name, test sample nimby. Now let's create an issue. So the way to do that for this example, I'm going to delete the hurricane, but I won't beat the ten database user. Also, you get here the warning from Management Studio that grading Server logins does not delete the database users associated with our Locke's. And in order to complete this process, tangled leaving the Lockean, you're also advised by she was server to delete the corresponding database user. Of course, in the case you do not need it anymore. And also in the case where you first transfers the ownership of schemas to new users. So I will be clicking or k here, but I won't be deleting the use the funnel database. So what do I have now? Now I have an orphaned user. So we'll go back to the database is security and go through users. Let's refresh. Okay, let's open the properties of the test sampled MB user. And you can see in the general tab that if these SQL user without the Lockean, so this is an orphan user, it will, we're using a container database. That would not be an issue. But since this database is not contained, this is an issue because the VCs are not fun user. So let's run I can't the vulnerability assessment and let's see either Tool finds or from user. So a quote to tasks for an ability assessment scanned for vulnerabilities, cure K here. And you can see that the now we have five rules that pass. And you can see this is the finding for the orphaned user. So you can see that the vulnerability assessment found that there are often uses in this hand-holding being database. So if we see here the additional details, it explains the issue, provides more information on how you can mitigate these risks. So in plain where it's how to drop, the orphaned user can see here it also generates a remedy Asia scripts. So that was a simple example of the vulnerability assessment tool. And she were server management studio. It really helps you to mitigate any risks, any misconfigurations in your SQL Server instance and databases, and also to maintain more secure databases and see what server instances. 5. High Availability Enhancements in SQL Server 2019: In this lecture, we're going to talk about the high availability enhancements in SQL Server 20181 of those enhancements has to do with availability groups. So now that is enhanced database level health and detection for availability and group and databases, as well as you are allowed to have up to five is synchronous replica pairs. That up to SQL Server 2017, you could have up to three synchronous replica pairs. Last but not least, you have the capability of applying secondary replica connection redirection in always-on availability groups. Now recrafting high availability and Cuvier needed note that you can deploy she were server as a shared disk failover cluster instances with incubator needs. This is not new in sewer Server 2018 since it was supported in SQL Server 2017 as well. The new thing here is that this has been inconstancy were Server 2019. And that means that you can have an enhanced instance health check monitoring using the same operator pattern as availability group health checks. Now when it comes to online operations from these version of SQL server, you can have online index built and rebuilt for clustered column store indexes as well. So this is an example where you create a clustered column store index alpha with specifying the width online on as well as you have the ability to have resumable online index create operations. So you can see in this example, I'm creating a non clustered index and are specifying only on and resumable on. And by the time I have this option set on that, these resumable on, I have the ability to pause the index creation and then resume it. These might come in handy when you work with very large indexes and they might be taking long enough to be created. So if these causes, in any case, a sort of blocking, you can pause the index creation, let the operation of sequel server continue and resume the index creation at a later time that might be more suitable. 6. How to Install SQL Server 2019 on Ubuntu Linux: Since you were Server 2013, that is support for installing SQL Server on Linux. Therefore, I decided to add to this new lecture in my course and see what server 2018 watts know where we're going to install sewers. Have a 2018 currently City p3 on Ubuntu Linux. First, let's take a look on the supported Linux installations for SQL Server 2017 or later. We can install sewer server or Red Hat Linux Enterprise edition. We can install it on Linux Enterprise server on Ubuntu Linux. We can, Iran's who was ever on Docker, as well as we can provision a SQL VM in Azure. Installing SQL Server 2013 preview on Linux. It's a relatively easy task because that is comprehensive documentation provided by Microsoft. So we're going to see a live demonstration of installing SQL Server 2013 city PC on Ubuntu Linux version 19.04. Let's pass it through a demo. So here we are on our test environment, the Socratic virtual machine, where the guest OS is when Boolean oxygen 18.04. Okay, the first step is to find the documentation provided by Microsoft in order to follow step by step the process of installing civil service, because on a team, city p3 on these addition and version of Linux. So let's search for the rotation. So we find the official limitation gets started with civil several voodoo. Part of Microsoft dogs is a quick starts and this is the URL. Okay? First, let's take a look on the prerequisites. We need an voodoo Linux distribution with veterans 1604 or later. We are pleased to Gilgamesh from memory. This is unbundle 19.04, so it sooner by Sean, 16.04, I have two gigabytes of memory and lets proceed commands. So the first step is to install the MS SQL server package. So we'll copy the W get command. We open a terminal and we run it. Okay. It turns our password in order to get that Mr. console. And next, we need to register the repository. Now for installing suicide or 2017. We'll run this command. However, since we want to test coursera 201860 P3, So this is a preview version of sewer server. We can use this repository, which is the MS SQL server preview list. So let's copy this and run it. In our happiness. Okay, so we have successfully registered the repository for installing SQL Server 2015 CTP say. Now, the next step is to run these commands in order to update our repository and then install SQL Server. So let's run the application manifest. And now let's start the installation of SQL Server. So now this will download the actual media of SQL Server 2013 city P3. It is around 224 megabytes. But if you have a fast connection to the internet, it is just a matter of a few minutes. Okay, the media was successfully downloaded and now distillation process starts. At this point. I would like to I mentioned that it is of course obvious, but I need to mention this for this type of installation of SQL Server, for this live demonstration, for example, you need an Internet connection. Okay, excellent. Now, in order to complete the setup of SQL Server, we need to run this command. This command is also included in the Quickstart documentation, can see it here, but it is also reported by the installation with that of SQL Server. Okay, now let's clean the screen and run the command. So this allows us to set up the installation of SQL Server. As you can see, now, we are prompted to select an additional server. At this point, I would like to mention that it is critical that you select the proper addition of sewer server, for example, the CCTP. So I'm going to select the evacuation addition, which is free. If it was a really strong factoring version of superscript, for example, you will when installing SQL Server 2017, if you are installing sewer server on a test environments for non-production purposes, you could select, for example, developed partition, which is free or express edition. However, Eve, you select a petri dish of SQL Server, for example, standard enterprise, enterprise core or web. You need to make sure that you have also purchase the proper license through a retail sales channel and you have a product keep went OK. Now, since this is a test environment and we are installing the CTP version of SQL Server 2018. Let's select one, which is immigration. So here we are presented with hyperlinks to the license terms. And if we agree, we need to type. Yes. Otherwise we type No. I agree with Tim. So I type yes. And now I am prompted to enter a civil service system, latrines or passwords. That is actually the essay users passwords. So yeah, make sure that you entered as a curb password. Let's confirm. Here we get the message that this isn't about valuation version of SQL Server and there are 173 days left in the migration period. Now concede that some more features are being enabled for this instance of sewer server, for example, force Flash. And finally we get the message setup has completed successfully and that SQL Server is starting. So the last step in this live demonstration is to confirm that actually yes, SQL Server service is up and running. In order to do this, we ran a system CTL status command. So we go to the quickstart and we copy this command. So actually this command instructs our Linux system in order to check the status of the MSS corral hyphen server service. So let's go back to the terminal and execute this command. You can see that the SQL Server service is active, that is running. And you can see here the original timestamp where C whatsoever was successfully started, that is 54 seconds ago where installation of SQL Server or successfully completed. 7. How to Access SQL Server 2019 on Ubuntu Linux using Azure Data Studio: Now let's see another interesting aspect of working with SQL Server and Linux. In this lecture, we're going to talk about accessing sewer service tutors and 18 currently CT p3 on a Boolean Knox using Asia Taylor studio. Now a few words about Asia, the US to deal. Azure Data Studio is a free tool. It is Microsoft's cross-platform database tool for its on-premises data platform say whatsoever, as well as for its cloud-based data platforms, Azure SQL Database, Asia, SQL, Data Warehouse, et cetera. It is cross-platform and that means that it can run on Windows, Linux, and MacOS. It offers reach customizable dashboards. It is extensible. Now, the current system requirements about Azure, their studios that you can install it either on Windows 10.188 or even Windows seven with SP1. You can also install it on Windows Server 2013 to 201212 to 20162018 comes through and install it on Mac OS 10.13 High Sierra or MacOS 10.12, Shira or later. Moreover, you can install it on Linux. So that is, for example, Red Hat Enterprise Linux 7.4 and later 7.3 or later, Susie Linux Enterprise server version 12, SP2 or later, as well as on Ubuntu Linux version 16.04 or later. The system requirements are constantly updated. So you can check off and the URL for this system requirements and you can learn more about Azure Studio on these. Last but not least, and note that about Windows, only 64-bit versions are supported. Now, let's install Azure, their studio on Linux. So we're going to install Azure Studio on the same Linux virtual machine we worked in previous lectures in London demonstrations. So that the so mandu Linux version 19.04. And the procedure is straight forward. Therefore, we're going to download and install the latest release. So we're just installing and launching the tool. And either an issue can install as well, missing dependencies. Ok, let's proceed to R and demo some back on our test environment and our Virtual Machine. Let's download Asia. There has to be of horrible Bulinus. So we search for Azure does to your downloads. And eventually we end up to the official Microsoft website. And here we have access to the installers. So you can see that here there are historic for all platforms, there's installer for Windows, MacOS, and Linux. I'm going to use the tar daughter GZ installation package. So let's download this. And I'm going to save it. Installation media is about 124. Megabytes, so allows your moments in order to download it. And let me table below on these official website, we can find installation instructions. So we are downloading a retired cheesy installation media. So this is a procedure to follow. Let's take a look if our installation is completed. So we need some more time and arrive after installing in trying to launch Azure Data Studio IV, that problem, we can check up out any missing dependencies, I guess them based on these excellent documents. By the way, while we're waiting for the installation media to be downloaded, we can copy these commands and prepare them from execution. As you can see, here, we need to replace these words varchar string with a proper version of Azure that we're downloading, which is currently 1.8, 2.0. So let's open a wealth transfer and paste the commons diagnostic to modify them. Okay, these are our commands. So we replace this string VARCHAR, we're downloading. Okay? And the next one as well. Ok, our cones are ready. Let's go back to our browser and check if installation is completed. So not yet to Currently I'm experiencing a slow internet connection, so we'll have to wait a bit more. By the way, if you don't have any data connection, you can download the installation media from another computer and you can place it on a Linux environment in order to be able to proceed with installation procedure. So now we can see that our installation media for Asia does to you has been successfully downloaded. Let's check its location. So let's right click on it. Opium containing folder. You can see that it is in mind downloads falter, and this is the name of the file. Okay, now let's go to the web and start copying and executing the commands. Let's clean up our screen. And okay, so now let's change directory to downloads. Okay, that's good. So now let's, let's extract condensed archive. Let's delete these empty space here. So let's run this command in order to export n. Now we are exploiting all edges to files from the archive who downloaded from the official websites from Microsoft. Okay, and let's update our path variable. And the last step is to start Asia. They're studio. Yeah, so if this is the first time you are working with SQL Server on Linux and you are in the process of connecting to and deliveries engine using a picture that will consider these moment is at peak, just like I did when I first did this. So now let's connect. Here. Let's just put a localhost with a dot for example. And let's look in without dare say user we created during the installation of SQL Server. Ok, let's connect. And you can see that we have a successful connect it to our instance of Coursera 2013 CTP, sorry, 0 on Ubuntu Linux. Let's maximize this. And now we are running and graphical user interface tool that these Asia, they're studio, an excellent client tool for civil server cross-platform. And in this Linux environment, we can access our database and run queries. For example, let's type here new query, and let's create a new database. So we have IntelliSense. We can where we see whatsoever, just like we were in our open system, for example, in Windows or on Asia sequel database. So it's business as usual. So let's create a database. Let's name these database test1. And TR. Database has been successfully created. Now let's change the context of our query window to this database we created. And this is tested one. Now let's create a table. So let's column, which is primary key and a code lab car column. Let's run the command. Also. Let's import some data allow test. Excellent. And Les Brown as a simple select query or to check the table of contents. And you can see the intelligence feature is really amazing. These are the contents of the sample table we had just created and populated test data. And let's export this as a CSV. So let's export to downloads folder for example, or documents. And let's name or final results dot csv. So let's save it. And this is our phi. So now if you really want to work cross-platform, who can backup the database with a backup command, you can restore it on other appended system with SQL Server that the same version or invertebrates Shawn, Show you can really work cross-platform. 8. What Have you Learned in this Course: So now let's summarize what we have learned in this course. So in this course, we talked about the system requirements for installing SQL Server 2018. And we saw a live demonstration of installing SQL Server 2013 on Windows, along with providing useful tips about the best practices for when installing SQL Server. Moreover, we've talked about SMS version 18, and we've compared it with SMS version 17.9 talks about the requirements. We show an installation. And then we've talked about the Security and Compliance enhancements, high availability enhancements, and other a significant enhancements in addition to all the path. We've talked about SQL Server 2010 on Linux. And we so interesting live demonstrations of installing SQL Server 2018 on the Linux using an online installation, as well as how you can start using sewer Server 2013 on Linux via Asia Data Studio.