SQL Server Over Wifi

[kd2017]

I know it's not ideal but would it be relatively safe to run sql server express on a user's desktop pc to serve up a small, light duty, backend db to a small office (2-5 users) over wifi? The users would use ms access for the front end.

Or is this a bad idea?

[June7]

I have SQLServer Express on a laptop and I haven't figured out how to get my other laptop to connect to the db. Have you?

[Minty]

Probably need to open some ports up on the server machine, and check other settings on the SQL server to allow external access.

[June7]

I don't know what "open up some ports" means.

[kd2017]

I haven't actually been able to test that just yet, June. But maybe this would be helpful for you?


There is also some settings in the sql server configuration manager to enable tcp/ip connections

[June7]

Okay, that helped me do a search and found https://techblogs.42gears.com/remote...tabase-server/ which brings back some bad memories. Fine with step1 but step 2 to open Configuration Manager results in "MMC could not create the snap-in. The snap-in might not have been installed correctly." All attempts to fix or get around this failed and I gave up. Then step 3 seems to indicate I need to also install SSMS on other computer.

Wish you better luck, kd2017.

[NoellaG]

Please be aware of security issues. At least connect over a secure VPN.

[kd2017]

Please be aware of security issues. At least connect over a secure VPN.

Can you elaborate on the security risks? I've never thought about needing to use a VPN within a wlan... Interesting and sounds like something I should have been on top of.

[NoellaG]

When you opening SQl server (express or full) for remote access, you're advertising to the world 'Hi guys, I've got data here, might be interesting for you to hack'. So :
- first check that you're using a SQL version that is still supported by Microsoft for security updates, and install the latest updates.
- Be sure you have disabled the default sa account and set up proper secure accounts.
- If the users log in on the local netwerk in the office, no need for VPN, but be aware that WIFI isn't always the most stable connection and can cause glitches
- if the users connect from home you need a secure connection like VPN

Best option is to discuss this with your security officer.

[kd2017]

Thanks. It's just wifi. Are the wifi glitches too much of an issue for SQL server to be a good idea?

[ArviLaanemets]

Most foolproof way to work remotely with database split to FE and (SQL Server) BE is:
In LAN you have SQL Server containing your BE;
In same LAN you have Terminal Server, with MS Access installed on it;
In Terminal Server, you have accounts for every user of your DB (or at least for every users which can work remotely);
Every user must have his/her FE stored either in his/her profile in TS, or in some Network directory accessible for this user;
For LAN, you must set up the remote access for all users who must work remotely with your DB.

User connects remotely into LAN, logs on into his/her TS account, starts his/her FE ... and does whatever is needed in DB.

I think it is possible to set up the remote access icon in user's computer so, that on clicking the icon all connection steps are automated, and from user's point of view simply the logon PW is asked, and then the FE of DB is opened.

With this setup. all real work in DB is done in LAN. Only the visual info is sent to user to be displayed on his/her display, an only user's keystrokes/mouse clicks are sent to FE instance in TS memory.

The main danger with having FE and BE in different locations is, that when there will be problem with connection between those locations, your BE may be get corrupted, and you have to restore it from it's latest copy.

[NoellaG]

The main danger with having FE and BE in different locations is, that when there will be problem with connection between those locations, your BE may be get corrupted, and you have to restore it from it's latest copy.

If you are using SQL server as BE, corruption isn't a problem. This doesn't mean you don't have to take back-ups. Using the normal version (standard or enterprise) you can use the SQL agent to schedule the back-up jobs. SQL express doesn't have a SQL agent, so there you'll need a Powershell script to plan the daily/hourly backups.
You can find several scripts to do so, example https://www.powershellgallery.com/pa...SqlExpress.ps1 , https://gist.github.com/AdamLJohnson...6d28131f8fc2aa

[kd2017]

Okay, that helped me do a search and found https://techblogs.42gears.com/remote...tabase-server/ which brings back some bad memories. Fine with step1 but step 2 to open Configuration Manager results in "MMC could not create the snap-in. The snap-in might not have been installed correctly." All attempts to fix or get around this failed and I gave up. Then step 3 seems to indicate I need to also install SSMS on other computer.

Wish you better luck, kd2017.

I seem to have it working.
I followed these instructions to open up the firewall on the server pc.
But when I got to the section "Connect to the Database Engine from Another Computer" I skipped the part about having ssms on the other computer. Instead I just opened up access and linked up the server using the notation tcp:<computer_name>,<port_number>

[CJ_London]

This doesn't mean you don't have to take back-ups.

also doesn't mean you don't need to compact on occasions

in Object Explorer, select Tasks > Shrink > Database.

It is a bit more involved than Access compact/repair and should be done with care - see this link
https://stackoverflow.com/questions/439071/how-do-i-shrink-my-sql-server-database

[madpiet]

Wait, I hope you're not advising to shrink the SQL Server DB. That's absolutely the worst thing you can do to a SQL database if you want it to be fast. The only time you would use SHRINKDB on SQL Server is to compact a database that you were going to archive.

SHRINKDB basically discards all indexes and then compresses the data. It's fine for if you just want the database to be small, but if you want it to be fast, you just screwed yourself, because all of your careful indexing is now gone. (So I hope all your index creation is scripted, because you're going to have to re-run it all).

Oh, found it... Brent Ozar on ShrinkDatabase

Read his article before using this feature... so you know what you're getting into. (Compact & Repair in Access is not the same as ShrinkDB... at all.)

Further down in the comments (definitely worth reading!) is this little gem: Why is Shrinking a SQL Server Database A Bad Idea