Microsoft Entwicklung, Datenbank-Entwicklung und Datenbank-Administration, Webportale Entwick

This project is about storing data from home systems for generating solar energy. The energy generated in this way is stored in the power grid. The systems are queried periodically and the data is transferred to the central database via a structure of four SQL servers. Due to the large amount of data, the data storage had to be changed from row store to column store. My job is to carry out the SQL server maintenance and administration work. In addition, I develop new logics based on new business requirements. In addition, a complete performance monitoring was set up on the instances and the SQL servers were re-dimensioned based on corresponding evaluations. The SQL servers were relocated to a third-party provider once the dimensions had been defined. As a result, all objects of the various databases and instances had to be migrated to the new SQL server
 

• Development of stored procedures and functions for migrating data from operational rows
• Migrate tables to partition column archive tables automatically
• Develop other stored procedures and functions to automatically migrate data during migration to data warehouse
• Execute the SQL queries
• Develop the stored procedures and functions, test and optimize them optimizing them
• Execute the hard SQL queries
• Updating and converting historical data in Datawarehouse according to the new business rules
• DBA administration of MS SQL Server databases
• Change the database server setting to avoid some of the wait events
• Maintenance of the indexes
• Develop a concept for database server optimization
• Change MS SQL server performance
• Monitor job history, server logs, server performance and troubleshooting
• Executing the backup strategy

The project is a data migration project. The data from the insurance company Uniqa is to be migrated to a new system. This migration is carried out using very complex SSIS processes, among other things. It should be noted that this involves large volumes of data. For this reason, the migration and process processing of the SSIS processes is being carried out in parallel. Highly complex mappings must also be carried out and implemented. This in accordance with business requirements. Individual tasks are defined and implemented using Jira tickets.

Another complicating factor is that the implementation takes place in a larger team and that only one SQL server and only one instance is available for all team members. For this reason, individual deployments to production must be carried out very carefully and in consultation with the other team members. In the following, I will show you the areas in which I carried out corresponding tasks during implementation:

 

• Create a new SSIS package for importing data into tables from CSV files
• Adding new fields to tables
• Create SQL scripts and update new fields
• Integrating fields for building new sources
• loading mapping tables
• Customize CSV for loading mapping tables into the database
• Integrate new mapping tables according to business rules
• Change mapping tables according to business rules
• Changes in SSIS packages according to tasks
• Integrate and modify clusters for parallel work in SSIS packages
• Create SQL scripts for change logic
• Modify PowerShell scripts for table generation
• Redesign SSIS packages according to new business requirements
• Optimize database objects
• Create CSV output files
• Optimizing output files in SSIS packages
• Testing SSIS packages
• Creating documentation
• Analyzing data
• Creating Excel files with data on changes or new requirements for the company to testMeeting mit dem Business für Anforderungen
• Creating SSIS packages to generate the output files
• Performance optimization of SSIS packages (SQL task)
• Creating SSIS packages with Dataflow tasks
• Deploying SSIS packages from test to prod
• Using environment variables in SSIS packages
• Splitting data into tables using the stream component in the ETL line
• Creating a component in the SSIS package for working in stream mode
• Creating additional auxiliary tables for working in stream mode
• Testing SSIS packages in stream mode
• Adding environment variables for stream mode
• Creating CSV output files in stream mode
• Optimizing the mapping tables according to the business logic
• Deploying SSIS packages from Prod to SQL Server
• Optimizing the mapping of tables according to business logic
• Performance optimization of SQL scripts in the SQL task of the SSIS package
• Change the order of fields in tables for performance optimization
• Analyze mapping table according to business logic
• Foreach loop container for CSV files in the data flow of the SSIS package Use
• Union-All-Komponente für die Kombination von Daten im SSIS-Paket Dataflow erstellen
• Lookup-Komponente für die Suche nach Daten im SSIS-Paket Dataflow erstellen
• Erstellen der Komponente Merge Join für die Kombination von Daten im SSIS-Paket Dataflow
• Erstellen der Komponente Data Conversion für die Konvertierung verschiedener Datentypen in SSIS Package Dataflow
• Abgeleitete Spaltenkomponente für die Erstellung zusätzlicher Felder entsprechend der Busi-ness Logic im SSIS Package Dataflow erstellen
• Erstellen der Flat File Source Komponente zum Laden von CSV-Dateien in SSIS Package Dataflow
• Flat file Destination Komponente für den Export von CSV Dateien aus SSIS Package Dataflow erstellen

First, the closed database had to be converted back to the original format. After successful conversion, the first changes could be made to the database. This primarily involved adapting user interfaces. The user interface for the employees was considerably reduced in terms of functionality and the user interface for the administrator was expanded to include data export to an Excel table. The whole thing then had to be tested and documented.

Migration of the Microsoft Access database to an SQL server. The migration takes place in two steps. In the first step, the Microsoft Access database is manually transferred to the SQL server. In this step, the tables and the data model in particular are migrated. Particular attention must be paid to the data formats. After successful migration, we created the backend in the SQL server. In a second step, all existing queries in this area are migrated to the SQL server. A certain area of the logic is now also migrated to the SQL server. The front end should continue to be operated on the basis of the Microsoft Access database. Access from the Access database to the SQL server is now being integrated. The user concept is also being implemented. The Access database, which now provides the frontend as a client, is connected to the SQL server.

Once the database has been successfully migrated from Microsoft Access to Microsoft SQL Server, the data is now migrated. In this case, this is implemented using a number of SSIS processes. Finally, the migration was tested and fully documented.

 

• Opening a locked database
• Converting the database format
• Customizing user interfaces
• Logic extension to include an export to an Excel table
• Planning the migration from Microsoft Access to Microsoft SQL Server
• Migration of the data structure to the SQL server
• Migration of the data model to the SQL server
• Migration of queries to the SQL server
• Migration of certain logics to the SQL server
• Connection of the front end (Microsoft Access) to the SQL server
• Migration of data using SSIS processes
• Testing the migration
• Documentation of the migration