About STUDIO
EMS SQL Management Studio is a complete solution for database administration and development. The STUDIO is an integrated suite of powerful database management tools united in one easy-to-use work environment. Developers will appreciate its ability to create/edit/drop any database objects and will find DB comparison, data export/import and test data generation tools extremely helpful. Database migration, BackUp and Restore tools will be of great help to DBAs. As will be the Scheduler that will automate a sizeable part of DBA's work.
Indeed, in order to perform some simple task you can purchase a separate EMS utility or a few of them.Yet, if you purchase these utilities within the STUDIO, you receive additional functionalities. First of all, you'll be able to launch the STUDIO components from Task Scheduler. That is, you can set the order to run the tools in, set the parameters to be used, the time and/or time intervals for the task to be performed and go to play a bowling game:) The Task Scheduler will fulfill all your tasks, save the logs for the performed operations and will notify you by e-mail about the successfull/unsuccessful task completion. The Task Scheduler will help you to really automate the majority of your DB service tasks. Secondly, you'll be able to store all registered Databases in one repository. Thus, you will need to register your data source once and will be able to use it in all STUDIO tools. This will save your time and reduce an error chance to the minimum. The third equally important benefit is the pricing. Purchasing the tools within the STUDIO, you pay a lot less, compared to purchasing them as separate products. You will receive up to 40% off the original price!
1) You should either download the complete Studio package anew and install it on the same workstation as your original version, which will be overwritten. All settings will be saved.
2) Or, you should update SQL Studio components through SQL Studio Direct service (Navigate/SQL Studio Direct main menu item). A pop-up window will tell you about available updates. Click Yes to download these updates (Internet connection is a must). The Refresh icon on SQL Studio Direct toolbar serves the same purpose. You can also set the interval for Automatic polling of the network in search of updates (see the corresponding icon).
The update procedure performed either way will not require any additional registration.
There is a third possibility for updating SQL Manager component only. You can download the Studio edition of the tool from the corresponding SQL Manager download page. You would then need to unpack the archive in the SQL Manager subfolder within SQL Studio installation directory (e.g. C:\Program Files\EMS\SQL Studio for SQL Server\SQL Manager).
DB registration and connection
Yes, the STUDIO allows to work with an unlimited number of databases at the same time. Once the database is registered at the database source repository correctly, you are ready to use it.
The single registered database repository allows one database to be accessed with any STUDIO tools. If you earlier purchased our product suites (bundles) you had to enter the database information in every tool you used. That eventually led to massive time wasting and typing errors. Now the problem is solved in the STUDIO with the help of a single registered database repository.
To connect to a named instance of SQL Server 2005 or higher just enter instance name in after host name and back slash symbol in 'Host name' field of Database Registration Info form. (For example: my_server\my_instance.)
To connect to a named instance of SQL Server 2000 you need:
- Run SQL Server Client Network Utility (WINDOWS\\SYSTEM32\\cliconfig.exe) on client computer where STUDIO (particularly EMS SQL Manager for SQL Server) runs.
- Go to the second (Alias) tab of the Utility.
- Add a new server alias with Server Name in form <server_name>\\<instanse_name> and TCP/IP as Network library.
- Use the server alias name as host name in STUDIO.
Besides, the following factors can be the reasons for connection problems:
- You are trying to connect to SQL Server beyond your local network, through firewall (See Q. "I work behind a firewall (router). Can I use Studio to manage a remote SQL Server?"
- Wrong login/password. Either you use SQL Server Authentication, but this authentication type is vorbidden on the server, or you use Windows Authentication to connect to the server, but the login that corresponds to your domain name is absent on SQL Server.
- MDAC works incorrectly (See Q. "How to reinstall Microsoft Data Access Components (MDAC)?".
- SQL Server works on a non-standard (other than 1433) port (See Q. "I can access my server via an alternative port. I have searched for a setting in the program to alter the connection port, but have not yet been able to locate this. How would I go about altering this port?".
- There are no enabled client connection protocols. Or all enabled client connection protocols are disabled on server side. Use SQL Server Client Network Utility (WINDOWS\\SYSTEM32\\cliconfig.exe) for connection to SQL Server 2000 or lower or SQL Server Configuration Manager to enable appropriate protocols.
If you use Windows XP with SP2, please follow the instructions below.
----------------------------------------------------------------------
Right-click on C:\Windows\Inf\mdac.inf and choose "Install".
When prompted for the XP SP2 media, point to the i386 folder in one of these places:
1. The \i386 folder on the XP SP2 CDROM. If you are unlucky enough to have a CDROM that has XPSP2.EXE in the root of the CDROM, and no \i386 folder, then you will have to create an i386 folder using a command like this:
XPSP2.EXE /X:C:\XpSp2
Then point to C:\XpSp2\i386.
2. The \i386 folder on a slipstreamed XP SP2 installation CDROM.
When prompted for the XP installation media, point to the i386 folder in one of these places:
1. C:\Windows\ServicepackFiles\i386 (it may not like this location, if not, go to the next one)
2. The \i386 folder on your XP installation CDROM.
This reinstalls and repairs MDAC.
To connect to SQL Server 2005 on alternative port do the following:
- Run SQL Server Configuration Manager on client computer where STUDIO runs.
- Expand the SQL Native Client Configuration section.
- Select the Aliases section.
- Add a new server alias and set server name, port number and TCP/IP as Network library.
- Use the server alias name as host name in STUDIO.
To connect to SQL Server 2000 on alternative port do the following:
- Run SQL Server Client Network Utility (WINDOWS\\SYSTEM32\\cliconfg.exe) on client computer where STUDIO runs.
- Go to the second (Alias) tab of the Utility.
- Add a new server alias and set server name, port number and TCP/IP as Network library.
- Use the server alias name as host name in STUDIO.
Yes, but to do this, you should open the port where SQL Server works (1433 by default) on your firewall and on the firewall of the remote network.
Object manipulation
You can work with any objects in SQL Server databases such as tables, fields, indices, views, stored procedures and functions, triggers, UDFs and events.
- Limit options in table and view editors. The “Select all records of a table” option will enable you to see all table records without extra references to the server, yet in case of large tables or low speed connection channel the data may be fetched with huge delays and the incoming traffic might grow considerably. This mode is recommended when working with local databases or in a private network. The “Select only” mode restricts the maximum number of records returned after the query. Man can't process a massive amount of information at once. Hence, we came up with this mode. To request and display the next portion of data use the Next button in the Data Grid toolbar. This mode speeds up table data viewing considerably, prevents hanging and connection timeout. It is recommended to work with large tables, in case of low speed connection channels and when the traffic volume is of importance. This is a default mode. With this mode on the “Use SQL sorting” and “Use SQL filter” options come really helpful. At the "Grid/Data Options" tab:
- Default Grid Mode. This option defines whether the requested rows will be loaded in the Grid all at once ("Load all rows"), or in parts ("Load visible rows") as the user scrolls down table data. The first mode increases the query opening time but speeds up scrolling. In the second mode the query opens very fast but there might be delays when navigating the grid.
- Use SQL sorting in data view. With this option enabled, when the user sets data sorting a new request is sent to the server, and the result is shown anew in the Grid. This option is very helpful with the “Select only” mode enabled, but it increases the traffic. When this option is disabled, the sorting is made on the client computer without any references to the server but only loaded data are sorted. That is, if you set "Select only 1000 records", then only these records will be sorted.
- Use SQL filer in data view. With this option enabled, the data will be sorted on the server to which a new request is sent, and the result will be shown anew in the Grid.
This option is also helpful when the “Select only” mode is on, but it insreases the traffic. When this option is disabled, the sorting is made on the client computer without any references to the server but only loaded data are sorted. That is, if you set "Select only 1000 records", then only these records will be sorted.
We recommend to set the following option values to achieve maximum efficiency when working with large tables:
- Select only – On
- Load visible rows – On
- Use SQL sorting – On
- Use SQL filter – On
Please, find the option "Convert created object's names to lower cases" in Options/Environment Options/Object Editors dialog and disable it if necessary.
You can customize all display formats: integer, float, date, time and date/time in the Environment Options window. You can do it from the Main Menu Options/Environment Options/Grid/Color&Formats.
The ‘DDL‘ tabs of the Table Editor and UDF Editor are read-only. They display the SQL text of the operations you carry over the table on the tabs ‘Fields‘, ‘Indices‘, etc, or over the UDF. To modify this text you can copy it to the clipboard and modify it using SQL Script Editor.
You probably have the "Autofit column widths" option enabled in the "Grid" bar of Options/Environment Options dialog. Try to disable it.
Query
Yes, the STUDIO has a powerful visual query builder that allows a newbie build a complex queries while drasticly save time for advanced users. All you need to do is to specify the tables that will be used in the query, their relations and the data you want to get. The visual query builder will create a text of the query for you. You will only need to run it. You can see the result of your query and make corrections if nesessary. The changes can be made from the visual builder or from the text editor. As changes made in visual query builder, the text editor will display them too and vice versa.
Yes, the visual query builder can build complex queries including UNION operator and subqueries.
Yes, of cource. The STUDIO can save the needed query to following ways:
1. ALL executed queries are automatically saved in the History. You can view the History and execute or edit any query you need.
2. You can add the frequently used queries to the list of favorite queries.
3. You can save any query as an SQL-script into file on your hard drive and load it when needed or execute it with a command line SQL script tool.
4. You can save your query with the chart. In this case after the query is loaded into STUDIO, the chart will be displayed the way it has been created.
5. the STUDIO allows to save the query chart as a bitmap image.
Yes, of cource. The STUDIO has a powerful tool for exporting your data (including the query results). STUDIO can export info MS Access, MS Word, CVS, XLS, HTML, RTF, DBF, XML and others. To ease the exporting procedure the STUDIO has the Export Data Wizard.
Report Designer
To create a report you can use the Create Report Wizard ("Create-->Report") or Report Designer Tool ("Tools/Report Designer"). To create a simple report based on the query you need to choose "Master Data band" on the second step of the wizard, double-click on it or click the Edit button and specify the query for your report. After that you need to tune the non-nesessary options in other steps of the wizard and press Finish.
To create a report based no the query you can use the Create Report Wizard. You need to choose the Create-->Report, then choose Master Data band on the second step of the wizard, doubleclick on it or click the Edit button and specify the query for your report. After that you need to tune the non-nesessary options in other steps of the wizard and press Finish.
Yes, open the report by double-clicking on it in the DB Explorer window and choose "Save report as" in the navbar or toolbar and specify the format you want to save to in the "Save as type" field.
Data manipulation
What is the difference between the Export/Import functions in STUDIO and SQL Manager for SQL Server?
The Export/Import tools in STUDIO includes additional features, which are not available in SQL Manager for SQL Server.
1. Export/Import data from/to several tables at once.
2. Export/import data from/to tables selected from different databases on one host.
3. Command line utility to automatically export/import data using the STUDIO scheduler.
4. A single registered database repository.
You can use Import Data Wizard for this purpose. It will help you to set all the necessary import parameters. Moreover, you can save these settings as a template and use them in the future. Besides CSV file you can also import from MS Excel, MS Access, DBF, XML and TXT files.
Right click on the database you need in DB Explorer and click on "Database Registration Info..." item in popup menu (you can also find this item in main menu "Database"). The Database Properties form will be opened.
Click on the "Directories" tab.
In the "Default Directory for Export Data" section you can choose the default directory for export file.
We recommend that you use Microsoft Jet 4.0 OLE DB Provider to connect to your MS Access database.
ODBC (Open DataBase Connectivity) is an open standard interface to access databases developed by Microsoft. The most database management systems have their own ODBC driver or the one developed by other software producers.
To migrate from ODBC data source you need to use Data Pump for SQL Server tool. You can start Data Pump from STUDIO by choosing Data Manipulation tab and then clicking on Data Pump for SQL Server. Or go to Start->Programs->EMS->SQL Studio for SQL Server->Data Pump for SQL Server->Data Pump for SQL Server.
When working with the utility you should use OLE DB Provider for ODBC Drivers to connect to your ODBC data source.
With the help of Data Pump for SQL Server you can migrate from any databases to which you can connect over OLE DB Provider (for example Oracle, MS Access, DB2, etc.) and ODBC Driver (MySQL, PostgreSQL, Interbase, Firebird, dbase files, etc.)
Yes, of course. When generating test data, STUDIO takes into consideration all relations and restrictions that already exist in your database.
Data analysis
There is a Database Comparer (DB Comparer) tool in STUDIO that can be used for this purpose. Its interface is simple and user-friendly, but the results of its work are quite impressive! You need only to choose two databases, whose metadata you would like to compare and set some additional parameters, such as database objects to compare, direction of synchronization and others. The result of DB Comparer's work will be a list of differences in the metadata of these two databases and a script, with the help of which you can synchronize the databases. Besides, you can synchronize each difference separately, using only the needed changes in the required order. Do you remember how much time you spent searching for the single field added to the table two months ago? :)
You can use DB Comparer for SQL Server tool to synchronize the structure of your databases.
To launch DB Comparer from STUDIO, please, choose Data Analysis tab and click on DB Comparer for SQL Server, or go to Start->Programs->EMS->SQL Studio for SQL Server-> DB Comparer for SQL Server-> DB Comparer for SQL Server.
To synchronize the data, you can use Data Comparer for SQL Server tool.
To launch Data Comparer from STUDIO, choose Data Analysis tab and click on Data Comparer for SQL Server, or go to Start->Programs->EMS->SQL Studio for SQL Server-> Data Comparer for SQL Server-> Data Comparer for SQL Server.
Yes, for this purpose there are console versions of our utilities - DB Comparer for SQL Server Console Application and Data Comparer for SQL Server Console Application. All console tools support configuration files (templates) that are created in GUI versions of the programs. The template file contains all the infomation necessary for working with the console version of the tool. With the help of console version and the template file you can easily perform synchronization from a command line without setting a greate number of parameters manually.
This possibility is not supported by our utilities as they allow synchronizing data and metadata only between one server type, that is between SQL Server and SQL Server.
BackUP/Restore
1) Using Backup Database Service. It can create a full or differential backup of a database, as well as a backup of transaction log. You can create a backup of the whole database or of some separate files. This method is highly reliable. While a backup is created, the users can continue their work with the database – the data integrity is ensured. A backup can be created to a logical backup device, a file, a tape or to a named pipe. In all the cases the device should be placed on the server or in the local network where the server is installed. The path to the device is defined from the server side. To use the network devices, SQL Server Service should be launched from the user name that has an access to the device. To call this service, please, open Database Management tab in STUDIO and choose Backup Database. To call this service from SQL Manager for SQL Server you can use Services/Backup Database in the main menu. To restore database or transaction log from backups created in this way, use Restore Database Service. You can restore a backup to an existing database or a new one. In the second case you should define new names for the database to restore.
2) Using DB Extract for SQL Server or Extract Database Wizard of SQL Manager for SQL Server. These tools create backups of databases or user-defined objects in a form of SQL script. The generated script can contain SQL Utility statements, Data Definition statements and Insert Data commands. The main advantage of creating backups with DB Extract for SQL Server is the possibility of saving database structure and data as a whole as well as partially. This method also allows getting a database backup from a remote PC. The weak point of this method is much time and traffic that are required for creating backups of large databases. To call DB Extract open Data Analysis tab in STUDIO and choose DB Extract for SQL Server. In order to call Extract Database Wizard, please, open SQL Manager for SQL for SQL Server and choose Tools/Extract Database. To restore a database from created in this way script you can just execute this script on SQL Server server. You can use SQL Script for SQL Server or SQL Script tool in SQL Manager for SQL Server.
3) Using Data Export for SQL Server or Data Export Wizard of SQL Manager for SQL Server. This method can be used for creating backups of data from any table type. It is useful when you want to save only data without the structure. We recommend that you use CSV format to create data backups. To call Data Export, please, open Data Manipulation tab in STUDIO and choose Data Export for SQL Server. In order to call Data Export Wizard from SQL Manager for SQL Server, open your table on Data tab and choose Export Data. To restore data from created in this way file you can use Data Import for SQL Server or Data Import Wizard of SQL Manager for SQL Server.
1) You can restore a backup file created by Backup Database Service on any server that has the same or higher version than the one where this backup was created.
2) You can restore a backup file created by DB Extract for SQL Server or with the help of Extract Database Wizard of SQL Manager for SQL Server on any server that has the same or higher version than the one where this backup was created.
3) You can restore the data backed up with the help of Data Export for SQL Server or Save Data Wizard of SQL Manager for SQL Server to a table of any SQL Server server version.
Backup Database Service can perform the following:
- create a backup of a whole database;
- create a differential backup of a database;
- create a backup of separate files and groups of files;
- create a differential backup of separate files and groups of files;
- create a backup of a transaction log. This method cannot be used for creating backups of separate database objects or schemes. A backup is always saved on the server. If backups of transaction logs are available, you can restore a database at a definite time moment.
DB Extract for SQL Server or Extract Database Wizard of SQL Manager for SQL Server creates backups either of whole databases or of only separate objects or schemes in a form of SQL script. The generated script can contain SQL Utility statements, Data Definition statements and Insert Data commands. A backup is savedon the client side. You can restore data only at the time when this backup was created. Thus, Backup Database Service uses SQL Server capabilities to create database backups, it is highly reliable, but it cannot place backup files in any client directory. It is recommended to use this method for backing up a whole database. The main advantages of creating backups with the help of DB Extract for SQL Server are that you can save structure and data of some specific database objects or schemes and that backup copy is created on the client side. The weak point of this method is much time and traffic that are required for creating backups of large databases, as well as lower reliability.
Template management
Templates in STUDIO are intended for storage of the parameters necessary for running STUDIO tools. With the templates you will be able to repeatedly perform one and the same operation using STUDIO tool without need to enter each time 10-20 wizard parameters. The parameters stored in a template can be easily modified or you can create a new template based on the existing one.
Template is a file that can store necessary parameters in certain format. Template files can be located anywhere in your PC if only you remember their location :) To simplify the task of template storage and search in STUDIO there is function Template Manager. The Manager allows you to register the necessary templates in STUDIO and not to worry anymore about the place where you saved them to. When you create a new template in STUDIO the Template Manager automatically remembers its location on the disk. All the registered templates are grouped by the tools they are intended for and will be at your disposal at any time. To run a tool with the selected template you will just need to double-click the template.
The Template Manager allows you to perform both operations: you can either delete a template from the registered template list only, or delete it also from the computer disk.
To create a template for the Import tool you should run the Import tool (Main Menu/Run/DataImport). Then you will need to fill in all the necessary import parameters using the Next button. A the last wizard step you should indicate where and under what name you would like to save the newly created template using button Tools/Save Template.
Scheduler
1. Create new Task.
2. Create 4 steps in the Task (Export, Import, BackUp, BackUp). When creating these steps you will need to indicate in what template files are the necessary tool parameters saved.
3. Create Scheduled Task in the Scheduler that will use the Task you've just created (1).
Yes. When creating/editing each Task Step you can define which step should go next. Moreover, for each of the three processed events (successful step termination, erroneous step termination, step termination by time-out) you can indicate a separate variant of next Task operations.
To solve such conflicts in every Task Step you can define its time-out. If a Step is being executed longer than it's defined in the time-out, the Step will terminate with the "by time-out" event, and the Task execution goes on according to the algorithm you set.
To work with the Task Scheduler you need to run the STUDIO Agent (a shortcut in the task bar). And also your computer should be running during all the time when the tasks will be launched.
Log management
Yes. STUDIO has a convenient tool to work with log files. While creating a Step in the Task you can specify the location of the log file of application to be executed. When choosing the tools from STUDIO the location of the log file is filled automatically. Log files of the performed steps are saved in the internal STUDIO database and are availbale in the Navigation/Logs menu.
Log files are kept in STUDIO during an unlimited period. You can delete the unnecessary log files manually in STUDIO (Logs tab).
Localization
In the current version STUDIO interface supports the following localizations: English, German, French and Russian.
