Home Forums Discussions General Discussion Now that we're SQL - best practice client server

My peer to peer network is slogging with 10 computers and I am looking at SBS 2008. What's the best practice for this situation - install SQL express on the server? install SQL and data base files on a separate server? Other ideas?

Install SQL on the Server. there is no point to install SQL Server Express if you have SQL Server

Actually, Amazing Charts V4 has SQL Server Express built in, and won't just tie to SQL at this time. Thus, I would install V4 and see how it goes.

Simply doing this on a peer to peer with or without sbs2003 as the main operating system has made a tremendous difference in speed.

I recommend using xp or vista as the main computer operating system, by the way, as sbs2003 opens a whole other can of worms given how complex and confusing it is to set up and use. If you have sbs2003, you pretty much will need your own IT person to manage the network and computers.

Jon, because SQL Server is an IP addressable RDBMS, would it not be better to allow the users to store their database on whatever server they choose?

As an experienced SQL Server developer/user, I believe above mentioned architecture will impose restrictions to networking AC from remote locations.

Additionally what design constraints would stop a user from simply replacing the "connection string" and connecting to the full power of sql server rather than express?

I've been looking forward to v 4, but I'm already using sbs2003 with the clients running XP Pro. Now it sounds like you're telling me this won't work well with v 4?

Kathleen

I have been using Windows 2003 server along with Version 4 for some time. I guess I am wondering if Jon means that the Server is a lot to get set up - if you are currently running Ver 3.7.... on the server and each of your XP workstations access the database on the server now it shouldn't be a big jump for this.

Ditto here. I already have the full SQL upon which runs my PM program. Guess I will stick with what I am using now. This then brings up an additional issue regarding paying an annual user's fee which, in my mind, includes my entitlement to regular enhancements and updates. If I cannot access these enhancements or updates using my existing system then I see no reason to pay for them.

Leslie who is obviously irritated with this news.

if you have doubts, and are willing to sacrifice the time needed to be sure, you can try running ac4 on a virtual setup-keeping the settings like you would in your clinic. you can download a great free program from sun called virtualbox (http://www.virtualbox.org/wiki/Downloads). i've actually got a test ac4 working on a linux host via a virtualized guest xp pro. re: server03, ac4 seemed to install and run ok on the server, altho i didn't test having clients attached to it. there is some performance slowing because of virtualization, so factor about 10-20% increase in speed in an actual setup.

djkym I'm not quite sure virtualization is the answer here. The purpose of migrating to SQL Server is to separate the business logic from the database function of SQL Server, which allows the server to be a "relational database management system."

The architecture as described by Jon obviously has many people scratching their heads. I think he needs to clarify what he means when he says v4 "has SQL built in...and running in peer to peer mode is faster than in client server."

All applications that use SQL Server as their datastore require an "instance of SQL Server" on which to attach the database. If you already have a SQL Server Instance, like Leslie, running anywhere on your network, you should be able to install the AC4 database to that SERVER.

The application connects to the database using a "connection string." In .Net we use a native SQL client connection. If you are not using .Net an ODBC connection is required. In either case the connection string simply provides the location to and the credentials for logging on to the server.

The power of using the SQL Server is that there is so much more functionality inherent in the server, which, in a well architected application tremendously decreases the need for the client to do a lot of the data processing.

For example, someone just recently mentioned providing an audit trail. This is a fairly common practice using SQL Server that is very difficult and resource intensive when done on the client. This type of task requires the application to keep a log of what data was changed, when it was changed, and who it was changed by.

This sort of task is easily handled by using an "update trigger," which is a piece of T-SQL code, that automatically runs anytime a record is changed. The BEAUTY of this architecture is that because the code is on the server, it will fire ANYTIME the record is changed, even if it is changed outside the application.

Try to do this in your application and you find yourself going thru all kinds of gyrations and multiple inserts to accomplish what is done invisibly by a trigger.

A place where this functionality is more germane to AC is when a patient has a name change. Using a trigger, can automatically insert the old name into a table along with the PatientID, and keep track of all the patients names. So if your patient is Elizabeth Taylor, AC will handle her many name changes just fine.

gk-
w/ virtualization, i meant testing out things before rolling things out for real.

as for other issues, i'll try to answer what i know. ac4 installer installs .net 2.0 as well as ms sql express server, and ac as a client connects to the express server (which i think jon is saying isn't tieable to a full regular ms sql server). as for speed, i think jon is referring to sql (ac4) vs access (ac3) connection speeds, rather than how ac4 is deployed. i run ac4 on a server that's also a client too (i know, bad practice), along w/ several other clients and have noticed better speeds using ac4 throughout all computers. if you're comfortable backing up and restoring ac (which i haven't had to do on our production area, btw), i would give ac4 a try, if not for the benefits you describe above.

djkym, SQL Server Express, is just a free version of sql server. The purpose of SQL Server Express is to allow developers to get their product to market WITHOUT requiring additional costs to end users who don't require full blown sql server. The database file (.mdf) and the log files (.ldf) are identical and can be DETACHED FROM ONE SERVER TO ANOTHER.

ALL I repeat ALL AC4 has to do is to ALLOW THE USER TO SET THE DATABASE CONNECTION STRING AND WRITE THAT SETTING IN THE
APP.CONFIG FILE!

This will allow the users to store their databases where ever they want on ANY SERVER they want.

This is one of those things that should have "in theory" after it. In any case, I could not install on an SBS 2008 server. I think this is might be deliberate because they want to sell SBS 2008 Premium which includes a separate server license for Windows Server 2008 with latest greatest SQL. Or it may just be some other problem. In any case, I'm not sure I would want to load down the domain server with the SQL server.

How many connections can we make to a SQL Expres server? When I add one more computer to my current system, the Access server comes back saying the computer has too many connections.

Anyone else there with an SBS installation? Are you using the features? Fax system? E-mail and Exchange server?

Kevin,

George is completely correct. I only have my databases on my server (whether it is SBS or a pseudoserver such as XP). I don't even want the application anywhere near the database files. This can be set up the same way as George talks about.

I do not think you are correct in your statement about its being deliberate. Both SBS 2008 Standard and Premium should have the same capacity to run AC 4.0.

I don't think I would worry about loading down the domain with your database system and, in fact, SBS 2003 contains SQL Server 2005 the actual version of SQL Express 2005.

Microsoft is marketing two versions of SBS 2003 (Standard and Premium) with a decoupling of SQL. Premium is simply Standard on one hardware and SQL on the other. You can still run AC4 on SBS 2008 Standard. There is a lot of controversy from what I have read about SBS 2008. I am still researching it and holding onto both of my 2008 DVDs.

As far as to the number of connections, this is limited mainly by the computer's resources and the OS. On XP Home, you can connect five, while with Pro, I believe it is 10. That seems rather low. I believe it goes up exponentially on a server OS.

It is limited in that it can only utilize 1GB of RAM and 1 CPU. It is also limited to 4GB of data, which is only double Access. The actual non-Express editions are unlimited.

I use all the features of SBS 2003. Considering 2008. Definitely use Exchange. If it is available, it is the only way to go. As far as fax, one can definitely use the server to fax if you have a modem and each PC has fax software such as WinFax. My faxing situation is overkill in that I can fax to our MFP, I can scan to fax through Captaris, and I can do regular PC to Fax and Fax to PC using Captaris. Captaris is a standalone fax server which is unbelievable with what it can do. Of course, it costs over $1,000 so it should. But, I am rather obsessed with PC to Fax and Fax to PC.

Wow Bert - WinFax, SBS fax service, Captaris, MFP - wouldn't think you're trying to avoid paper woodja. I've got the same obsession and looked at all the things you're using, except I've held out, just trying to find the easiest solution that would work and that my staff could learn. I also had Hylafax set up when I was using a Linux server, but it only worked occasionally and not well. The price was right (free). I've tried the Brother MFP solution, it's supposed to be able to transmit the fax over USB to your computer, but it hasn't worked and I've been too lazy to get on the phone to see why.

Kevin, someone stated earlier that AC4 is built on .NET 2.0. The entire data access philosophy behind .NET is the disconnected dataset. An application connects to the database does what it needs to do and then disconnects, freeing up the connection. So when you are using SQL Server and .Net, I would be looking at concurrent connections. SBS allows 5 concurrent connections, that should be able to support close to 10 users in a properly designed system.

Bert with regards to file management, I use the following:
1) Snappy Fax Server and 5 Snappy Fax Clients. Everyone can fax from their machine and everyone can see received faxes.
2) I have built an electronic filing system that is based on the filename and uses the .Net Filewatcher. The FileWatcher is a component that watches a directory for any changes you specify it to watch for. So I watch a directory called "filer" for created...when a file is created.

I have identified at least 34 different types of documents we receive and/or create. The file naming convention is important. I use DocDate_PatientID_DocType.ext So a document name would look like this: 20081115_1023_labpap.pdf

When this file is dropped in the filer directory, the filewatcher:
1) catalogs the file in my sql database
2) moves the file in the patient's folder

Someone looking at a patient record in the database can also see a list of all of his/her documents we have.

I have also set up a workflow system that uses the filewatcher. we have a shared folder "work" everyone has a folder under this folder. When a request for prescription comes in it is put in the doctors folder. The Filemanager notifies the Doc that there is a new file in her work folder. When she is done with the file she can either put it in the patient's folder or give someone else by putting it in that person's folder.

I intend to make this much better by using the new FileStream datatype in SQL Server 2008. This allows you to save a file to the database and have people access it only through the database. SQL Server 2008 also has a hierachical datatype that allows you to assign an ID to group things in a hierachy. Things like patient, office visits, docs per office visit. I estimate it will take about 8 months to complete the programming on this.