Link to home
Start Free TrialLog in
Avatar of Tony Owers
Tony Owers

asked on

Demo of web application..

I have developed a web application that i want to make available for people to try out and have a look at.

in my mind i see it loading the database into a session, so the user can log in, try things out test what they want to do make changes to the data that doesnt get saved back to the original data base, and when they log out or the session expires the dummy database no longer exists,.

i am using ms sql 2017, asp classic on a windows server

is what i want to do possible, and how would i start

thank you
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Loading SQL Server 2017 database into an ASP session is not easily achievable.

Use some other (simpler) SQL engine, e.g. SQLite which consists of one disk file so you may copy it into a new location for each logged-in user. Yo may then delete the copy after logout or after some inactivity period.

Of course, you could have SQL 2017 database backup which you would restore into a randomly named database when user decides to use the demo but this approach is "too heavy" for the demo purposes.

Additional option is to generate data for each logged-in user identified by unique session ID and store them in one common database. Each table which can be updated by the user must have SessionID column to avoid conflicts. And each query will need the SessionID as one of the parameters or filters in the WHERE clause. Such database would require regular cleanup maintenance task.
Avatar of Tony Owers
Tony Owers

ASKER

the SQLite way seems to be the easiest method to implicate from your descriptions, could you point me to any Docs that would help me set this up.

Thank you
You may look at the SQLite documentation here: http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki This should give you some basic info about the SQLite database access. ASP.NET is better approach for database access from web but if you tend to ASP then you may read some hints here: http://sqlite.1065341.n5.nabble.com/how-to-use-sqlite-from-classic-ASP-with-no-installation-td90100.html

SQLite ODBC driver is available here: http://www.ch-werner.de/sqliteodbc/

And sample ASP code is published e.g. here:
<table>
        <tr><th>Name</th><th>Surname</th></tr>

    <%
    dbpath=Server.MapPath("sqliteveri.db")
    Baglanti = "DRIVER=SQLite3 ODBC Driver; Database="& dbpath &"; LongNames=0; Timeout=1000; NoTXN=0; SyncPragma=NORMAL; StepAPI=0;"
    Set bag = Server.CreateObject("ADODB.Connection")
    bag.ConnectionString= Baglanti
    bag.Open


    set recordSet = server.createobject("adodb.recordset")
    SQL="Select * from DATALAR"
    recordSet.open SQL,Baglanti,1,3
    %>
    <% Do while not recordSet.eof %> 
   <tr><td><%=recordSet("isim")%></td><td><%=recordSet("data")%></td></tr>
    <%
    recordSet.MoveNext
    Loop
    %>  

    <%            


    recordSet.Close()
    bag.Close()

    %>

    </table>

Open in new window


You may also download some SQLite Studio which will allow to create and maintain your initial database.
so i wont be able to use my existing sql ? will i have to rewrite all my queries to do this?
Well this depends…  SQLite SQL dialect is different from MS SQL Server. Basic queries can remain unchanged but SQLite does not know some constructs, e.g. SELECT TOP n and also some data types can differ between the two platforms.

You don't need to rewrite everything but you should test everything.
how about stored procedures?
If you have many stored procedures then you have to reconsider the SQLite use... SQLite does not support SPs.

In such case is maybe better to apply SessionID to each table which is user specific as mentioned above:
generate data for each logged-in user identified by unique session ID and store them in one common database. Each table which can be updated by the user must have SessionID column to avoid conflicts. And each query will need the SessionID as one of the parameters or filters in the WHERE clause. Such database would require regular cleanup maintenance task.
way beyond my skill level..

is there a way i can copy the original DB and create a temp DB on session start in mssql use that for the session and delete it at the end?
SQL Server does not allow several temp databases at once.

I did not test this way but you could try to restore the database backup into randomly named database on the session start. Everything you must do in your app is to use different DB connection for each user (with different connection string) and you have to do some cleanup because the number of unused databases may grow rapidly.
strictly speaking, but it will allow many databases, they dont have to be officialy temp. they will be temp databases due to the fact they get deleted at the end of the session...
the plan i have in my head,

user lands on login page that generates a random dbname and saves it in session,

when they log on using demo crdetials this triggers the copying on the database naming it using the name saved in session, connection string will be dynamic using the db name in session.. would need to limit the number of db active at anyone time with a server busy message if the limit is reached so 1000's of db instances cant be created by malicious activity..

user does what they want to do and on logout or session end the database gets deleted..

does this sound doable,
Yes, this sounds doable.
I can copy and rename the db using copy db wizard, i cant find a way to do it using T-SQl, and mssql doesnt let you save the siss package


i have got this almost works but fails on the renaming of the logical files

backup database DemoCMMS
to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\DemoCMMS_full.bak'
with init, stats =10;

restore database DemoCMMStest
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\DemoCMMS_full.bak'
with stats =10, recovery,
move 'logical_Data_file' to 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DemoCMMStest.mdf',
move 'logical_log_file' to 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DemoCMMStest_log.ldf'

Open in new window


Error Message

20 percent processed.
40 percent processed.
61 percent processed.
75 percent processed.
81 percent processed.
92 percent processed.
Processed 624 pages for database 'DemoCMMS', file 'cmms' on file 1.
100 percent processed.
Processed 2 pages for database 'DemoCMMS', file 'cmms_log' on file 1.
BACKUP DATABASE successfully processed 626 pages in 0.244 seconds (20.013 MB/sec).
Msg 3234, Level 16, State 2, Line 5
Logical file 'logical_Data_file' is not part of database 'DemoCMMStest'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Completion time: 2019-11-17T09:08:14.9861434+00:00

Open in new window

Are you sure Logical file names are used in the original database?

The MOVE clause defines new physical file location for Logical file included in the backup. Yo may see the Logical names when you issue  
SELECT * FROM sys.database_files
on the original database or also when you open the backup file for restore in SSMS.
i think that was the problem, previous copying and restoring of the db has changed the logical file names, i am have now created a completely new db and imported data so i have an untouched db to stat with.. trying it again now..
tested this it isnt the file names causing the problem..

the problem is it is not updating the logical file names when it creates the new database, it trys to keep logicl file names the same so cant create them as they already exist.. how do i copy the db with new logical file names..
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, i hade to remove the TestDB_mem line as that file isnt created..

and to repeat this i had to add a kill instruction to the db before it runs so it can over right the files...

logging out is fine, no problems triggering the delete sp

next step is to identify and trigger the delete sp on session end i have read somewhere that session end is a usable event in asp classic, do you know how to tap into this or should i open another question..
Yes, the "mem" is just my filestream data folder which you don't have. The script was just presenting the possibility to have equal logical file names in one SQL Server instance.
should i start a new question for tapping into the session end event?
Yes, it would be better because more experts can be involved.
ok thank you for your help, ill close this one down now..
Thank you for your help..
You are welcome!