Database notes

General design notes

3-Tier architecture is divided into:

• data access layer (SQL statements)
• logic layer (Web service)
• presentation layer (application exe / or cloud access i.e. Mendix)

The data access layer contains all the SQL and database access stuff as an assembly dll. This is the only place where SQL appears. It is agnostic to the other layers provided that it implements the expected functionality, but is specific to (e.g. SQL Server) a particular DB implementation. It can be re-written to suit PostGRES or MySQL without upsetting anything else. The DA dll is referenced in the logic layer web service. This becomes an ASP.NET application and is hosted on a server (probably the same server but not necessarily as the DB) away from the users. It will sit happily in IIS or Azure out of the box. The process of deploying it is as simple as copying the WS project folder to the IIS web folder root (:-o). It listens for SOAP requests from any presentation layer things.

The presentation layer (application exe in the present case) calls functions from the web service via ConnectedServices object in the project and the web service passes through these requests to the data access layer and back with the results (dataset object). This object contains the 'connection' details to the web service URI. But, because the web service is acting as a middle-man, it also exposes a web SOAP interface which can a) appear as a webform/webpage with input boxes for function arguments - this is extremely useful for testing, and b) can be accessed by a cloud presentation system like Mendix with no modification :-). Mendix would call the same functions as the exe with the same arguments using the same SOAP interface on the same web service. Thus, nothing needs to be re-written if we wish to offer a web interface for the application which we might well need to do in the future. This gives us some quality argument along the lines of 'we are coding for future Mendix integration'.

Pros/Cons

1. Security - the separation of logic layer from data layer means that the web.config file resides on the server and not on user's machines where it can be disassembled and/or otherwise hacked. The database can be configured to only allow the web service to access its tables, so nobody can meddle with the DB data through another interface like SSMS for instance and break the app then plead ignorance.
2. Because the major functionality is split, there is a proper separation of concerns. It is generally the case that direct access between application and DB is a bad idea. It doesn't seem to have any performance disadvantage. In every developer site that I look at, this architecture is always advocated.
3. Disadvantage is that this setup is somewhat tied to Microsoft ecosystem with ASP.NET SQL Server, Azure etc. However, only the web service layer would need modification to suit other systems (like Linux).
4. It appears that Azure active directory can be integrated with the web service but would need to investigate this in detail.
5. Kubernetes clusters can be implemented natively for scaling etc or DB and WS (together or independently), but this is somewhat a side-effect of using Azure. Again, more finding out is required. Large enterprise apps that are busy are thus ready to scale without further work.
6. Deployment and dev should be simpler because each area can be developed independently. If we start on day 1 with a cloud DB, this is going to simplify dev greatly. Both data and logic layers can be continuously deployed CI/CD, only the application layer will need rebuilt during dev. We will additionally then only rely on git for sharing code rather than having a shared DB etc.

Get a list of database tables and their last read and write date and time

public DataSet GetUpdateStatsDB(string connectionString, string database)
{
    string selectSQL = "SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update, last_user_scan FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('"+ database+"');";
    SqlConnection con = new SqlConnection(connectionString);
    SqlDataAdapter da = new SqlDataAdapter(selectSQL, con);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

References

Link to pdf of the process