Dbeaver Azure Sql



-->

Create a JDBC Data Source for SQL Server Data. Follow the steps below to load the driver JAR in DBeaver. Open the DBeaver application and, in the Databases menu, select the Driver Manager option. Click New to open the Create New Driver form. In the Driver Name box, enter a. DBeaver is a free and open source universal database management tool for developers and database administrators. With DBeaver, you are able to manipulate your data like in a regular spreadsheet, create analytical reports based on records from different data storage, and export information in an appropriate format. For advanced database users, DBeaver suggests a powerful SQL-editor, plenty of. DBeaver Universal Database Tool Free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.

This article discusses issues when using the Microsoft JDBC Driver for SQL Server to connect to an Azure SQL Database. For more information about connecting to an Azure SQL Database, see:

Details

When connecting to an Azure SQL Database, you should connect to the master database to call SQLServerDatabaseMetaData.getCatalogs.
Azure SQL Database doesn't support returning the entire set of catalogs from a user database. SQLServerDatabaseMetaData.getCatalogs use the sys.databases view to get the catalogs. Refer to the discussion of permissions in sys.databases (Transact-SQL) to understand SQLServerDatabaseMetaData.getCatalogs behavior on an Azure SQL Database.

Connections dropped

Dbeaver Azure Sql Server

Dbeaver

When connecting to an Azure SQL Database, idle connections may be terminated by a network component (such as a firewall) after a period of inactivity. There are two types of idle connections, in this context:

  • Idle at the TCP layer, where connections can be dropped by any number of network devices.

  • Idle by the Azure SQL Gateway, where TCP keepalive messages might be occurring (making the connection not idle from a TCP perspective), but not had an active query in 30 minutes. In this scenario, the Gateway will determine that the TDS connection is idle at 30 minutes and terminate the connection.

To address the second point and avoid the Gateway terminating idle connections, you can:

  • Use the Redirectconnection policy when configuring your Azure SQL data source.

  • Keep connections active via lightweight activity. This method is not recommended and should only be used if there are no other possible options.

To address the first point and avoid dropping idle connections by a network component, the following registry settings (or their non-Windows equivalents) should be set on the operating system where the driver is loaded:

Registry SettingRecommended Value
HKEY_LOCAL_MACHINE SYSTEM CurrentControlSet Services Tcpip Parameters KeepAliveTime30000
HKEY_LOCAL_MACHINE SYSTEM CurrentControlSet Services Tcpip Parameters KeepAliveInterval1000
HKEY_LOCAL_MACHINE SYSTEM CurrentControlSet Services Tcpip Parameters TcpMaxDataRetransmissions10

Restart the computer for the registry settings to take effect.

The KeepAliveTime and KeepAliveInterval values are in milliseconds. These settings will have the effect of disconnecting an unresponsive connection within 10 to 40 seconds. After a keep alive packet is sent, if no response is received, it will be retried every second up to 10 times. If no response is received during that time, the client-side socket is disconnected. Depending on your environment, you may want to increase the KeepAliveInterval to accommodate known disruptions (like virtual machine migrations) that might cause a server to be unresponsive for longer than 10 seconds.

Note

TcpMaxDataRetransmissions is not controllable on Windows Vista or Windows 2008 and higher.

To perform this configuration when running in Azure, create a startup task to add the registry keys. For example, add the following Startup task to the service definition file:

Then add a AddKeepAlive.cmd file to your project. Set the 'Copy to Output Directory' setting to Copy always. The following script is a sample AddKeepAlive.cmd file:

Appending the server name to the userId in the connection string

Prior to the 4.0 version of the Microsoft JDBC Driver for SQL Server, when connecting to an Azure SQL Database, you were required to append the server name to the UserId in the connection string. For example, user@servername. Beginning in version 4.0 of the Microsoft JDBC Driver for SQL Server, it's no longer necessary to append @servername to the UserId in the connection string.

Using encryption requires setting hostNameInCertificate

Prior to the 7.2 version of the Microsoft JDBC Driver for SQL Server, when connecting to an Azure SQL Database, you should specify hostNameInCertificate if you specify encrypt=true (If the server name in the connection string is shortName.domainName, set the hostNameInCertificate property to *.domainName.). This property is optional as of version 7.2 of the driver.

For example:

See also

Dbeaver Azure Sql Login

I’ve recently started a job where I’m working with multiple database systems. Before, I’ve worked primarily with SQL Server, so I’ve always had SSMS to use for a querying and maintenance tool. I used Azure Data Studio for a while to query SQL Server and Postgres, but it’s still very lean on features, and it is missing things in order to work with Postgres effectively. I was hoping to find one tool to query all the database systems that I work with.

I came across the DBeaver database tool and was impressed with all of the features that it delivers. There are a few different versions, but I tried out the free Community edition as well as the paid Enterprise edition.

Dbeaver Azure Sql Server Connection

The Community edition includes the ability to connect to multiple relational database systems (including SQL Server and Postgres), including creating and querying objects. The free tier also includes a good ER Diagram tool. You can also import data from CSV files, and export data to several formats (including CSV, text and JSON). The export can also script data to a SQL script, which I’ve found very useful.

Dbeaver Azure Sql Connection

The Enterprise edition includes the ability to connect to selected NoSQL databases. I work with MongoDB, so this allowed me to access any database system that I worked with. Another useful feature was the schema comparison tool, which I’ve used with Postgres. There are additional features included, like data comparison and mock data generation, but I haven’t made use of those features yet.

Dbeaver Azure Sql Client

I’ve been using DBeaver Enterprise at work, and I definitely would recommend this tool. It’s been a bit of a learning curve to learn my way around and learn a whole new set of keyboard shortcuts (I miss being able to run a query with F5, instead of of the two key Control + Enter in DBeaver), but I like being able to manage any database at work from one tool.





Comments are closed.