Join Ron Davis for an in-depth discussion in this video Using SQL Server Management Studio, part of SQL Server 2014: Installation and Administration.
Let's take a look at SQL Server Management Studio, which is a tool that DBAs spend a lot of time in. So, if you remember, I pinned SQL Server Management Studio to my taskbar, it's right down here, and I want to show you something; you're going to right-click on it, and then I've got a number of options. Sometimes, depending on the permissions of the account that you have logged into, you will be prevented, in SQL Server Management Studio, from doing certain actions, even though you are an administrator.
If you encounter that, you want to do what I'm doing right here, and then right-click it again and run as administrator. If you don't have it pinned, I just hit the Windows key, you'll just type SQL Server, and then, over here, we'll right-click it, and we'll say "run as administrator". And now, SQL Server Management Studio opens, and I want to connect into my default instance, and I'll just select "Connect". Now, there's a number of tools that we look at; perhaps one of the coolest is the concept of doing reports.
So, up here, on the instances, I'm looking at my default instance, and I'm going to right-click in there, drill down and look at Reports, and what I'm going to look at, let's look at the Servers Dashboard report. So this is a handy little report; all of these reports have the .rdl extension, and that means it's a XML Schema that is used by reporting services. So what? Well, because it is an rdl extension, I can export it and import it into a number of other applications that support rdl, such as: if I have Sharepoint, and I want to create some kind of a Management Dashboard in Sharepoint, off of rdl type reports, within Sharepoint, I can go in and add rdl's into a Sharepoint web page and display them like that.
So, here's my Server Dashboard, and as you can see, if I move it on down, I have a number of different information and I'm really not doing anything on this server, so there's nothing much going on of interest here, it's just to show you that I do have this Server Dashboard. If I right-click again, and, again, go down to Reports, now I can go into Standard Reports and, you see, I have a lot of them. So, if I come through and I look at Memory Consumption, here's my top memory consumption, and recall that we're not doing anything, so this is not going to be representative of a standard database that we're actually coming through and doing work on.
But this shows me what's happening down here with my memory, and again, if I right-click and go into Reports, Standard Reports, and now I can look at the activities: All Blocking Transactions, or Dormant Sessions; I'll go to Top Connections, and it pops up the 10 Oldest Connections. It comes down here with my Connection ID which is a GUID, and my Session ID, so it comes through to show me that, and all the other information.
I'm always connected here onto my local machines, just the way I'm doing that. Now let's close everything, and close that so we don't confuse ourselves, I'm going to expand Databases, and here's our sample, AdventureWorks, and I'll right-click AdventureWorks, go into Reports, and you notice this, they want me to look at Disk Usage by Table, which ought to be a hint. So I'm coming in and I'm looking at Disk Usage by Table, and I can see just that as I move down.
Now let's look at something else, I'm going to right-click, Reports, Standard Reports, and we'll go with Top Transactions by Age. I don't have any currently running transactions, but if I right-click here again and go down into Reports, you see whatever I picked is added down here. So SQL assumes that a report that I looked at I'll be interested in looking at again. And if I click Custom Reports in here, now you see I can import an rdl report.
So, you would use SQL Server Reporting Services to create a report or maybe Report Builder 3.0. If you haven't used Report Builder 3.0, that's really something you need to take a look at. It was a huge leap forward, and it really is robust and easy to work with, and users love it. But you can create your report, and it's saved as an XML Schema with a .rdl extension, and then you can just import it and you've got it. And the reason you might want to do that, perhaps you use different graphics rather than using the accursed pie chart; maybe you use a column chart, et cetera.
So, there's my reports. Now, if I select my server up here and right-click it, I can also look at Activity Monitor, and this is the standard stuff if you've ever worked with Activity Monitor in Server, or in Windows 7, Windows 8, then you've seen about the same thing. And if I expand any of these out, I get my different processes, here's my SPID, my Processor ID, and information about those, and Resource Waits, and this will tell me the different waits that I have, I/O, I/O is something we're always concerned about because of disk contention, and Recent Expensive Queries.
And so this is just showing you an update that we run in here, maybe an insert, select top 4, different things that we've done. So there's my Activity Monitor. Again, let's close all but this and then close Activity Monitor. Another thing to take a look at, let's expand out Management, expand out Server Logs, and look at the log, we're just going to double-click it, and we'll expand Now, RESTORE DATABASE is the last thing in that log, and you remember, we restored the database when we were setting this first up.
Here's another database; is complete, "Restore is complete on database 'AdventureWorksDW'". OK, so here's your logs. Now, if I come down and I take, like, AdventureWorks or take a database and I right-click it, I can also look at the Properties, and this will come through and show me the different properties, and here's like the files. Now, this is interesting. Now notice AdventureWorks2014, when I brought it in, it does not have an owner.
That has a ramification; let me show you. So if I expand AdventureWorks, Database Diagram, right-click, New Database Diagram, it won't create a database diagram because there is no owner. So I have to go in and set up an owner; again, I go into Properties, Files, hit my ellipsis, I'm going to type in Administrator, and we'll check the name, and that's good, and I'll say OK, and we'll say OK, and now we've got that owned, and say OK, and Database Diagram, I'll right-click this, New Database Diagram, and now it's asking me for support, which means the dll that I created, and I'll say Yes, and then I can create a database diagram, which we're not going to do, I just wanted to show you changing.
There's other pages, so here's your different options we can come through, and remember that correlation? I can go in and set that up, and here's my recovery model, which we'll take a look at later on when we come in to maintenance plans, et cetera. Compatibility level's a big deal. A very big deal. If you bring over, say, SQL 2012 or its predecessors and attach it into SQL 2014, then the compatibility level that comes across will match what the old one was, so it won't be 120, I think compatibility for 2012 is 110.
I think. So why is that a big deal? It's a big deal because of this: SQL 2014, one of the great enhancements was the query processor, and the query processor was not enhanced since SQL -- are you ready? -- 7.0. So SQL 2014 changed the query processor, and how it analyzes queries, so you really want to go with the 2014 query processor to change that configuration, because it'll just do a far better job.
So, here's these other things, permissions, et cetera, and you can go in and add your different permissions. So, we'll cancel out of that. One final thing I just want to show you, I'm just going to click New Query, and I'm in here on New Query, and what I want to use is this tool, this Code Snippet Manager, which gives me this -- it's been around for a while, but I really like it -- it allows me to just import these little snippets of code. So, for example, if I right-click here and say Insert Snippet, and I'm creating an index, and I want to create a basic index, right, so down here it's got a great index and now it's got all the correct T-SQL.
Now, of course, you know, I'm going to have to change what the index is named and all this other stuff, but I've got the basic T-SQL in there for me. And here's my properties. So, that's SQL Server Management Studio. There's much else involved in Management Studio, but there is a brief introduction to it.
- Planning SQL Server installation
- Installing SQL Server 2014
- Using the tools: SQL Server Management Studio, PowerShell, and more
- Managing the databases
- Automating maintenance
- Performing backups and recovery
- Importing and exporting data
- Indexing databases
- Monitoring activity, performance, and events
- Implementing security