Video : Relational Database Management Using DBArtisan

DBArtisan

Hello everyone, and welcome to this webinar on Dbartisan. Here, my name is Anil, I’ll be your host for the afternoon. What we’ll do is a quick housekeeping rules. If you have any questions, please enter it into the Q and A box and then we’ll be happy to take those questions. If you have any other follow up questions, please submit your information in the chat and then one of us will be able to get back to you and do a more personalized demo. Today the focus is on how you can keep your databases running smoothly in terms of using it with our DV Artisan product. Here what you’re seeing is can I get a quick show of hands that you’re able to hear me clearly and see my screen please? Okay, I see we have a couple of people just joining us, so we’ll just give it a moment.

01:14

While we’re waiting for folks to join us, let’s go ahead and take a look at the website here. This is the Idera website. Once you go to Idra.com, you go to Products, and then you go under DB Power Studio. That’s where you can actually go ahead and look at all of the amazing product line that we have. We have each and everyone our family of products, which is called as DB Power Studio. With that it comprises of four different products. The first one is for database administration, which is DB. Artisan. We have DB Change Manager, that takes care of database changes. We have DB Optimizer, and finally we have Rapid SQL. This session is being recorded, so as soon as the process takes a day or two, you will get an email from us regarding the recording. Again, I see a bunch of folks joining us.

02:15

So welcome. Now what I would like to do is to cover our main product of the day, which is DBartisan. Can I get another quick show of hands that you’re able to see my screen, please? Okay, so what I’ll do now is I’ll showcase our DBartisan product line. Here you can see I have a bunch of data sources being registered. Here I have a SQL Server, and then here I have some ODBC servers that I have connected to as well. Now, the first thing here that you’ll see is if I click on Register, these are the various sets of databases that we support. It can pick IBM, DB two, you can pick SQL Server, Oracle, MySQL, et cetera. Postgres as well. Once you have connected to it. Here, for example, I’ll go ahead and choose this one. You are brought into the section where you can see the metadata of this particular server on what the build is and what are the processes, all that good stuff.

03:26

Database Monitor

Next, the first thing that you will do is that you’ll click on the monitor button and that’s where you can look at all the list of database processes. Now as a DBA you want to make sure that all of this information is accurate. You can go ahead and see all the running processes here as well. Next what we can do is we can actually look at our filter. Here you can see, you can engage a filter by ignoring system objects so it’s not going to show you any of the system objects. You can only show my object for the user that’s logged in and has the permissions. Finally here you can look at the server version metadata and look at the configuration. You have our SQL Server Agent and then you have our error logs. Here you can see all the error logs and then you can have some server level triggers that you have there.

04:22

Data Storage

The next area is storage. Now storage will give you the ability to create backup devices. Look at the data files that you have here and you have some file groups and the logs that you can look at. Next we move on to the security area where you can actually go ahead and look at also list of logins. Here you have the ability to also look at any server roles, any links servers or remark servers.

Databases

Finally here you have the ability to look at the databases. So let me expand that. Here you have a bunch of databases that you can work with and I also have another SQL Server that has a bunch of user databases populated by the adventure works and all that. Here I’m just going to use the AdventureWorks Lt 2019 for this example. If I click on tables, you’ll see I have a list of tables here.

05:20

One of the cool things about DBartisan is the context menu that’s available. Anytime you can right-click, you could do almost anything in DBartisan. If I wanted to know more details I can right-click and say open and it brings up the property sheet for that particular table. Now within the property sheet you have all of the various areas that you can see if it’s enabled and then you can also look at the space that’s been allocated in terms of the data, how much it is and how much and if there are any dependencies. Now this is a very cool feature that you can actually go ahead and look at it and here you can look at if you want to edit this, you can look at that plus it even references the user data types as well. We can take a look at the permissions.

06:07

User Permissions

Here you can use Grant revoke, etc. Then you have the DDL view. This is where all the actual DDL for this particular database table resides. It if you want to look at the data. There you go. Now while we’re here you can actually take a look at, let’s say if you are working on a test application of your current production application and you want some sample data, you could pull some of this metadata and say generate as, insert statements. Now this will actually go ahead and generate some insert statements and go ahead and populate it in the ISQL window here. This is pretty cool. Right now I can actually go ahead and format. There you go, looks pretty nice. I can also go ahead and syntax here as well. Now this enables you to actually go ahead and look at all of this metadata and this is so useful for a lot of areas.

07:09

Next, let me go ahead and do a select star from and here if you look, I can right click on it and I can view as HTML. It’s nice. I can also right click and view it asCII if I wanted too. Finally, if I wanted to just do it as a grid, I have my grid control here as well. Next, what I have is the ability to one of the key benefits of DBartisan is the ability to get things done quickly without too much fuss. Here if you look at some of the key points such as updating statistics, you can go ahead and update the statistics here and there you go, just like that. Next what you’ll want to do is if you want to go ahead and set a checkpoint, if you want to do any DVCC commands, we all have all of these here as well.

08:01

Data Filtering

Do a check catalog, you can do this one here. I can actually even go ahead and preview the sequel of this and schedule it if I want. That’s it. Next, what is really unique to DBArtisan is the ability to actually go ahead and filter what I would like to see. Now I can organize it by object owner. I just have just based on schemas, I can just organize it based on the schema and there you go. Now, this is a much more cleaner way of looking at things, but if you would prefer, you can also include it based on object type as well. Next, you can say if you wanted to go ahead and show system objects as well, you can go ahead and do that. Next you can only go ahead and show my objects also you can do that as well. What you can also do is you can actually add a bookmark.

08:55

Now, this is really cool. Let’s say, for example, if I wanted to go into the sales Lt schema, right, I go in there and then in fact, let me do this here. Let me go ahead and object owner. Here if I do that and here if I’m at the sales schema, I just want it to be all the time. I need this. I can go ahead and if I’m somewhere else in DBartisan, I can actually come in here, go to my BOOKMARKS, go to my SQL Server and there you go, it takes me straight to that. So again, a great time saver. Next, what we can do with DBartisan is to explore the Data Sources menu. Let’s go in here and I’ve shown you how to go ahead and register a data source manually. What if you wanted to go ahead and click on this and let me go ahead and delete one of these.

09:57

SQL Server

And then I just click on Discover. What this is going to do is this is going to go over into the network and try and find me a list of SQL Servers or any of the other database platforms that I have registered on the machine. It’s going to go ahead and discover that for me. While it’s doing that, let me just go ahead and instantiate another instance of Dprdison. There we go. Now what we’ll do is let me go back here and I’ll go ahead and register one manually here to show you. So here I go into SQL Server. Here I’ll just put in the instance name my security parameters. Here. I’ll just choose windows. Auth we also support all of these different types of authentications. You also have this interesting thing called a data source group. Now let’s say that this is a production database.

11:09

You want to customize this and get this here. And this is also customizable. You can actually give your own server group Seven if I want to, but I can choose anything I want. Here I’ll just put as red and I can give it a shortcut name. Choose here Finish, then I can connect it. There you go. I can see that there’s icon associated with it. That’s a great way of showing this.

Schema Extraction

Next, let’s talk about the Utilities menu. This is where you can actually perform Schema extraction, schema application, all that good stuff. Here if I click on Schema Extraction you can go ahead and pick the server and here I’ll pick this, then I’ll pick the Schema. Here I need the sales Lt Schema because that has all the info. I can go ahead and pick tables. Here, I can just expand these I need.

12:32

Next I’ll click Next and this will give me an option to pre-populate all of this. Now this is a great way of having it as an option template so you can actually work with whatever is needed. If you wanted to have some data movement rules, you can have the inserts statement as part of the extraction. If not, you can retain the owner or change the owner. Choose Next. And here we can hit Finish. While this is getting done here you can see it, found the link on the network, so I can just go ahead and hit register. That’s it.

Export Data

Now what’s really cool about the data source here is that you can actually export all of this as a central shared data source management file. Think of your dev test production databases and that’s where you can actually go ahead and leverage some of these to your advantage.

13:26

Import Data

Here I can go ahead and save this to a location. I’ll call this a master data source file and that’s save all the data source exported. Next, if I just wanted to select all connect and then I can just use import and wherever I save the data source files, it’s going to go as an ETDs file. I can just go ahead and import that. I have those options there as well. Next, what we’ll do is we’ll go ahead and go and hit continue. There you go. This is all of our extracted files that are going to be loaded here in just a second. There you go. So that’s how easy it was. You can use an add to script library here. Let’s say I add a control five. Let me go to another one. Now let’s open up a brand new SQL window. So there you go, that’s my square.

14:52

Metadata

Again, a great time saver. Next what we’ll do is let me spend some time on how you can actually look at the database manager. This is where it is all about the database and it gives you all the metadata. All you can do is just move around, look at all of that, then you can look at all of this metadata here as well. Let me take a look at a question here real quick. No, that’s right, it works only on Windows, but you can install if you’re working on a Mac or Linux-based machine, you can install any virtualization platform, install Windows on that and load VB artisan.

Data Analytics

Next, what we’ll do is let’s explore some of our analytics in built analytics analyst packs. That’s what we call it, the capacity list. Here, let me go to the other one. Let me see here, let me register the different one here, let me go to SQL Server.

17:04

Cool, we got that. Now let’s go ahead and show you here. So here I have my analyst packs. The performance analyst here enables you to look at ad hoc performance analysis within the database. Here you can actually do ad hoc as long as you have DVRs and Open, you can view all of these here, it’s just a small set of scripts that you install on a dedicated database. Here I’ve created different databases for that very purpose. CAS for capacity analyst, PA for performance analysts and essays for space analysts. With Capacity Analyst you have the ability to perform capacity planning. For example, if I wanted to go ahead and say new, I can kickstart a new capacity plan for this and choose next. These are all the list of structures that you want to go with and have that as part of it. You can allocate which database you want.

18:11

You can choose any of these parameters selected by object. If there’s anything, just tap this. That’s it. You can schedule this by the Windows task scheduler and go from there. Let’s see, we have a question looking at Schemas, could you reverse engineer an old Schema and modified? This is not a data modeling tool, but we do have a solution that you can reverse engineer from and make changes to it. Yes. One of the other tooling that we have is the ability to look at Schema based objects. So which is DVR? DB? Change manager. That can be done at the Schema level. Next, let’s take a look at welcome Teresa.

Performance Analyst

Next let’s take a look at the Performance Analyst. Performance Analyst is like I said, an ad hoc solution where you can as long as DBartisan is open, you will get like an ad hoc alert like this.

19:29

So within this you have some alarms. So it tells you this. You can navigate to these alarms and go to the applicable page and it takes you straight to this. And lastly, we have the space analyst. The Space Analyst enables you to look at all of the space as the name suggests. It gives you a nice GUI-based and graph-based area where you can actually go ahead and toggle the display between a grid view and this. In this you can actually go ahead and instantiate the Space Management and wizard. This is where you can choose all of the different reorgs and analyst jobs or maintenance jobs. Really powerful. So, the other options that we have is in our tooling section. Let’s say if you wanted to find in files, let’s say if there’s a file that I wanted to find and I can look at a switch table, I can do that, then I have the ability to do a database search.

20:31

Database Search

Now this is pretty awesome. I can actually literally just view it under here and see what all I have customer if it will find it and everything. If I’m looking for tables, views, procedures, any of those, I did not find anything here. Now if I go back to this one here, I can hit cancel. If I go back to the databases here and go to the tools and do database search and search across here and views, here you go, hit finish. It’s actually going to go ahead and search and once it’s found something, it’s going to go ahead and give you a display. What’s really cool about this is that you can actually search across databases and servers. If you’re having a bunch of servers that you have registered with a DBartisan. You can go ahead and search across that regardless of the database platform.

21:34

So that’s pretty awesome. Now it’s just going to search here. Almost there. And there you go. It’s found customers in all of these various locations. Isn’t that cool? Now I can actually literally just go in here and just say Open Editor. It goes and opens up that sales order header detail. Everything is at a click away and DBartisan’s user interface is really powerful. Next, if we want to go into our, let’s say our query builder, everybody who’s not an expert in SQL can actually just go in here and right-click and start adding areas. Here you can see once they’re chosen, I can go ahead and highlight which columns I want. Here I can do this as well. You can see I’m building the SQL here as well. All I got to do is just copy this going to a new ISQL window and I can also show the data types.

22:39

I can also go ahead and perform this as an Insert query builder as well as an Update, delete or Create view. It’s pretty nice all within the same user interface. So that concludes our quick webinar here. Let me take a few questions here. Let’s see, does this do statistical assets? No, this does not do any of that. No, you’re fine. Ask any as many questions as you want. Please ask any questions. We still have about three minutes, so please go ahead and ask any questions you have. If you’re looking for something on the statistical area or think we do have a product called Aqua Data Studio that actually does of that analysis. So you can check that out. From a DBA perspective, our goal and Idera is a huge organization. We have multiple products that complement each other. Along with DBartisan being your flagship database administration tool, you could look at Aqua data Studio.

23:51

That’s your analytics-based solution. You could also check out Er Studio Data Architect, which is our flagship database enterprise data modeling solution as well. You could technically build the database design and Data Architect and build the database and administer the database here in DBartisan and also perform any kind of analytics on using Aqua Data Studio. I’m going to leave it open for another question. So let’s say here. Excellent, excellent. Awesome, Teresa. Good.

If there’s no more questions, I would like to thank you for your time and please do share your feedback. What I would like to do is also focus on some cloud databases and some upcoming webinars. So that’ll be awesome. We’ll have some more content for you. This is an ongoing process, so we welcome feedback and we’re always eager to learn from our customers. We thank you for your business and for those of you who are joining us for the first time, welcome and thank you so much for attending this webinar and we looked to seeing you at the next one.

25:17

Thanks and have a great day.