Author: {coding}Sight

A community platform for IT specialists

DevOps Solutions for Database Development Automation

{coding}Sight Leave a comment
Total: 16 Average: 3.4

What is DevOps?

Database DevOps is a philosophy that comprises the following concepts:

  • automating the development, deployment, documentation, testing, and monitoring processes between software developers and operations engineers
  • integrating the development and operations processes to efficiently synchronize, validate, manage, and apply database changes

The Database DevOps practice is focused on improving the efficiency of database management. It helps with streamlining the process of deploying and improving databases by giving the ability to automate many aspects of the database lifecycle, thus speeding them up and simplifying them.

Therefore, DevOps is the best practice to unify development, operations, and quality assurance processes. Read More

10 Best MySQL GUI Tools

{coding}Sight Leave a comment
Total: 75 Average: 4.5

MySQL is among most widely-used and popular database technologies, so quite a lot of tools have been created in order to make the processes of designing, creating, and administering databases easier and more convenient. However, with all of the possible options, it can be difficult to choose one tool that will fit your requirements best. That is why I prepared a list of 10 MySQL GUI tools that, in my opinion, are the best solutions both for developers and DBAs alike.

Let’s take a closer look at them. Read More

How to Automate the Process of SQL Server Database Schema Synchronization

{coding}Sight Leave a comment
Total: 4 Average: 2.8

The goal

Keeping two databases in sync is a task we often encounter when working on SQL database development and maintenance. One particular case is this – the versions of a database present on testing and development environments need to be consistently synchronized to make sure that tests are running on the most recent version. This should be done by detecting schema changes in the development version of a database and then automatically synchronizing them with the version from the testing environment on a scheduled basis.

How to achieve it

We’ll look into how automated synchronization of two SQL databases can be achieved by combining a schema comparison tool with PowerShell scripts and scheduling. Read More

Bulk Insert Data Files Into SQL Server

{coding}Sight Leave a comment
Total: 1 Average: 5

According to Wikipedia, the bulk insert is a process or method provided by a database management system to load multiple rows of data into a database table. If we adjust this explanation to the BULK INSERT statement, the bulk insert allows importing external data files into SQL Server.

Assume that our organization has a CSV file of 1.500.000 rows, and we want to import it to a particular table in SQL Server to use the BULK INSERT statement in SQL Server. We can find several methods to handle this task. It could be using BCP (bulk copy program), SQL Server Import and Export Wizard, or SQL Server Integration Service package. However, the BULK INSERT statement is much faster and potent. Another advantage is that it offers several parameters helping to determine the bulk insert process settings.

Let’s start with a basic sample. Then we will go through more sophisticated scenarios.

Read More

Counting references to a record in a table via Foreign Keys

{coding}Sight Leave a comment
Total: 3 Average: 3.7

I have recently needed to solve the task for my own purpose: to calculate the number of external records linked by a foreign key for each record in a table (File). The task was solved for the specific structure of the File table, but if necessary, the solution can be reworked to a universal one.

I’ll clarify that the solution was developed for an unloaded database, without millions of records and an every minute update, so there was not much concern about the performance.

Read More

XML performance tips

{coding}Sight Leave a comment
Total: 6 Average: 3.5

Parsing data from XML using XQuery is a routine practice. In order to do this most effectively, little effort is required.

Suppose we need to parse data from the disk file with the following structure:

<tables>
<table name="Accounting" schema="Production" object="Accounting">
<column name="Date" order="3" visible="1" />
<column name="DateFrom" order="5" visible="1" />
<column name="DateTo" order="6" visible="1" />
<column name="Description" order="4" visible="1" />
<column name="DocumentUID" order="1" visible="0" />
<column name="Number" order="2" visible="1" />
<column name="Warehouse" order="7" visible="1" />
</table>
</tables>

Use BULK INSERT, if you need to read data from a file:

SELECT BulkColumn
FROM OPENROWSET(BULK 'D:\data.xml', SINGLE_BLOB) x sample xml file

Read More