Transcript

00:07

Rapid SQL

Today we’re going to be covering Rapid SQL. Let’s go ahead and we’ll get started. As I mentioned, the projects are supported in both Rapid and DBArtisan. If you were a Rapid SQL customer, you’ve known from early releases of Rapid SQL. We’ve always had problems project management support in the later releases of DBArtisan. Specifically, I’m working with DB. Artisan. Nine, seven, five. We now have support for projects within DBArtisan. Again, listening to our customers, listening to those DBAs that requested that support, requested those features, we’re now able to display that and utilize that in DBArtisan. On that, I’m going to be working with Rapid SQL today, but the functionality is going to be identical. First, how do we start a new project? So we have a couple of ways. We have the project tab. We can hop down and we can start a new project, or we can open a project.

01:05

SCCI

We can also start it from the File menu. File New. Here’s our project, and we can also open an existing one. Now, one thing I want to start off with is going into our Options tab. We’ll go to File options, we’ll look and let’s scroll down to our booking here. Where are we, Scott? All right, we’re going to come back to that. Let me come back to the options. What I wanted to point out here is where we assign the source control. The project facility within Rapid SQL and DBArtisan has the integration points to source control systems. I have visual source safe installed. I’ve worked with team Foundation Server TFS. We’ve worked with some of the other vendors out there as well. The key that we need, the one requirement is we need SCCI compliant. So obviously, visual source safe is, TFS is. For those that aren’t and may not potentially have that built in, a lot of times you can find wrappers, third party wrappers that will make the interface.

02:21

We get those wrappers installed and applied properly and configured, then Rapid SQL can view that. As I step through my project creation, you’ll see where that comes into play today, let’s go ahead and start a new project. So file new project. Now here’s where we add our name. Let’s just call this Skill Sprint project. We can put it in a location. This location can be on our specific workstation or maybe on a network share, but so I’m going to scroll down. I have a projects folder that I work with. So there is my Rs projects. Now add any descriptions if we want project for Skill Sprint. Now initializing the new project. We can initialize from database from existing files, so we can pick and choose what files we want to bring into the project. We can bring it in from version control so we can point to our source code control provider that we have configured and pull those files back in.

03:22

DBO Schema

Or we can opt to not initialize. By doing that we’re just going to start with a blank project and then we can build it as we go. For this example today, this skill sprint, I’d like to show how to bring it in from a database. Here just asking me if I want to create the folder, select yes. Here is where I’m looking presented with my list of data sources. In this case I’m connected to my local 2012 SQL Server instance. We’ll just go ahead and accept that we’ll press next we’ll select the GIM database. Now we can narrow it down to pick our specific schema. In this case, I’m going to opt for the DBO schema. For my example today, what I want to show is just select a subset. So I’d like to bring over functions. Here we can bring over the dependent objects, but I’m going to be bringing those objects over anyway indexes.

04:25

SQL Scripting

Let’s bring over our procedures, our tables, and lastly, we want to bring over our views. Again, as you see, a very simple editor, the wizard to step through if you are using DBArtisan, this is very similar to the Schema migration wizard. On the right hand pane we can expand the objects. In this database. I have no functions. Here are my indexes. So I’m going to select all. Here are my procedures. Again, in this case, let’s only select one of those the tables. We’re going to bring all those the tables back and let’s see what views we have. And again we have a few views. I’m going to select these three. So press next. Here is where we can start really fine tuning how we’d like rapid SQL to generate our code. Here we have the group dependencies, we can order drops based on the selection and we can script drop statements.

05:30

If it’s important for us when we build this project to always include drop statements, we have control when we select an option here you’ll notice it’s represented in the right hand pane. We’re going to add that syntax to check to see if it’s there, if it exists. If it does, then we’ll drop it. So again, just unchecking here. Again, this is our example table, our example preview table. It’s just a hypothetical table giving you the ability to see what options are determined. Here, there’s the create statements, here there’s the script if not exist. I can toggle that owner off as well. If we want to maintain the owner, we can certainly script the owner. If we uncheck that, you’ll notice that now the DBO is removed from that. Here we’ll retain that and again, if not we could either retain or change owner. At any time I can pick a different owner if I’d like.

06:25

Truncate Table

Then we have some other options. Truncate table as well as some data movement options if needed. Lastly, then we have the ability to update the stats. So here is our options template. If we’d like to save this, let’s go ahead and Skill Sprint project creation. So we’ll save this. Potentially if we have a generic set of options that we’d like to use each time we create a new project, we can save that in our options template. We’re able to just drop this down and select this and we have all those options then saved for us. Again, a nice usability feature to make it quicker to get started. Lastly, then we’re ready to finish. At this point if I show everything, show all, we’ll see Rapid SQL went through reverse engineered, populated the scripts. At this point I’m ready to move forward. I’ll press continue. Now here’s my prompt after we build the project.

07:34

Now I’m prompted with the dialogue asking me if I’d like to add it. In this case I’m going to select no. If I were to select yes, I’d be prompted with their username and password dialogue looks identical in my case to the visual source safe dialog. We’re seamless integration points there and then at that point it’ll drop everything into a new project called Skill Sprint underscore project within my VSS database. So we’ll go ahead and select no. Now we’re working with the project. At this point I’m ready to start. At any time I can double click the file. We’re going to open it up in an Iciqo window. I can make any changes. Maybe I wanted to add a comment. We can save that. Again, it’s saving it back to my working folder. Right, let’s go ahead, we’ll just open up, let’s open up the procedure that I have.

08:32

SQL Syntax Checker

So just easily just double clicking. Or if you’d like you can open it up and I seek a window and we could drag. Come on now, Scott. All right, not behaving for me today. Let’s just double click, we open it up this way, then we’re able to walk through, make any changes we need. For example, let’s go ahead and we’ll make a change. We have that. Let me look at that. That’s real time syntax checking right there, catching my air before I even put it in there. I forgot the where clause. So let’s go ahead. Now you’ll see that it was removed. Again, the Rapid SQL DB artist and usability feature coming in handy for me. We’ll go ahead and press save there and now we’re making changes. As we make changes, then we’re having them saved back to the project. Again, if this was an integrated one that was integrated with them version control, we see these denoted as such and we would be able to right click and check out or check back in.

09:44

When we walk through those wizards, the dialogues, they’re going to be presented to us just as they would if you were working natively with the tool, as well as having the other features to look at different versions and look at the history. Now, once we get to this step, let’s say for instance, we finished our project, we’re ready to take it to the next step. Maybe we’re ready to pass it to our DBA to build this project for us. Or I’m sorry, to deploy the project. At this point, this is where we reference the project menu again. Now you’ll see everything else is lit up, right? Here we can build with selections only. You’ll notice that I have one selected. I can look at a build order. If it’s important for me to move the order, so I could make the broker table, maybe be the after office location, so I can pick and choose and move the order around, we can press okay, going back to the project menu.

10:52

Version Control

Here we can add new subfolders. If, for instance, I forgot to reverse engineer, pick another object and you wanted to create a subfolder for those objects, we could do that here as well. Adding more files is as easy as coming to this menu, selecting add files, and we can pull those files in as well. Look at some of the other options. Here we’re getting into version control again. We can add them to the version control. This point we can also I skipped over adding database objects. If we did want to go back and reverse engineer more objects, we could do that from within this menu as well. Here are the project properties, just letting us know some high level details about it associated with what data source and then what database. We’ll look a little further than here the properties of the actual file that I’m working with.

11:47

Again, here’s my last modified date. Lastly, there are those three for the new open and Close. At this point, let’s go ahead and build this project. So here I’m presented with the dialogue. Here’s my build target. It recognize what data source I reverse engineered from, so it knows it’s going to be targeted against the SQL Server 2012 at the Gym database. Here we can build the subfolders or not. It’s totally up to us to select. We can build the script and display in a SQL window. We could execute it immediately, or we could schedule it to be built later. Now the scheduling is going to require your machine to be on, because this is going to be a Windows task scheduler job that’s going to start up rapid SQL, execute the call to build the script. Today we’re going to just generate it right from within.

12:41

Right here we’re going to build and generate the SQL script. So let’s go ahead and press OK. And here is my script. So that was quick. We were able to bring that whole all of those objects back into one Excel file. I’m sorry. One SQL file. As you can see, every object that I brought in now is listed. So scroll down. We’re about 678 lines of code with all the objects that I’ve selected in my project to be brought over. Again, at this point, then I can save this master SQL file and push it over to the DBA, or push it over to another environment where I can execute and build the project on the database level. Let’s go back to options and let’s see if I can figure out where that option is for what, I’m drawing a blank right now, folks. There we are under general, under version control.

13:41

Here is where we can pick. You’ll see my available plugins that I have on my machine right now is Visual Source Safe. If I were to add the TFS client, I’d see that listed as well as any other wrappers for other version control systems. You would have the drop down here again, mines by default. Here’s our working directory. If I would select on Advanced, then we could actually connect not found. If I go to admin now, I’m in my source safe options. Again, this dialogue looks very similar because we’re calling natively. Again, we’re working natively between the verse control systems that were configured and you configure Rapid SQL or DBArtisan to work with. That’s a high level view and some details about how to work with projects in Rapid SQL. Now, if we take a look at DBArtisan real quick, I thought I’d close on this note just to show some of the DBAs and DBArtisan customers on the webinar.

14:54

DBArtisan and Rapid SQL

For DBArtisan, we would connect to a data source. It’s not mandatory, but I’m going to select it here. From the project tab, new project. Again, I’m working with XC six, and this build is nine, seven, five. Again, you’ll see the exact features that you saw earlier. If I did new project, you’ll notice here that DBAs project. I’m going to leave it in a root folder for right now. Let’s just do not initialize and we’ll press OK? Yes. Now you’ll notice that we have a project tab, our project window, if you will. We have the project on the left hand pane and then we have the rest of what we’re used to seeing. If we ever lose this, for instance, maybe accidentally click on the X, we’ll go to view and we can bring it back this way. Or just project an open project and we can go this method and bring it back.

16:02

Again, multiple ways to work with DBArtisan with projects, as well as Rapid SQL. Working with projects. Thank you.