Video : How To Use DBArtisan in Multiple RDBMS

Transcript

Hello, everyone, and welcome to this short webinar on DBArtisan. DBArtisan is a tool by IDERA Software. It’s a multi-platform tool for database administrators. We connect to many different platforms. The advantages of DBArtisan are that you can learn one user interface and manage all of your different platforms using one tool, versus having to learn each individual vendor-provided tool or third-party tool that only allow connection to one data source. With that, let’s go ahead and get started.

We can see here that I have several SQL Server instances, and I also have an Oracle instance here. Let me explain the different panels that we see. This is the Datasource Navigator here on the left, and on the right, we have the Datasource Explorer. That consists of two panels: it has a panel that lists your instances as well as what will be some more information that we’re going to see once we get a connection established to that datasource.

We’re going to talk about registering datasources. You can choose Datasource > Register. I’m going to click edit because we have these already filled out; it will be a little easier to understand what needs to happen. This is the different platforms that we support natively, and I’m going to connect to this SQL Server instance, so I would select that database platform. Then I can click next, and I go on to the connection information. We’re going to connect through TCP/IP, and I’m going to list the IP address here. If your network will resolve the host name, then you can use the host name. Mine doesn’t so I have to put the IP address in this instance. The port, obviously, is 1433, the default port, and I could give it a datasource name that is independent of the host name. If you wanted to call it something that was a bit more relevant for a human to understand, you can certainly do that.

For security parameters, here is where you provide your login credentials. We do allow you to select an auto-connect feature so that you don’t have to enter the user id and password every time, and it does encrypt that password, so you are fully protected from a security standpoint. I can also use the option to connect using Windows authentication, so if your systems allows you, using LDAP for example, to connect using that user id, you can simply check the box and you don’t need to fill anything else out, it will automatically connect you.

In some different platforms, you may have to enter some custom driver properties or datasource properties. We do allow you to establish datasource groups, so we can see here that I have two, SQL Server and Oracle. You can also select a category for your different instances. This 157 box is listed as a Production server, but I could also mark it as a Default (no setting), Development, Test, or QA, or I could customize this if I had a situation that didn’t fall into one of these categories.

Once you provide whatever credentials you want, it’s a good idea to click Test Connection, and it will let you know whether you succeeded or not. That’s handy to be able to check your ability to connect before you leave the datasource registration wizard.

At this point, I’m done, I’m going to click Finish. Now, that doesn’t connect you; that is just setting up the datasource in this list. You can see that if I select these, I can right-click at this point and choose Connect. If I had a similar datasource that I wanted to register, I could also use Register Like, and it would create another datasource and give me some of the basic configurations that we have set for this one. Of course, I might have to enter some new information like a new host name, but generally, that’s a time saver.

Once you click Connect, you’ll notice there’s a red carat that pops in and indicates you are connected. I can also connect to multiple datasources at a time. If I wanted to connect to this Oracle instance, I could connect to that also, so you don’t have to be restricted to having a connection to just one instance at a time. Even on multiple platforms, you can connect to different instances.

This is our Datasource Navigator. I can select an instance here, and it does not affect my Explorer window. My Explorer window is governed by the item I have selected here, and these two things look very similar but there are some differences. You notice that when I select this (in the Navigator), nothing really changes, but when I select the instance over here (in the Explorer), there is a change in my Explorer panel that shows the Overview of that instance. It also changes the selected datasource up here at the top. This is important, because in here (Navigator), I can go through things and look at different types of objects without affecting the work going on in the Explorer. But if I come over here (to the Explorer) and change (instances), then that changes this (the Explorer view), and in this example, this 114 box is marked as a QA box, and GIM is my default database when I log in, so it’s automatically selected. If you wanted to select a different database on this instance, you can select from this dropdown. I could also select a different datasource here as well, so I could choose Oracle, and it would switch down (in the Explorer) to this over here. Just be aware that this functionality in these two drop-downs is controlled either through the Explorer or through the selection in these drop-downs.

I want to talk more about what’s available in the Datasource Navigator, because there are some things here that you can do, so that maybe you don’t need to dig into some of the more granular details available in the Explorer. For example, if I wanted to create a new database, I can select Databases, right-click, and choose Create, and that’s going to give me a new wizard to create a new database. Most anything that you’ll do with DBArtisan that is along the lines of creating new indexes, new users, new tables, new columns, we’re going to give you a wizard to be able to do that, so that you don’t have to remember all the steps or the exact syntax, if you were going to write the DDL yourself. You’ll go through this wizard and you see down here at the bottom, the last item in the wizard is the DDL view. The wizard is not going to execute the DDL directly and create it on the fly; it will give you the DDL. At that point, many times during the course of a normal work day, you might want to make some changes that would probably be best suited to run during a maintenance period. We give you the DDL, and we’ll allow you to run that at your convenience, whenever you would like to do it.

I could create a new database here, as I said. Right-click is your friend. If you select an item in either the Explorer or the Navigator, it will give you the options that you are going to have available at that point, so it’s context sensitive. Some of the things we can do, you see here that I can update statistics, I can set this schema to offline or online, I could drop a table, I could run an extraction, I could open it up. There are a number of different things I could do. I could do backups and restores from here.

If I drill down here a little bit more, I’m going to take a look at the Tables here. We click on Tables, again here is a wizard to create a Table. You give it a name, fill in some information about the storage if you’d like to, provide the columns, indexes, constraints, and eventually you get down to the DDL view.

I can also keep drilling down, so we’re going to take a look at this Broker table. I’m going to choose Columns, here are the columns in my Broker table. Again, if I need to add a new column, I can do that, it’s easy to do with the wizard. I can select a New Column here, I could add a bookmark, I could copy the object name. If I double-click on it, nothing really happens; that is the extent of what you can do to these things in the Navigator window. The Navigator window is handy because it lets you take a look at things; maybe, for example, I want to be looking at the columns in this table because I’m creating another table and I want to see what the columns and the data types are. Maybe there’s some work I’m doing where I need to create a foreign key in a new table, and I can do things like that over here (in the Explorer window). I have the ability to see things of interest here (in the Navigator window) while doing more advanced things in the Explorer window.

For security, if I wanted to add new logins or server roles, all of these things are wizard-driven.

I’m going to close the Datasource Navigator at this point, and we’re going to focus on the Explorer. Unlike the Navigator, the Explorer is going to give me more details. If I click on these instances, it gives me an overview, and it affects the drop-down selections as well. As I drill down into my databases, if I click on Tables, it gives me more information in this right-hand panel. My Explorer is going to expose more information for me than the Navigator did. Now I can see the number of rows, I can see the file tables and file groups. If I double-click on one of these Tables, it’s going to open up the Properties of that Table. I can see my columns, my actual properties, data compression, indexes, permissions, any dependencies, my DDL view. It’s even going to give me a view into the data if I want to scroll through that on a page-by-page basis. Finally, if this table is involved in a lot of queries, maybe that aren’t using indexes, it will show you if you have indexes that are missing based on what’s been going on with these tables. This is a very useful section to be able to go through this quickly and see the different things that are important to a DBA.

This is available regardless of whether it would be SQL Server or Oracle. For example, if I come down to my Oracle instance, and I choose Schema and Tables, here is a large list of tables. These columns are sortable, I’m going to type in Broker to find the Broker table. Here’s that same user interface, even though it’s an Oracle instance. We do have some different options here because it is a different database platform, so there are a few more things that Oracle provides, but the basic things are still here, like my DDL view. So I still have that ability to get that information that is important.

One thing to point out: if I click on Oracle and open the Schema folder set, it may not be what you want to see. We can solve that problem with the ability to click on the options to Organize by Object Owner or Object Type. If I click Object Owner, and come back to Schema, here is the different schema that you might expect to see in an Oracle instance where you have your different schemas. It’s just a different way of viewing things. It doesn’t have as much effect on SQL Server because they handle database instances and databases differently than Oracle does. Just be aware of this drop-down arrow that we can organize by Object Type or Object Owner. The same functionality for that organization option exists in my Datasource Navigator as well as my Datasource Explorer. There’s no reason you can’t have one set one way and one the other, if you want to view things in two different ways.

I’m going to select this (SQL Server) database again. We’re going to look deeper at some of these things. If I want to see what my indexes are, I can double-click on the index, and I’ve got tabs up here that are going to give me the information I need. Here’s my DDL for my index if I want to see what that looks like. Foreign keys, triggers, procedures, we have all this information that’s available right at our fingertips. It’s a common user interface, so I can find the same information that I would expect to find under Procedures, I’m going to find Procedures under Oracle as well, and that information will be easier to find. Rather than having to learn separate tools, the UI provides a benefit in flexibility, and it helps you be more efficient in the things you are doing.

That’s a quick overview of DBArtisan. It’s a tool for database administrators to be able to manage multiple platforms with ease. You don’t have to learn every different tool for every different platform that you would need to manage. You can learn one tool, learn to manage that through one user interface, and it makes life a lot easier. Thank you for watching this short video on DBArtisan. I think you’ll find that it’s a very flexible and powerful tool.