Transcript

00:00

DB PowerStudio

DB Optimizer is the last product of the bundle of DB PowerStudio is the solution to optimize and to profile your databases. You can easily connect to your database and start a profiling session to see what is happening currently in your database and use other features to automatically generate the different query plans to execute the query and automatically find the better query plan to use towards better performance with queries. Okay, let’s focus on how to manage now for the information on the website. If you go to IDERA.com you will see the IP from products you can find DB PowerStudio. In DB PowerStudio you will find the different products I just spoke about earlier. And from this product, you can select DB Change Manager. From here you can have different information, and the most important part is you have a button to start for free.

01:30

Virtual Machines

You can download the trial version of the product on your workstation and start it to test the solution. Everything I will show you today, you can reproduce on your side and check if it matches the features that you are looking for. So Divisions Manager is here. I’m just launching it behind the scenes. I have launched different virtual machines to host my databases. I have some overarching and MySQL available in this virtual machine. We’ll be able to connect to this machine. When you have the downloaded version, you should receive a cell number to register and then when you are installed, when you launch it, you will have this welcome message which helps you to easily find all the information that I will talk about today for getting started. This wizard asked me if I want to compare from the schema.

02:46

Connect to Database

From my schema I can get a snapshot from the schema, I can set up my databases which is the first thing to do when you set up the product. You need to set up some data sources to be able to connect. I will show you mine on the left part of the product, and if I select for example Steam Archive, it automatically will open this button. I have my cheat sheets which are open and show directly all the different information. Again I get and click the different steps or produce and do it directly. I will reduce this part on the left part.

Register Data Sources

Like I said, we have all our data sources. We currently see some folders. In my folders, I have my different data sources. When you first install the product, you will need to register these data sources. Like I said, we are supporting DB2, SQL Server, and Oracle for now, so from here we can directly click on the database platform that we want to register.

04:06

We can select the folder in which to register it. We have some colours directly on the corner itself to easily see if we are working currently on the database platform, which is for our production environment, or if it’s a test, and so on.

SQL Server

As I have selected Microsoft SQL Server, I have specific fields to fill in order to be able to connect to my database. When I have provided all this, I will be able to connect to it if I edit when I have registered. For example, I have my virtual machine source name, the port I use and the user. Okay, so now I can easily expand this node and directly I can expand my database objects and I will be able to see the different databases that are hosted on my server and I will be able to check directly from here.

05:21

Database Objects

My default object I can see is this table. I can easily see the columns that I have inside it, and can right-click and extract some data to see what is contained in my table.

SQL Code

For example, from here, as you can see, we are able to create some SQL scripts from here. and I will be able to have some code completion directly and I will be able to load some cache to be able to provide the column and so on. You can use this without this edit to type all the SQL scripts you need and directly be able to check the data that you have. From here, I can see my database and the different objects I can show you.

Archive

I have two different kinds of jobs. I have one to create an archive, which is a snapshot I can generate for the schema, and the other one is a comparison. We’ll be able to compare two different databases or compare some archives with other archives or with live databases.

We’ll start with archives and create a new one. I will give a name to my archive. I can specify specific projects to store my job and aggregate all my different tasks. When I have specified this step, I can click here, for example, to go to refinements. I can also use the tabs that are here. I can use the Back and Next buttons. Lots of different ways to manipulate it. I haven’t chosen my source because I was under data source and I have created a new job, a new archive job. You can easily change your job by just dragging and dropping this source and it automatically changes it for the refinements.

08:22

Configuration Parameters

I will be able to see from here all the different values, configuration properties, and parameters that can be extracted from the servers, from the server directly.

Extract DDL

You have a different option if I want to extract the DDL for the specific options. With DDL I can specify different options for automatically delaying the older version after the value I set here if I want.

Email Alerts

When I run a job, I will be able to set its time to specify if I want to receive an email, if I want to be notified with the file directly or with an icon in the system itself. The last one is the story when you have run your job, it has been performed. This new job, when I save it, I will see my first version. If I run it again, save it again. In my story, I have two new versions of this job.

09:45

Generate Reports

If I check my results, I can see from here all the values. I can export it in a specific format. For example, RTS if I want to use it with word PDF if I want to generate a PDF. On desktop, I can specify all the different parameters I want for my report. When I click the finish button it automatically will generate my report. It should be finished if I go to my folder. You can open it and see that it has automatically generated the report with all my values. This is the configuration archive and how it works. I can click here and it will generate a report. From here I can see itand browse my different pages from there. That’s it for the configuration architecture. Quite simple.

Data Comparison

I can do the same for the comparison job this time. I will compare, for example, this SQL server here with another one, which is another VM in another version. Let’s compare this one. I’m just keeping the same platform, SQL Server, for my example because for the configuration itself, it doesn’t make sense to compare the configuration itself with the schema. Like I said, for the first one, we had some archives, some archives of my configuration. I can use my license to connect directly to the databases. Or if I have some, I can connect to the Archive. From this archive, the one we have created together is Job 1. I can select this job and I can use the latest release we have created, which is version two, or I can use a previous version or a specific version. If I want to compare with V 1 and so on, I will be able to select which version I want to compare each time.

Okay, so it is interesting to create some archives and to be able to reuse them to compare with a specific version you have of your own. I’m using the most recent one with another database. If I go to the five months and I can select the properties I wanted, it takes the properties from this source. I have a different option. The notification is to react and click the play button. We can see here that we have 84% which are the same and some differences between the two nine properties. Not fun. Like I said, no choosing is a sensation of SQL Server so it makes sense that some configuration parameters have been deprecated, stuff like this makes sense and some others didn’t match. The values are not the same. I can easily click but I want to view a report like this. I can easily see the differences and browse my pages to see what my values are.

14:26

I can see this property doesn’t exist anymore and I can see here okay, the public version is different, the memory size of my virtual machine is different, and the Windows version is different. Okay? I can easily create this query. The differences only concern the version of the database and Windows. I can click the generic clip but I don’t have any properties which I can change with SQL. I can run whatever query I can run. It will not change my Windows. So that’s it.

Schema Archive

For the configuration, we can go to the next part which is the Schema archive and Schema comparison jobs. We can do the same as we have done for the configuration and can click and create a new configuration job. We’ll call it a Schema job. I’m using this data source from the refinement part so we’ll have more data for the configuration. For the schema, I can select the Schema.

15:54

Filtering Data

I am interested in the two parts on the left. This one and this one are only filtered and what we will collect from the database is the right part. From here I’m just filtering some data. I’m just interested in my table and dependencies. I don’t want the server type and so on and from here I don’t want this and if I check I can see the table we have shown earlier and it’s okay for me. In the option we can select if you want to include the dependencies from the table directly in my archive.

DDL

If I want to extract the DDL, I can use extract my DDL using the same option. To purge. To remove. The old versions notifications option next and I can click the play button to automatically extract all my object from my game schema and if I want to see my database and I can easily see what I have extracted from my archive like I said, for the configuration you can create with this archive job easily some version, some specific feeds to be able to compare later with different other implementation of my database and to be able to see all the differences between my version, my release I have created and the current version, for example, or another version.

17:44

Compare Database Schema

From there, with the comparison job that I will show just after we will be able to generate some script to synchronize the different schema. So I save my job. I will see in my story my version one and I can repeat it later on so if I go to the schema comparison job, I create a new one like earlier I can use my archive or I can use the tool live data source. I will compare it with SQL Server. With this one, I go to refinement, and I will select my game. This is why I spoke earlier about the trial version. I’m just showing you some simple databases with some examples and I really encourage you to download the product and test it in your own environment to be able to check it and select target messaging. This one is also an important refinement to selecting the object options. To select my different options I have two.

19:21

Synchronize Database

The first one will change the behavior because the first is manual so it will compare but doesn’t change anything but the second one is the redundant rose because you will automatically synchronize your databases. It can be useful in a test environment or specific development environment with the automatic feature we can easily compare to the version and automatically when the difference between the database schema is above a threshold that we define, we can specify that it will automatically update the database. As it is automatic, the risk that you can delete some important things and things like this or you can use the manual one really depends but on the production of your month, I will not choose the automatic process in the other option. We have lots of them, but some are interesting if they only exists in one of the parts. Should we drop this object or should we just enumerate different things like this for my different options?

20:53

I can also extract my DDL, for example, to be sure I have a copy at least of my database for later. I click the Play button, notification as usual, and it is automatically connected. It is automatically connected to my two databases, extracted the different schema and I see that I have 77% matches I can generate from here whenever I can show it. I can show my individual results. From here I can easily filter and see my different kinds of objects on the right part I can uncheck my match so I can only see the different objects. From here I can easily click on a table, for example, and I can see from there the difference. I have view details here which show me that my last name and first name columns in my first database are 60, and in the other one it is 40, 25, and 20.

22:15

Export Data

Directly I can export this result. I can also view the differences between my two DDLs. I can directly compare my script from here. Each difference I can easily see the source DDL and the target DDL. If I want to compare it manually, I can do it. I can open the transcript from here and from this script. Like I said earlier, we have a SQL editor to clean the product. We can play this cape, and it will automatically alter my database. Of course. We find the good credentials to perform the quote, they can click the play button and we automatically change my database. We’ll save my comparison job. If I replay it directly, as I have done my alcohol now I see I have 83%. If I check my results from here, I mentioned change. I see that. Certainly, I haven’t compared this view. This is because I have only generated the script for the table I was on.

24:11

So I haven’t changed it. I had to understand the way it didn’t work so as I have done, it is only for the current object for me. Okay? I should have used the script generator which generates the script from all the objects. For me, I can play this script. Okay? So I can save it once again. So I have two versions. I will create my 3rd 184 percent, which is better. Top manage. This is my view, probably. In the option. I can refine and avoid this so I can see all the other different objects and export my script to save it for my eyes. I can generate a report as we did earlier if you want to. Use it for example with excel. I will probably choose a csv if I want to generate a direct HTML page. I will say this one PDF world depending on what I want to do with so that’s it for the save it.

26:02

Compare Data

Like I said, if we go back to history, I will see directly from here my discount one. They will be able to select directly the one I want with the individual results. Okay, this will source the schema. The last feature is for data itself. For the data, we don’t provide a job because we are not providing this feature to extract all the data from the database. We are just comparing the data directly from two databases. I like to see there’s no option for Live. I can compare if I select the game option for the option, depending on what way you select. You see, I have different options for how we will compare the data. Like I said, for the Schema, we have an option for automatic synchronization, which means that if you have run your automatic synchronization with the CBI, you can run the job to do it.

27:44

Data Masking

For the data itself too. I haven’t mentioned it, but it’s time. You can create a job for a job. You can create a command line to run this job from a batch, from your computer or from an unscript if you use it. From automatically synchronized with this option, we also provide some features like data masking. For example, if you have a credit card number or some information you want to add when you copy the database from the source to your target, you can specify some data masking to generate some data similar to the original one. If you want to be sure or to shuffle all the data to be sure that the data can be used in a bad way manually. I can select different database mapping from me, I will select transport my database, my two databases from my source and my target.

29:07

For me I am able to see my different tables and current columns that will be used for the comparison. I can also edit the SQL if I want to have a subset of stuff like this, I can use this to automatically remove some data notification history as usual, and finish the job. It is running, everything is running on my laptop. I think time out with the network issue. Comparison result, it has compared all my data and all those are matching, which is not very interesting for my demonstration. We’ll select some other data so I can save this job and I will go back to my option of our view. I will select this database and this exact same database in fact. As you have seen, I can select for example my data from two different databases on just the server. I click game for this one, game dev for this one I can go to my option, and go back to my mapping because I had changed it, so I refresh it.

30:57

I have the same table in my two environments so that’s okay and I can play my job. Normally we should have some difficulty, but it connects to my source. Extracting the data for the first table does the same for my target. Compare the data for these tables and go on. Let me check here just to show you. Okay, so this is DBArtisan, I’m connecting to this one. I go to my database, I go to game tables no okay. If I go back again, I should have two differences in my book of tables between the twelve stances. Okay, my bad, I was already having some defenses but this is haunted and it was 100% I see I have five rows so I haven’t read nicely. I can click the view button and from here I will be able to see the differences. I see now I have four differences here between my Jackson and mine.

34:04

Okay, and we have some other previously existing and we can see them directly from here. If I want I can select one role for example and from here I can make source match target possibility and automatically I say this is the good version I want to get to keep and that’s it, Jackson will keep this one and then I will generate and open it and it also consents. Okay, so this is the data comparison job and that’s it. If I create another one to show you the difference. For the data masking data comparison job, I have to show you the different platform support options. If I go to the automatic option for this, but if I go to mapping, if I click Refresh current, we can see from here, for example, for the client I have some of the first name and last name data location.

35:59

The size of the data is not the same. One is 60, the other one is 50. It’s warning me that I could lose some data and otherwise, I wanted to show you that when we are using the option of automatic debt we can select have a discount which was not available in the manual, which allows you to shuffle mixing the data between the rows and creating some random values based on the current values included. If it’s for example numbers, it will only create numbers. If it’s some letters, it will create some later. That’s it. Like I said, I show you when we register, and we can select this different platform. Gene gdb is just useful for the SQL editor. You can use genex gdbc connection and use some SQL editor to run your queries, but you will not be able to use it for the main features for the archives and compiled.

37:29

Multi-Platform Support

We are supporting different platforms and like I said, for example, I can connect to my Oracle the same way as I have connected to my MySQL. For me, I don’t take a note which is a database, but I will find things like my cable directly available here with prefixes for my source and so on. And I can see my client. I should be able if I go there from here, I can launch the comparison data job you see, I have right-clicked and clicked compile. I have right-clicked and clicked the Data Comparison job from here. Earlier I was getting news from this node you can use File new configuration from this button from File New you have also the same so many ways to achieve what you want to do. I compare Oracle for example with MS Square. I can do it.

38:56

My database is automatically here. I refresh my team, they will be able to see my table and some differences. My archive is slower on this end. This is why I haven’t chosen. It’s trying to get my data and some automatically will bind the different data types between the different platforms, different kinds of data types and some specific fields can be mapped. You can’t map it directly, so you won’t be able to compare this specifically. I spoke earlier. You have the play button, you can stop it when it’s running. This is where you can click for example to generate the command line. Okay, sorry for that. It has been compared. I can see that I have some partial mapping between my different tables. If I check here I can see for example that the data hasn’t been bonded for the time stop. If I click here, I won’t be happy because that seven and that ten can’t be compared.

42:05

I should run Schema comparison before maybe towards the same size and then I will be able to run it properly. As you see, I’m able to bind for example in my table client I have some number here on the merits here and this is my dividends manager which automatically binds the different data types between the different platforms. Kind of reporting. I want to generate the folder where I want to save the job and I can save this script and automatically will run my job with the job name the pass automatically runs it when we schedule it. For example, we will be able to run it whenever we need since we have the main features of the product which you can find back in the welcome data shown to you at the beginning. Maybe it will make more sense for you now. What would you like to work with?

44:01

The setup for the data sources, the Schema object to create jobs or comparison jobs from here? This is a job I have worked with today and earlier. I can directly reopen them from here and some tutorials to explain to you what you need, and what you may want to do and like I explained earlier with cheat sheets which automatically open to you. That’s it. Let me open my go-to meeting. This presentation will soon end. This is how to manage the change for different platforms from a single interface. If you have any questions, don’t hesitate to contact IDERA directly. You can find our coordinates on our website. You can directly contact your federal manager to have some more details or we can plug in a demonstration if you need. You can request a demonstration and we can schedule for more specific data. Or if you have some specific questions, do not hesitate to contact us. Thank you for attending.

46:07

Cheers. Bye.