Developers ensure database accuracy

• Download a fully functional, free trial.

Introduction

With products that range from radial seal rings for automobiles to steel frames for bathtubs, DTG Development and Technology has its hands full with its core manufacturing business. The company turns its database needs – including systems to handle customer data, orders and inventory management – over to iCOMcept, a software consultant and Microsoft partner in Aachen, Germany. iCOMcept relies on Red Gate Software's SQL Data Compare and SQL Packager, and Visual Studio .NET to streamline operations and ensure database accuracy.

iCOMcept began providing IT services and software programming for DTG in 1997. The first software solution used Access 97 as both the front-end GUI and the back-end database.

"Before we started using SQL Data Compare and SQL Packager, updating and synchronizing the database was done by hand, which was very time-consuming," says Thomas Stensitzki, IT consultant at iCOMcept. "And, because the coding was done manually it was very difficult to program error-free code."

From four hours to 30 minutes

As DTG's business grew and evolved, iCOMcept found that new software tools were also needed to manage the data and keep operations running smoothly. In 2002, iCOMcept upgraded DTG's database to Access 2000, but still faced performance issues. Switching to a SQL Server database on the back end, while still using Access 2000 as the front-end GUI solved some of those issues, but iCOMcept still faced problems when running Access. The company realized that only a completely new front-end database could optimize the application.

In January 2004, DTG began using .NET programming for the front-end, SQL Data Compare for comparing and synchronizing data from its SQL Server databases, and SQL Packager to package databases as executables. The transformation was done without interrupting DTG's business.

"Immediately we saw a great improvement in our ability to perform fast and accurate database updates due to software changes," says Stensitzki. "Before using SQL Data Compare and SQL Packager, a normal update and comparison took about four hours. An update can now be done within 30 minutes. This allows us to perform database updates during working hours, instead of being limited to updating overnight."

Easy comparison and synchronization

iCOMcept uses two SQL Servers with three databases – one for development, one for staging and one for production. The SQL databases store SharePoint portal configurations and data, website configurations and data (CMS), intranet information, and application data.

Frequent updates to DTG's database are required as new features and functions are added to the .NET front-end application.

"The ease-of-use of the generated .NET project in SQL Packager is crucial to me as a programmer," says Stensitzki. "This can then be easily integrated into the .NET project of the customer's software."

SQL Data Compare enables iCOMcept to easily compare the contents of databases and synchronize the development, staging and live databases. Data on the staging and development databases are synchronized on a weekly basis in order to avoid any difficulties in checking accounting function and stored procedures.

"Updating accounting data is a very delicate procedure," says Stensitzki. "Before using SQL Data Compare, we ran into quite a few problems because we did not use transactions."

The production database is first verified using iCOMcept's local client software installation. The production database on DTG's SQL Server then also has to be updated. In most cases, this comes with an update of the client software itself, which is updated automatically from a local web server.

The database structure is updated remotely by iCOMcept using a VPN connection to DTG's LAN. When a new update has been installed, iCOMcept uses a testing client to check the software update and the new database structure.

SQL Data Compare is used to keep lookup tables identical between DTG's production server and iCOMcept's servers. This also allows stored procedures to be tested using real data on iCOMcept’s local development server, without actually testing on the live system.

With the help of iCOMcept and programming tools such as .NET, SQL Data Compare and SQL Packager, DTG Development and Technology can be assured that its crucial customer and inventory data is safe and accurate.

Further information

For more information on SQL Packager, SQL Data Compare and other products by Red Gate Software please visit our website.  To see how well these products can work for you too, download a fully functional, 14-day trial.