jjsather
asked on
How to have customers secretly run SQL Server Update Scripts?
Our customers will each have their own copy of our DB, which could even be the Express version or otherwise. The DB will need to change on occasion (both data and structure) so each customer will need to run the script changes to their own DB. Of course, we don't want them to see any scripts (to keep it tamper-proof, and keep the DB names secret).
What's the recommended way to do something like this? Through an msi? If so, how is it created? We've never done this before, so not sure what the "best practice" is for creating and/or running SQL DB scripts when it needs to be run by the customers without them necessarily knowing it's happening.
Not sure if it matters, but we are using VB.NET, Visual Studio 2010, and SQL Server 2008 R2.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've never worked with encrypted text files, but I'm up for learning. I'd also like to try to break down your suggestion into practical terms to see if I'm tracking with you.
Let's assume multiple DB versions, and each version will have various proc changes via alter scripts, 2-3 pages long each. Of course, we don't want to run procs for versions older than the DB, so we make a large text file with various delimiters, like this...
Version:1.1.1
START PROC
alter proc ProcName1
[blah blah for 3 pages]
END PROC
START PROC
alter proc ProcName2
[blah blah for 4 pages]
END PROC
Version:1.1.2
START PROC
alter proc ProcName3
[blah blah for 3 pages]
END PROC
START PROC
alter proc ProcName2
[blah blah for 4 pages]
END PROC
Version:1.1.3
START PROC
alter proc ProcName4
[blah blah for 3 pages]
END PROC
START PROC
alter proc ProcName5
[blah blah for 4 pages]
END PROC
START PROC
alter proc ProcName5
[blah blah for 4 pages]
END PROC
Now I encrypt the text file. My program reads the current DB version (suppose it's 1.1.1) and decrypts the text file above. It reads the relevant scripts and executes them. (Which in this case, would be Version 1.1.2 and following, ignoring the Version 1.1.1 scripts.)
Is this what you're saying? If so, I'm not opposed to it, but I assumed there'd be a more elegant solution, with tools already in place. But if what I'm asking for is very specific to us, then if we need to build it from the ground up, then so be it.
Otherwise, I'm trying to think if it'd be worthwhile to have these scripts in a SQL dbf file? Or maybe an XML file?