My favorite feature is the possibility to create sample data. With this feature, it is possible to generate sample data. You can populate tables with sample data by right clicking and selecting the option SQL Assistant>Generate Test Data:

You can specify the number of rows of the sample data (in this sample 1000 rows) and then press the Generate button to generate the sample of data:

The software will give you the choice to store the project settings:

If you open the table, you will be able to see that 1000 rows were created:

This is extremely useful to make tests in our apps with several rows and test the performance with real amounts of data when we connect to databases.

Generate scripts to insert data

With SQL Assistant, you can generate a script to insert all the data. This is a fast way to export the data for the tables or to generate the insert code in a fast and easy way. To do that, in the context menu, go to SQL Assistant>Script Data:

Select the tables that you want to use to generate scripts:

You will be able to see the T-SQL scripts created with the insert data code. This is a very simple way to populate empty tables or export data:

Generate scripts to create tables, views and other objects.

The script DDL option is used to define objects. Generate the objects' code for tables, views and stored procedures:

You can select database objects that you want to create generating T-SQL scripts:

As you can see, the scripts are generated to create tables or other objects using few clicks:

Generate stored procedures

The option Generate SQL Code allows creating stored procedures based on the tables. Chose a table with data and select the option to test:

As you can see, the code is generated to delete, select and update the data when the generate button is pressed:

What you will have is 3 stored procedures to delete data from a table with a given primary key specified as a parameter. Another procedure will get the columns with a primary key specified and finally, the third procedure updates the data:

The stored procedures are very important to integrate the tables with the software applications. It keeps a safer code and it is harder to attack with sql injections to the database.

Export data to Excel (xls, xlsx, csv, xml, JSON, TBL.

Other important feature is the export data option. SQL Assistant can export the data to different formats like XLSX, XLS, CSV, JSON, TBL, XML:

If we export to Excel, we can see the tables with a format specified by default. It is possible to customize the format according to your preferences:

To export to different formats, it is necessary to have MS Excel installed.

Diagram creator

It is also possible to create diagrams of tables and views using the Create Database Model option:

You can select the tables that you want to include in the diagram:

Also, you can include the views:

It is also possible to import the stored procedures in the diagram:

To end the diagram wizard, we have the diagrams that can be customized according the customer preferences:

Unit tests
The unit tests allow to test the right functionality of the database objects. We will press the right button on a table and specify the option SQL Assistant>Unit Tests:

Unit test creates insert, select, update and delete code. It is necessary to edit the code to run it:

Other features

There are several other features like the Data load where you can load data to test a heavy load, you can compare the objects of 2 databases, compare the data of 2 tables and more.

Conclusions

As you can see SQL Assistant is an incredible tool. This tool was tested here in SQL Server. However, this software can be used in MySQL, SAP, DB2, PostgreSQL, Amazon, Teradata, IBM Netezza, MS Access, SQLite and Oracle.

This software also includes intellisense features that helps a lot to write T-SQL code in an intellegent and efficient way.

Tuesday, April 11, 2017

I talked about using Vembu DBR to take backup from Windows Workstation machines in another article; now, I'll show you how to use this amazing tool to backup Windows Server machines. So, let's begin.

Requirements

1 Windows Server machine - Server: As Vembu BDR uses the classic Client-Server model, we will need a machine that will work as the Server. Vembu BDR supports:

Windows Server 2012 R2 (64 Bit)

Windows Server 2008 R2 (64 Bit)

Windows Server 2012

Windows Server 2016

1 Windows Server machine - Client: We will need another machines to work as the Client. I will use a Windows 2008 R2 machine for this example.

Installation

Server

The steps to install the Vembu BDR Server are exactly the same that I pointed out in this article:

1. Download Vembu BDR from here and copy the installer to the Server machine.
2. Double click the installer to start the installation wizard.
3. Click yes to give permissions to the installer.
4. On the first wizard screen, click Next.
5. Accept the agreement and click Next.
6. In the Default Settings screen, you'll see a list of the settings which will be used for the installation. If you want to customize these options, check 'Let me customize the configurations' and press Customize. I will leave the option unchecked because I'll install the program with the default settings. So, click Install. Next, click Yes in the confirmation dialog.
7. Once the installation finishes, you'll see a last screen. Leave the two options selected and click Finish.
8. The Vembu Web Console will be opened. Use 'admin' as user name and password and then click Sign in.
9. Select the Time Zone corresponding to your location and clic Save.
10. Enter a Vembu BDR ID. This is a unique ID that will identify the Server machine. So, I'll name it testools_server. Then click Update.

7. Enter an ID for the Client. I used: 'testools_client_winserv'. Then click Next.

8. In the following screen, select Typical to install with the default options. Click Next.

9. Verify all the installation options and, if everything's OK, click Install to begins with the installation.

10. Once the installation finishes, leave the two check boxes selected and click Finish.

11. Login the web console with 'admin' as user name and password.

12. Select the time zone and click Save.

13. Click Yes in the confirmation dialog.

14. Enter the IP or hostname of the Server and click Connect. You'll see a success message:

15. In the next screen, click 'Install Vembu ImageBackup Disk image driver.'. That will install the driver for monitoring the disks. Wait for the installation to finish and the computed will be rebooted.

16. Click Yes in the confirmation dialog. Now wait until the machine is rebooted.

Taking the backup

1. Once the machine is rebooted, open the Web Console.

2. Access with 'admin' for user name and password.

3. Select the all the elements that belong to the disk and click Next.

4. Select the options of scheduling. I left all the options by default here. Then, click Next.

5. Configure the retention. I left all the default options here. Click Next.

6. Enter a name for the backup. I used 'testools_server_backup'. Then click Save the backup.

Monday, April 10, 2017

As time goes by, you may have noticed that accidents happen, especially when it comes to handling data. So, if you have a particularly important set of data in a machine, it's better to have a backup plan to avoid potentially critical problems. This time, I'll review a tool designed to take backups from different platforms: Vembu BDR. So, let's check the requirements for this demo.

Requirements

1 Windows Server machine - Server: As Vembu BDR uses the classic Client-Server model, we will need a machine that will work as the Server. Vembu BDR supports:

Windows Server 2012 R2 (64 Bit)

Windows Server 2008 R2 (64 Bit)

Windows Server 2012

Windows Server 2016

1 Windows machine - Client: We will need another machines to work as the Client. I will use a Windows 10 machine for this example.

Installation

Server

First, you'll need to install Vembu BDR in the Server machine. To do this, follow the steps:

1. Download Vembu BDR from here and copy the installer to the Server machine.
2. Double click the installer to start the installation wizard.
3. Click yes to give permissions to the installer.
4. On the first wizard screen, click Next.
5. Accept the agreement and click Next.
6. In the Default Settings screen, you'll see a list of the settings which will be used for the installation. If you want to customize these options, check 'Let me customize the configurations' and press Customize. I will leave the option unchecked because I'll install the program with the default settings. So, click Install.

You'll see a confirmation alert. Just click Yes. Now wait a little for the installation to finish. You can check the progress status in the installation window.

7. Once the installation finishes, you'll see a last screen. Leave the two options selected and click Finish.

8. The Vembu Web Console will be opened. Use 'admin' as user name and password and then click Sign in.
9. Select the Time Zone corresponding to your location and clic Save.

10. Enter a Vembu BDR ID. This is a unique ID that will identify the Server machine. So, I'll name it testools_server. Then click Update.

And that's it. If you see the following dashboard, it means that you successfully installed Vembu BDR in the Server machine.

Client

1. You'll need the Vembu client installer for Windows. Download it here.
2. Copy the installer to the Client machine.
3. Double click the installer.
4. Press Yes in the User Account Control alert.
5. In the first wizard screen, press Next.
6. Accept the agreement and press Next.
7. Add a Vembu ImageBackup ID. This is a unique ID that will identify the Client machine, so I called it testools_client. Then, press Next.
8. Select 'Typical' to install with the default options or 'Custom' to customize the options. For this example, select 'Typical' and press Next.
9. Now, click Install to start the installation
10. Once the program is installed, leave the 2 check boxes selected in the final screen and click
Finish.
11. Once the Vembu Web Console is open, enter 'admin' as user and password and press Sign in.
12. Select the Time Zone for the Client. be careful to select the same zone you selected for the Server. Then, click Save.

13. In the alert, click Yes.

14. In the 'Backup Server Details' screen, enter the IP or hostname of the Server and click Connect.

Taking the backup

1. (Continuing from the step that clicks Connect after entering the Server's IP or hostname) Now, you need to install the Disk Image Driver. The first screen after clicking Connect will show you a link to install the driver. Click on it.
16. Click Yes in the confirmation pop up. The you'll see the driver installing:
The machine will be rebooted after the installation is complete.
2. Once the client machine is done rebooting, open the Web Console again.
3. Access the console with 'admin' as user name and password.
4. Select all the elements that belong to the disk you want to backup. Then, press Next.
5. Configure the schedule for the backup. Since this is just an example, I left all the options with the default values. Then click Next.
6. Select the options for retention. In this case, I left all the default values. Click Next.
7. In Review Configurations, enter the name for the backup. In this case, I called it 'testools_backup'. Then click Save the backup.
8. Click OK in the confirmation dialog. Now you'll see a progress screen:
Wait some time (this depends on the system you are running this backup tool) and, finally, you'll see a success message:
And that's it! You can further verify the backup opening the web console in the Server machine and going to Dashboard:

Vembu BDR is a very convenient tool to take backups. What I liked the most from this tool is that it provides all the extras you need as you go on with the backup process. Once you set it up, it's very easy to use and understand. I encourage you to give it a try.