CODEWITHSUNDEEP
asp.netsql-serverconcept
Khalid
Khalid

Reputation: 371

Single website, single database schema multiple copies

I know it maybe a mess in a logic I am thinking of, but the scenario is that I have a website which has 8 multiple copies same website same database schema but published on different places on the same server.

My problem is that I have about 50 stored procedures (per database), so what can I do to make maintenance easier?

Every time I modify one stored procedure shall I modify it at 8 places? Shall I change the web.config file 8 times each publish operation?

I am thinking about making a simple CMS system which I will store publish directories and on button click it will publish all project to selected directories, but still I have a problem which is the stored procedures? On first publish I am thinking of altering the whole stored procedures at first publish.

Please any suggestion is welcome, now I have only one copy and you know its hard to maintain after taking the decision.

BTW the website is ASP.NET Web Forms but I can port it to any new .NET web technology like MVC or .NET Core.

Upvotes: 0

Views: 71

Answers (1)

Dale K
Dale K

Reputation: 27198

If you have full control over the hosting then a "multi-tenant database design" as suggested by Dai would be a better approach. However if the circumstances require multiple instances then you can do the following:

  1. Store a version number in your database so you know which scripts have run and which haven't.

  2. Script any schema changes (which includes any Stored Procedures), potentially seed data, and including a new database version number.

  3. Store the scripts in a directory under the website.

  4. Have a maintenance routine (manual or automatic), which runs any new scripts which haven't been run before, by comparing the version numbers.

Upvotes: 3

Related Questions