#12 Why would a database developer want to use Microsoft SQL Server and Microsoft Azure?

Loïc, what is your career path? 

I started my software development studies by a DUT in Belfort in 2008. After other professional attempts outside IT, I finally converted in 2019 by joining AFPA school in Nancy. The course was a professional title IT developer designer. During this training, I did an internship at 2DCOM as a software developer. After graduating, Technology Partner hired me. 

What are your daily assignments at Technology Partner? 

Since my arrival, my role at Technology Partner has been: 

1. The development of the Back End: development of API projects I'm working on, mainly C# ASP.NET.
2. Database architecture: architecture design and migration.
3. To be the reference database: a one-time intervention on all projects requiring a particular need. 

Here are some examples of projects I've worked on: 

• MyTax, a virtual tax reporting solution (go to project sheet) 
• JobSwitch, a workforce loan management platform during the global Covid 19 pandemic (go to project sheet)
• Foostix, a project to completely overhaul an online meal ordering platform (go to project sheet). Currently, it is on this project that I work every day. I'm in charge of analyzing the old version of the database to reproduce it in the updated version. The goal is to update it with a new architecture, innovative technologies and migrate it to the project. 

What tools do you use frequently? 

Development environments are paramount when programming, I mainly use: 

• SSMS (SQL Server Management Studio): which allows database management: Architecture and programming. 
• Visual Studio: which allows the development of the project in backend in C#.
• Postman: which allows the execution of web API (which link the front to the back).
• SSMA (SQL Server Migrations Assistant):  which allows the migration of one database management system to another. 

What do you think is the point of a good database architecture? 

• Performance Gain: A bad pattern can cause a drop in performance when queries. 
• Ensure data integrity and consistency.
• Scalability: It is easier to evolve a well-structured and well-structured scheme.
• Business Intelligence: Previous points make it easy to do BI with various such as Power BI.

What do you think are the advantages of using SQL server? 

SQL Server is Microsoft's SGBD. It manages all databases. In my opinion: 

• Built into the Microsoft stack: ease of integration an Azure, Entity Framework, C#, Power BI, etc.
  
  
• Robust and efficient.
  
  
• Relational base:  Perfectly suited to certain types of projects.
  
  
• User management / rights management: the ability to connect to the server through different authentication modes, the ability to manage rights for each user and for each item.
  
  
• Partitioning: Tables can be partitioned: multiple files for a single table, for example, allowing data to be stored spread out by accounting years for better performance.
  
  
• Data compression: the ability to compress underutilized data (archiving).
  
  
• Integrity: MS SQL Server provides access to "check" constraints that allow data integrity control over a written record without having to write a trigger, and if that's not enough, it's possible to code triggers.
  
  
• Programming: the ability to program stored procedures and compiled (more efficient) triggers, but also more advanced elements such as assemblies, which can be programmed into C# and integrated into MS SQL Server to store/use objects directly on the server (e.g.  geography, to manage geolocated data).
  
  
• Recursive Requests: MS SQL Server allows you to encode queries in the form of CTE (Common Table Expression), which allows you to write sometimes more readable queries but also give access to recursive queries (for example to manage a hierarchical system (file/file system) with an unspecified depth). 

Why use SQL server with other tools, such as Microsoft Azure?   

• Good value for money:  Thanks to the advantage of the PaaS Azure SQL Database, costs are controlled and more affordable for businesses.
  
  
• Scalability: MS SQL Server is a DBMS (Database Management System) that is suitable for both small and large databases, i.e. there is no longer any need to worry about the technical capabilities of the database for the customer, also, Azure only charges what is used in real time. 
  
  
• Transparent Data Encryption: The database is encrypted on the server side; the data is decrypted transparently during queries. In addition, data fiches can be encrypted at rest, so it is more complex to hack a system encrypt in this way.
  
  
• Safety and replication: Replication involve writing data in multiple locations at the same time, allowing greater availability in case of server downtime, but also avoiding data loss in the event of a disk crash. 
  
  
• Geo replication: In the event of a regional or larger-scale outage, the geo replication allows the switch (manually) to a secondary database (see : safety and replication) in another Azure region.   
  
  
• Restoration (Point in Time Restore): Azure automates the backups of the database allowing a rollback in case of an anomaly. These backup points can be close (less than a minute for a small base).
  
  
• Load Balancing:  Splitting the workload across multiple servers if necessary. 
  
  
• Programming: there is the ability to easily program planned and recurring tasks (jobs) thanks to Azure Functions (e.g. base cleaning, etc.). 

Written by Loïc Tessier & Charline Pennisi