?
Solved

How To: Distribute a SQL Server backend for a VB Application

Posted on 2003-03-31
12
Medium Priority
?
329 Views
Last Modified: 2010-05-18
Hi,

I need to distribute a SQL Server database backend to a number of different sites/networks. As I have never done this before (normally use MS Access!) I thought I would ask the experts, you guys, for some help/tips.

If anyone has any suggestions, please let me know.

Details:
Approx 25 tables
Approx 60 Views/Stored Procs
Distributing by CD
Distributing to SQL Server 7.0 or 2000

Thanks in advance for your help.

TC
0
Comment
Question by:TC99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 8242424
Hi TC,
Check the "transfer database" task in DTS.
Can choose to move or copy the database to remote locations.
Hope this helps.
Jay
0
 
LVL 1

Accepted Solution

by:
hmadhur earned 225 total points
ID: 8243752
Hi TC
Would like to have a bit more information, like are you deploying this SQL Server database alongwith an application that you have developed? Or is it that you just want to deploy this database on some other sql server?

In the later case you can backup the database and store the backed up file on the CD. And on the target SQL Server you can restore the database. This is one method. Otherwise you can copy the MDF, NDF and LDF files (the physical file extentions for your database) on the CD and then execute the sp_attach_db procedure to attach the database to the target SQL Server. This is the second method.

In the case of deploying the SQL Server alongwith an application, you can create scripts of the database and execute the scripts by modifying the setup package. You can create scripts by right clicking on the Database name and pointing to All Tasks->Generate Scripts. Here you will have option to create scripts for tables seperately or just one file for all the objects.

Hope this helps. Any more queries are welcome.

Regards
Madhur
0
 

Author Comment

by:TC99
ID: 8244427
Hi,

Thanks for your input Jay, I will lok into that.

Madhur, I have written an application for a company that uses an MS Access backend. The company have got on very well with the app and have decided to pass it on to all their users, with my help. This means that I need to create a CD that can be sent to their users for installing the app and the now SQL Server backend.

I use InstallShield to install the application but also need to give them the option to install the database at some point.

I have looked at sp_attach_db and got it to work but was not sure if it was the right way to do it. Will the scripts re-create all tables, views, stored procs, etc. including any sample data in them?

Thanks both for your help.

TC

PS. On a seperate note. Can you tell me what these points are for. I had two questions to ask but because I put all my points on this one I could not ask the second!?!? I did want to ask how to easily create views and stored procs in SQL Server from my MS Access backend? Help!
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Expert Comment

by:AndrewNovick
ID: 8246297
You're on the right track with sp_attach_db.

You'll want to either use it or a restore of a backup.

while either will get a database in place, you still have to deal with issues of users.

The logins at each site are different from the logins in the database (either the mdf file or the backup).

Your best bet is to run the sp_s to create the login that the application will use and the users in the database.

Since you're installing in multiple locations the most important thing about the code that runs the script is the error handling.  I've never liked programmingin Install shield so what I suggest is that you create a form in the Access application (are they still using it for the front end?)  and use it to automate the sp_s to move the file, attach it (or restore) create the login and the users and authorize the user to have the access that you want.

The last task is also one reason it's a good idea to use an application role. It eliminates the permission management issues.

Regards
Andy
Andrew Novick
http://www.novicksoftware.com

Home of the T-SQL UDF of the Week Newsletter.
0
 
LVL 1

Expert Comment

by:hmadhur
ID: 8251399
TC
I completely agree with Andrew. Programming in InstallShield is bit difficult. I always used Package and Deployment wizard (which comes with Visual Basic alongwith the source code) and modified that to cater my requirements.

The SQL Server database will be installed only on one machine, right? (The server machine!) The scripts will recreate all the objects that you specify while creating them. Sample Data will not be scripted. All the data in the tables will be gone. So be careful while training the company about the installation of Database if you use scripts. First ask them to backup all the data.

You can use sp_attach_db without any problems and the data will also be safe. But, as Andrew said, the problems of users and logins will still be there. You can execute the stored procedure from your installation program (using isql.exe). Now that depends on you, which installation program you wish to use.

About your second question, what exactly you want to do? Do you want to import the query files from Access to SQL Server? According to me, you cant do that. Even DTS will not allow you to import the query files. So you will have to create them the hard way, manually. :-)

Andrew,
What is sp_s? I dont know about this.

Thanks and Regards
Madhur
0
 

Author Comment

by:TC99
ID: 8252468
Hi Madhur and Andy,

Thanks both, for all your help.

Andy, can you clarify what sp_s is as it would seem sp_attach_db is the way I need to go as I must have certain elements of data in the tables. (Note - It has a VB front end)

TC

PS. Can I split the points between you???
0
 
LVL 1

Expert Comment

by:AndrewNovick
ID: 8253148
Hi

The sp_s  are:

sp_addlogin to create a new sql server login
sp_grantlogin for Windows accounts instead of sql server accounts

sp_grantdbaccess which grants to the login the right to access a database.

and you know about sp_attach_db

Andy

0
 
LVL 1

Expert Comment

by:hmadhur
ID: 8253661
Oh, sp_s actually means sp underscores.
Yeah, you can use the above mentioned procedures to create users and also grant access rights to the users.

And about splitting points, you can do that. :-)

Thanks Andy

Regards and Best wishes
Madhur
0
 

Author Comment

by:TC99
ID: 8253742
Hi both again,

You've both been really helpful and I get the impression points are the in thing, so I would like to split them without prejudice..

How do I do it??

Although I have enough to get on with now, could I post some more comments here in the next couple of weeks regarding this topic?

Thanks again.

TC
0
 
LVL 1

Expert Comment

by:hmadhur
ID: 8253848
Hi TC,
Points are not the in thing. The point is how well can you answer the question and provide good solutions. If the solution is proper and the questioner accepts it, then it builds your confidence to solve another problem. Whether you get the points or not, you learn a lot from this site. So be a regular visiter here.

Frankly I dont know how you distribute the points.

Points or no points, solution must be there.

Regards and Best wishes
Madhur
0
 

Author Comment

by:TC99
ID: 8253990
Hi Madhur,

Thank you for showing to me that this site is not just about helping people for points. I did wonder. I am a member of two other forums for VB and help people there because I can and I know they will help me when I need it.

I am not interested in obtaining points but it appears you need them to even view other peoples comments here.

Thank you for the time you have spent on helping me and I hope I might be able to return the favour one day.

If Andy is happy I will keep this topic open for a while until I have a working solution.

All the best.

TC
0
 

Expert Comment

by:CleanupPing
ID: 9275413
TC99:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question