Transcripts
1. Part 2 Introduction: Hello and welcome to Microsoft Development Essentials. I'm your instructor, Erik Frank, in part two of this class. I'll get you up and going with Microsoft sequel server. First, I'll give you a background of the product. Next, I'll show you how to download and install a free version of the product. And finally, I'll show you how to use the management studio for basic database operations. By the end of this, class will be ready to get going with Microsoft Sequel server Thank you so much and I'll see you in class.
2. Microsoft SQL Server Overview: before we install sequel, server and assess, and I'll give you a brief overview of the product. I'll give you a brief history of the product and also described the additions of sequel server that air sold today. Also describe the features that sequel Server Express Edition offers sequel servers, microchips, flagship database product. They do offer other database products, such as Microsoft Access, but this is generally considered to be more of a desktop or single user database system. The first version of Sequel Server was released in 1989 and has undergone significant changes over the years. It should be noted that this database management system only runs on Microsoft operating systems. Many new features been added to Sequel server since its initial release. These include the introduction of XML data types, the integration of the common language runtime, or CLR in the introduction of new data types such as blobs for storing images. Other features they have added include options for high availability for such things as fail over clustering and the development of data access techniques for developers that include a geo dot net in the entity framework. The current release of sequel server is 2014 sequel Server, like most Microsoft products, comes in several editions. The Enterprise edition is the top of the line edition is designed for very large applications or database installations. The standard edition is the next step down in the product line is limited by the number of CP use in the number of machines that can be deployed on the business. Intelligence Edition is designed for data warehouse applications. The last edition is the express edition, and that's the version will be using In this class. It has the limitation of using only a single processor as well is utilizing one gigabyte of memory. In addition, the database is limited to a maximum of 10 gigabytes and file size. Their new limitations on the number of users that can utilize the system. Well, these although these air some limiting the system is ideal for a software development environment or a smaller application. If you're unsure, you can start with this database system and then upgrade the database later as the usage on the system grows. While sequel server expresses free software, it offers a great deal of features. First, it integrates very nicely with visual studio. You can design tables and develop sequel queries from within Visual studio for software development. The database also supports technologies like Link radio dot NET and the entity framework will not be covering these in this class, but I am planning a future class that will cover these in much more detail. In addition, you can manage sequel Server Express from the Sequel Server Management Council. From this council, you can execute tests, worries, do database design work and also used the database management tools. This database also includes support for Relational Data, XML data and spatial data. These air just a few of the features that this database provides. After we installed Sequel Server Express, I go through a brief demonstration on how to utilize the management council. I'll take you through Hello World for the database. This demonstration will start with creating a new database and then create a simple table. Next will load that table with data and write some simple queries against that data. Finally, I'll demonstrate how to back up and restore a database while these air simple operations that will demonstrate how to get up in going with the management console. In this lesson, we have looked at the history and the features of Microsoft sequel server and some of the features of sequel Server Express and the next lesson I'll show you how to download, install the software sauce you and then a next lesson. Thanks so much.
3. Microsoft SQL Server Installation Instructions: okay, in this lesson, we're going to go ahead and download and install Microsoft sequel Server 2014 Express. So, um, I went ahead and google the page here. So go ahead and go to that page and we'll download, um, Secret Server 2014 Express. Now, one of the things you need to be careful of is downloading not just sequel Express, but along with the tools as well. If you don't, if you just download sequel server expressed without the management tools, you'll have to download the management tools later separately, Which is which is kind of a pain. So So I'm gonna go ahead and select Express and tools the 64 bit version of that and select next. And it's gonna go ahead and kick off that download, and it's about 8 800 megabytes. So it's gonna take, uh, just a few minutes to download that. So once it's downloaded, we'll go ahead and run through the rest of the install. All right, a download is completed, and now I'm gonna go ahead and launch are downloaded file and right away. Mike, Microsoft Windows complains with this typical security warning, Son una say okay to let it go ahead and it will impact the files, and then it will kick off the installation script. Once. Since installation starts, it'll go through and ask. A number of different options along the way will probably take most of the default settings along the way, but there are a couple that I like to change that make it a little bit easier for a development environment. So the first window that comes up asked whether we're going to do an upgrade of an existing installation or we're going to do a new standalone installation. So I'm gonna go ahead and select new standalone installation in the next screen last to accept the terms and conditions, and I'll go ahead and do that and move on to the next screen. So in the next cup in this step, it's gonna check for system compatibility and see if it finds any issues. Now one of the things you'll notice here, then is that the features that you want to install and I like to go ahead and take the default of those, But if you haven't downloaded the correct version, you won't see the management tools, and it's really kind of a pain toe download and install them later so we'll see that they're here. So go ahead and take the default settings of that. Also, the next screen will ask for an instance name. Unless you're on a network, our corporate network or something like that, I would go ahead and take the default of these as well. I've noticed in visual studio some of the settings it likes to look for this default name. If you're developing with Sequel Express, I recommended just leaving this alone. Unless you have a specific reason not to change that, the next ah ask for Ah, the account names and settings. I go ahead and take the default in that case is well and select next. This is one setting. I do like to change the authentication mode. I usually select mixed modoff indication so that you can get in with Windows. Authentication as an administrator and also with sequel authentication is administrator account as well. So when you do this, it's going to ask for a an additional password, and it's just a little easier in case you happen to get locked out of the server from Windows authentication that you have a way to get in with sequel of communication as well. So I set both of those passwords and hopefully I type them both grab it Looks like it didn't complain. So when we wanted the next step and it will run through the rest of the base installation here. So this will take a few minutes of probably 10 or 15 minutes, depending on the speed of your computer to run through on. Then once that is completed, really are installation is complete. In the next lesson, I'll take you through. Ah, have Teoh start up the management console, how to run through some basic operations using the Management Council how to create a database, create some tables, some fields in the tables, put some data in the tables and also run through kind of a backup and restore operation kind of the, uh, hello world equivalent for databases. That'll show you basically how to get started with this tool. So the backup and restore uh, operation is very critical. It's probably that that's why I included in the first lesson it's one you need to know right aways, how, when you're particularly when you're working on software and you have the chance to really corrupt the database. It's It's critical to know how Teoh back the database up and restored to noon configuration . So, uh, kind of a lesson learned the hard way If you don't know how to do that, because e a a lot of grief while you're developing code. So, um, we'll go ahead and let this run and finish up. All right? Well, that really completes the installation process. And, uh um, go ahead and close down the window here, and I also close out this window as well, so that really completes the installation. And if you want to know that your installation has been successful, you should be able to go under the sequel server, um, window on the program start menu and launched the sequel Server 2014 Management studio. So it may take a few minutes the first time you set this up. But, uh, if we use Windows authentication and connect, here we go. So we're not connected to the database and our installation is complete. So in the next section, I'll take you through the basic tour, the Management Council and how to create a database. It's all seeing the next lesson. Thanks so much
4. Hello world database: Okay, now that we've installed sequel server, I'm gonna take you through some exercises. Teoh show you the basic operation of management tools and some basic kind of development techniques so you can get going with sequel server. So the first thing we're going to do is start up the sequel Server 2014 Management Express studio. So go ahead and start that up in the first thing that happens when the management studio comes up is it wants you to authenticate. So remember, during the installation instructions, when we talked about having mixed mode authentication in this window, you can either log in using Windows authentication or sequel authentication. And since we installed both, we can use either one. In this case, I'm just gonna go ahead and select a Windows authentication, and I will go ahead and log me into the database. So this is the Management Explorer studio. This is the object Explorer on left, and you can see there's various menu options for ah, different operations of the studio. The one that we're gonna look at is databases. So right off the bat, you get a couple of databases installed under the system database menu. I'm not gonna be particularly interested in nose in this exercise, but under here, we're going to create our own database, put a little bit of data in and show you some basic operations of this tool. So I'm going to select a new database by using the right mouse click Select New Date database, and I'll give this database and name. We're going to call it first. Hey, the base select Okay. And noticed that it creates both a database file and a long file. So go ahead and select. Okay? And you'll notice the green bar down there, and it is now created our database tail our excuse me. Not a database table, but a database rather. So underneath the database, you see a number of different tabs that you can expand. And there's various ones, such as the database diagram, tables of views that you can program later on. Some program ability, where you can put things like stored procedures and triggers and so on. In this case, we're just gonna select tables and again I'm going to to right mouse click on here on tables and I'm going to select the table selection so that so I'll select table. First, I'll give this a data type of 20 uh, last. Okay, and we'll give this a data type also of 20 you know, put in the email address and all sign that it's just a little bit longer of 30 characters, and we'll go ahead and close this and asked me if I want to save the changes to this item. House A. Like, yes, they asked me for a name for my table. So I'm going to call this employee employees Select okay, and you'll notice right away that it doesn't show up underneath the tables. Icon, Where is our table? So if I right mouse click and I select refresh now you can see the table. If I expand the view here, I can see the columns of that table. So that's kind of the quick and dirty way toe. Create a table. Now if I want to look to see if there's any data on the table, I'll highlight the table. I'll say select top 1000 rows. Obviously, there's not 1000 rows. There's no rows in the table, but this is the default sequel statement that will select data from the employee table. Now I'm gonna modify this in a couple of ways. First, I'm gonna modify the query windows defaults to the first database table, and I'll show you why. That's significant in a minute. So now if I say so, like star from employees and I execute that query, notice that the bottom note, no data comes up, which we would expect. We just created him an empty table. So go ahead and close this query window, and it's gonna ask me if I want to change. Save the changes to my query about really interested in that. Um, if I right mouse click now on this table, I can say edit top 200 rose, and that will bring up an edit window. So go ahead and put some name in this table so we'll start with, uh, Smith. We'll put makeup on email address here. Um, and let's put in one more. Um, so we have Bob Smith, Tim Brown and Craig Jos. So now again, I'll close this and it will save our data to that table again. If I go back under our employees table. I right, mouse click may say select top 1000 rows there is our entries again. Um, I can show you another simple query. Let's say we want to know how many Rose or in our database, you can use an aggregate function called Count. I'll execute that, and we get what we expect is three rows. So now that we have some data in our database, I'll show you another simple operation which is probably one of the most critical operations to know right away is how do we back up our database? How do we make a backup copy now that we have some data in there, So, um, if you if you go on on the database I'm sorry, if you if you right, mouse, click on, um, the database. Our first database icon. Let me do that again. All right, mouse, click up here and under tasks, there's a bunch of different tasks you can do, and one of those is is back up. So I'll select that selection in the menu pops of Can you notice it has our database backup type. We want to do a full back up. It's gonna back it up to a default location, which is ah, subdirectory. Where the software was installed. I'm gonna put it in a different place. I'm going to put it under a place that's much easier for me to remember. And I'm going to put that in the temp directory and I'll give it a file name. And to be consistent, I'll call it first database. So I have the directory and I have the final name they selected. Okay, here I'll remove the first location, our default location, so only back it up in one place. Also like okay and notice. It says it's successful. It shouldn't take very long. There's only three rows and in the database, so now I'm gonna go back under. I'll bring up our query window again. Um, I'll select that the rose and you'll see that. So I'm going to delete again. I'll select First Database is, as are a default database, and I'm going to delete one row from the database. So there's a sequel command culturally from eso it's delete from, and then it's the table name fleet from employees. And then there's a where clause where first equals Bob. Notice how Bob is in single quotes that is in sequel. How you have to specify strings. So if I execute that now, notice it says that one row is affected. And just to verify, we're going to bring back all of the records again and noticed down. We only have two rows as expected. So kind of that closed that operation. Say, Oh, Well, G I deleted a bunch of data. How can I get it back? Well, now I can restore that database from backup and on all our data should be intact. So let's go ahead and do that. So I'm gonna go ahead and select restore from from the database menu. I think we're going to select from device where we backed it up. I'm going to add, had our location that we backed up to earlier. I see temp, and I'll give it our our final name, which is first database. Looks good. We're going to restore into our first database. How select. Okay, our database has now been successfully restored. So now if I go back under, um, our first database and I look at our our original table and now I select the top 1000 rows . I now see the record that I had deleted earlier has now been successfully restored. So there's air really the basic operations you need to know. In Sequel server, we created a new database added to table should some basic query operations as well as a backup and restore operation. There's a kind of the 101 hello world kind of operations you need to get going with the database in subsequent classes, and later lessons will show you a lot more details on how to design an effective database, how to write effective quarries and then how Teoh integrate those into code into see sharpened sequel server. But for now, this is the basic lesson on, but thanks a lot for ah watching.
Eric Frick, Destin Learning
