Home Forums Discussions Problems SQL standalone instead of Express SQL Software

I don't know exactly where to post this message.
I just hope it will not be too out of place.
What happened is my A/C version 4 has some problem. It restarted the data base by itself in the middle of the day.
If users wait out about 3 minutes then it is OK. But as you know, 3 minute freeze is a disaster.
I have an SQL specialist on my staff. She advised me that she can use stand-alone SQL on a separate server instead of using SQL Express that we are using now. Our SQL express is free but the standalone is far more powerful and she has that software.

My posting here is to ask for your support to let the Amazing Charts to allow my staff specialist to use the SQL
stand alone. In order to use it, she will need to know theconnection string. It is like a key to connect to the three SQL data bases of Amazing Charts.

I have programmed in Main Frame (IBM) for over 25 years but I am still new with SQL. I just repeat what she said about string connection. I believe if A/C allows her to use SQL stand alone, it will make our software more versatile.

Thank you

Chris Le

Interesting that you should write this. My brother in law is also an SQL "guru," and tells me he can set up the sql server to save the AC data in two different servers simultaneously, but he would need to install the full sql server, whatever that means. I, of course, have no idea what it would entail but I would love to have that degree of redundancy in my office. I already had 1 episode where the power supply for my main server died and I spent 2 days getting a new one. Needless to say, having another backup server with no down time would be extremely valuable.

In the meantime I am buying a second server with redundant power supply and hot swappable hard drives in raid configuration to hopefully prevent this from happening again.

I'm not sure you "need" to replicate the data across multiple servers, for a small practice. It would be overkill.

The issue with your server was a design issue. When ever I "build my own server" I always buy two of the things most likely to fail:
1) Motherboard (identical model)
2) Power Supply (Redundant)
3) Extra Hard Disk (even if you have RAID)
4) CPU Fan

Of the 4, the identical motherboard is most important, because if you have to swap out the Motherboard, you will more than likely have to reinstall the OS and all applications.

When I build servers, I ALWAYS, repeat ALWAYS, separate the OS drives from the Data Drives. So my server:

My OS drives consist of 2 160GB mirrored HDD's.
My data volume consists of 4 x 750gb HDD's (RAID 5)

I use a NAS Drive with 4 X 750gb HDD's (RAID 5) as my primary backup device. I IMAGE both the OS and the Data volumes weekly. I also write the images to DVD for off-site storage.

I hope this helps.

Chris, FYI, to read up on connection strings go to www.connectionstrings.com. With regard to AC4, I'm sure I read somewhere that it will only work with SQLExpress.

"small practice" is a matter of perspective. Who really considers their practice "small" when they have a waiting room full of patient's and a computer screen with a cursor light blinking or dark? One or two days of lost income and upset patients is worth the "overkill" if you ask me.



As for the rest. Sounds pretty good. I am in the process of talking to a new IT person. I will show it to him. For me, security, reliability, and redundancy are paramount.

Well that all sounds pretty complicated, but when we installed AC4 on a server that already had SQL 2005, the installation program did not bother with installing SQL Express and the databases are under SQL 2005.

The beauty of having an Access Jet 4 backend like what I have at my office is that you don't need a typical RAID dedicated backend server, with redundancy that multiplies the hardware costs exponentially.

Just yesterday I was looking at my former 7-year old backend "server", now running as a client. I sports a 2.8 GHz Pentium 4 machine with a Prescott motherboard and has a now failing hard drive and it has only a 300 Watt Power source. I swapped out the old 80GB HD for a new 500GB HD and bingo! Another 5 years for that beast...

>>> The issue with your server was a design issue. When ever I "build my own server" I always buy two of the things most likely to fail:
1) Motherboard (identical model)
2) Power Supply (Redundant)
3) Extra Hard Disk (even if you have RAID)
4) CPU Fan

The problem that I see with having extra hardware just lying around, besides taking up space and tying up your money is that all of that hardware will quickly become old, and much of it will never be usable when something finally fails. Why not get the stuff at your nearest Micro Center? Instead of a redundant MB, why not buy a new "barebones" or full Dell server on eBay when something goes wrong? They would be much cheaper...

Al

I have a Dell PowerEdge 3600 server which has redundant power supply and hot swappable drives, raid configured. It's pretty awesome to have confidence in knowing it's HIGHLY unlikely to ever catastrophically fail. Good idea to get a new server.

Chris,

First, that is something I would go right to Jon on. But, I will save you the trouble, because no one is going to give you the connection string to their databases.

I'm not really understanding your question as my knowledge of SQL is very poor. I am probably mistaken, but I thought when AC V4 is installed to your "main"computer (server) it installed SQL 2005, which is a full version of SQL (?). SQL Express is installed on the client machines, those that connect to your main computer in order to access the database.

Can someone comment if I understand this correctly?

Adam,

The "full" version of SQL 2005 is just that. The full version: SQL Server 2005. It comes with SBS 2003 Premium Edition and other server OS. If not, you have to pay for it as it is Microsoft's actual flagship database. SQL Express is Microsoft's free version where My SQL is an open source Sequel database.

SQL Express would install completely to the "main computer" and be the database for AC whether SQL Server 2005 was there. If SQL Server 2005 is there as it is on your computer, then the AC installs SQL Server Express as its own "instance" which are basically separate installs of SQL within SQL Server 2005. It was designed to install this way and so I would install it that way. Some try all sorts of different methods on here and, for some, they have their reasons. I see little reason to do so.

As to the clients, there is no need to install SQL server and you should not. There is the screen, which comes up that asks you if you are going to be using the client as the main computer. That is where you select the second option. While it will work that way, I would recommend uninstalling SQL off any client as it will take up resources and there is actually no reason for it to be there.

Bert,
Thanks for clearing up the issue on the server.

My confucion about the clients: when installing them as clients, (the second option in the AC installation wizard), it goes through a series of screens that make it look like it is installing SQL Express, then it says it failed to install, which is apparently a normal part of the installation process on Vista machines. I didn't do any additional installations above and beyond what the installation wizard does for me.

It's all working just fine, I'm just seeking to understand what the installation wizard actually did.

Just look in your services on your CLIENTS. The only service related to SQL (if there is one there) would be SQL Server VSS Writer, which is no longer needed after 2003 OS. However, it came with XP and as I just checked, it comes with Vista and WIN7 as well.

You can also download Studio Manager for Express, but it will do little good without the sa password.

http://www.microsoft.com/downloadS/...E3D-94B8-5A0F62BF7796&displaylang=en

Thanks for explaining that Bert!

Al, what do you think Dell or any server maker does? They have that "extra hardware" laying around. So when things break, they can ship it out.

My server cost me 1800. and the extra hardware 500. If you can find me a Dell PowerEdge, with:
750gb x 4 Hot Swappable RAID 5
8 GB RAM (upgradable to 32)
Dual QuadCore Opteron Processors

If you can find me a dell Opteron, with those specs, and the warranty, its a good deal.

Ok - my clinic has just hit the wall with AC and sequel express. Does anyone have the process of what to do to install AC on the sequel standard 2005? AC help desk is looking into the process, but I'm checking to see if anyone here has the answer. We've got Windows server 2003 with 3 gig processor, 4 gig ram and we're not even using 1% of the bandwith available. AC is just crawling. My IT suspects its because we have 15 users that are actively logging into the database and running AC. Solutions?

With SQL Server 2005 Standard Edition the memory it can use is the maximum memory of the server. So in your case the 4GB of RAM (which is actually around 3.2GBs) Express is limited to 1GB of RAM

There are pros and cons to each as SQL will continue to use more and more memory for pages and procedures. Fortunately, you can set a maximum of say, 2GB.

I had no issues with Amazing Charts creating an instance with SQL Server 2005 and not Express. I just removed the SQL Server 2005 instance components and then removed the AmazingCharts: Database engine. Of course, everything was backed up for reinstalation.

I am a little confused about what you mean when you say you are only using 1% of the bandwidth available.

Of course, the other solution would be to install a 64-bit OS and add RAM, but then Express can still only utilize 1GB of RAM.

In your situation, my sense is that SQL Express would grab its 1GB almost immediately, leaving you with about 2.2GB of RAM, which should be plenty to run your other programs including SQL and AC.

If you go to Task Manager | Performance (tab) |Physical memory, what is the breakdown of memory there, especially the free memory.

And, when you go to Task Manager |Processes (tab | then click on the memory section of the row above the processes until you get the processes with the highest amount of memory, is SQL at the top; and how much memory is it using?

When they talk about 1% it could be the percent of CPU (processor usage), or they could be looking at a network trace.

Either way, it's a good chance that SQLExpress is memory-bound.

Bert, in reading this string, it looks like it is addressing my desire to have a fully redundant server online. When it started in 2009 you implied it was impossible to get the connection string to run the full SQL standalone, but now t looks like you can? What am I not getting here?

Good question. The connection string should be within the program. I have no idea how I connected the time that I did. AND, I am definitely not a SQL expert. Sorry for the confusion.

My question was actually not about a stand alone server for backups, but how to migrate the database from sql express to Sql standard.

I still haven't received an answer on this.

I am not sure where the server for backups came in. I didn't see that mentioned in the previous posts.

You may not get an answer, because it isn't something that one just does, and AC isn't going to tell you.

I am assuming you mean SQL Standard 2005. I don't believe you can just install the non-express SQL and make a new instance. I believe you would have to upgrade from the Express version. You can either do this yourself or I can connect you with a SQL expert. But, I can't do that anymore unless you are very serious about going through with it, because too many users here have asked, I then set it up, and they disappear wasting a lot of the time of my friend.

A year and a half passed by from my first message
about using SQL Stand Alone.
I would like to thank everyone.
In fact, we have ran SQL Stand Alone for more than a year
without EVER CRASHING: flawless system.

I have an SQL specialist on my staff doing it. She is Microsoft Certified DBA. Technically, it is all smooth sailing.
The only difficulty is: we must know the password string that Amazing Charts uses.

I am forever appreciative for the Tech Support of Amazing Chart
who came one Sunday and connected our Stand Alone SQL to Amazing Chart data base for us without ever revealing these passwords.
(Not that we want to know the passwords. Basically they use the technique to take over our server. When they typed in the password string connection, we saw only the mask ********* on our monitor).

Well this is a happy ending for a complicated subject.

I am 50-50 about knowing the passwords. But, in order to use SQL Server Management Studio, you need the passwords and in order to back up SQL, you need the passwords. One should be allowed to back up SQL, so we have them. Of course, we don't give them out.