November 6, 2015 at 12:33 pm
Based on feedback from my previous post (thanks everyone) I went ahead and installed SQL Server 2008 R2 Express on a LAN connected laptop here in the office, I built three Tables along with about a dozen Stored Procedures (using code so they can be rebuilt when required). I was able to get the database running and attached to VBA code in a Word application on my laptop only (so far), and finally I was able to read the tables and display data via the application itself, so far so good !
I did have to make some assumptions though, and I do have some questions about what I did, for comment as follows (I will call my database SIMDATABASE, and my server SIMSERVER\SQLEXPRESS, for example purposes):
1. Authentication
I chose Windows Authentication, instead of SQL Server Authentication, there will only be two or three users eventually all of whom have valid Windows/LAN IDs and Passwords here in the office.
2. Is IIS necessary ?
The SQL Server install instructions advised to install IIS first, which I did on the laptop, however given that the database will be used by a Word application (with underlying VBA code) is it necessary to have IIS installed on each user's laptop in this case? Will using SQL Server with a Word VBA application work ok without it?
3. Named Pipes and TCP/IP enabled
I assumed one or the other would be necessary, I enabled both, I assume that enabling both will not harm anything.
4. Connection
I am using the following connection string currently in the Word application:
"Connection"="driver={SQLServer};server=SIMSERVER\SQLEXPRESS;database=SIMDATABASE;Trusted_Connection=Yes"
Can I put the Word application on other laptops, connected to the database on the laptop I have used? If so would I have to change the Connection string shown above somehow, perhaps to point to my laptop (e.g. with the IP address, or Computer Name, added in)?
5. Backups
It appears I will need to set up my own backups somehow (given that backup features do not exist in the Express version). Given that I can recreate the database from scratch (without data) when required (now that I have code to create the Tables and Stored Procedures when necessary), can/should I:
a. Just back up the .mdf and .ldf files on a regular basis and just copy them back if necessary if data problems occur (but the database setup itself is still ok)?
b. If the database was lost completely for some reason, could I run my code to rebuild the Tables and Stored Procedures, and then just copy back the latest .mdf and .ldf files from backup?
Would a. and/or b. work?
Am I missing/misunderstanding something?
6. Idle Issue
I have read of the Idle Issue with Express, that running a small/meaningless query every 15 minutes would keep the database alive.
Is there anything I can read further on this matter that includes best practice examples to alleviate the issue?
Any feedback would be appreciated, thanks.
November 6, 2015 at 1:31 pm
For the backups, don't copy the mdf and ldf files. If you just copy them, they might be in an inconsistent state and won't work if you try to copy them back. They also include all the DDL, so attaching them would replace any DDL that you have for the database.
Use a bat script to run sqlcmd code to backup your database which can be set as a Windows' scheduled task. Real backups will use less space and can be more reliable than just copying the data and log files.
November 12, 2015 at 7:53 am
Thank you very much for the reply Luis, I appreciate it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply