Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server HW specs w/ VFP sp2

Status
Not open for further replies.
EzLogic

EzLogic

Programmer
Aug 21, 2001
1,230
US
I am ordering a server that is going to be hosted at a data center.
We have an app running vfp9 sp2 at the clients. the Backend data is 100% sql server, and we plan to have the data hosted at that data center.

This is the specs:

ISP speed is 100Mbps at the server from the data center.
Offered me 15 TB of bandwidth going out.
# of POS station is about 500 workstations.
App is a retail POS running VFP 9 installed at the client and uses the SQL server for the data.

1 Xeon E3-1230 Single Processor, 4-core x 3.3GHz
16GB DDR 1333 ECCC
Windows 2008 R2 Standard / 64bit
1TB SATA
100 Mbps ISP at the server
15TB Bandwidth (outgoing) per month.


Ez Logic
Michigan
 
Sort by date Sort by votes
How embarrassing.

Sorry mike, i thought i typed that.. evidently i didnt

so, are these specs good enough for the server? this is the first time, i launch a sql backend app. i am new to sql


Ez Logic
Michigan
 
Upvote 0 Downvote
No problem. I've made the same mistake myself.

So, the next question is: When you refer to "the SQL server", do you mean the Mictosoft product known as SQL Server (with a capital S)? As opposed to a more generic sense of a server running any SQL database (which could be Microsoft, but equally Oracle or MySQL or anything else)?

If you are talking specifically about the Microsoft product, at first glance your specs would appear more than adequate. But check for yourself here:
For any other database, the same thing probably applies. If a server is capable of running Microsoft SQL Server, it's probably capable of running most other database servers as well. But, again, check with the vendor's spec sheets.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Thank you Mike,
yes ms sql 2012 server.

So, that is good enough for about 500 pos station that will be using it as back end data.

app is installed locally to each station, but data ia on that remote server

Ez Logic
Michigan
 
Upvote 0 Downvote
For remote usage the bandwidth in Gbps or Mbps is more important than the allowed traffic volume. Many providers simply calculate the network bandwidth x time and have a limit you can't exceed anyway, especially if you share the bandwidth. Let's see:

1000Mbit could theoretically transport 12.5MB per minute, which accumulates to about 32 TB per month. So you could practically get your 15TB, but only when constantly running at max speed, and if at max two users share that server. You can't get your 15TB, if your POS only works during working hours, so don't let this number impress you. Data will flow quite often and much, still not as continuous stream. Your max transfer rater is a simple product with many factors: 30 days * 24 hours * 60 minutes * 60 seconds * 100 Mbps. And 100 Mbps is 12.5MB per second, simply 100/8. So 30*24*60*60*12.5 = 32400000 MB, about 32 TB. If your system runs 8 or perhaps due to time zones 10 or 12 hours per day, you can only use the allowed traffic. Considering times where the system will be more frequented even less.

Also take a look at pricing:

A server license will cost you much less for the database server, but as you say you have 500 clients, its 500x209USD per CAL in that option. I'd go for the 4 core price.

But a warning: Consider the price gap - 7.171 USD for your 4 core server vs 105.400 USD for the 500 CALs option. The price gap does not only mean the core option is a bargain, it also means your system is dimensioned too low for 500 concurrent users, most probable. It's not, that you need 15 such servers, but I'd say some more cores and RAM wouldn't hurt. POS means you submit prices and receipts all the time, this is taking much bandwidth, not continuously as in video streaming, but considerably much data per time.

You could also consider using Azure and pay what you use. It could be cheaper AND you won't need to care about hardware. It's a different programming, though. If you're already ready for MS SQL Server usage this thought came too late. You never can plan too early. I am not an expert in hardware resource planning, but I would consider starting with just a few locations and see what numbers you can measure. Also ask in the SQL Server forum and perhaps in hardware. They should know better.

Bye, Olaf.
 
Upvote 0 Downvote
Look carefully at pricing of SQL Server. Microsoft has moved to a per core pricing model.

16GB on the server SHOULD be enough.

I'm concerned about the drive. To maximize SQL Server performance, you should have at least three physical drives, one for the OS, one for the SQL Server database, the other for the SQL Server logs.

Craig Berntson
MCSD, Visual C# MVP,
 
Upvote 0 Downvote
Thanks Olaf for detailed write up.

Yes, the POS will work in business hours mostly from 9am to 7pm or so.. about 10-12hours per day.

However, the vertical market that the POS is made for is not heavy intensive application, thus transactions are not large. Almost all of the transactions/queries (for the most parts) are all on the server side, Store Procedures.. Again, it is a lightweight retail POS (not an ERP system).

Having said that, I have 1 data base, with all the tables. and each table has an fk_store so i know which store it belongs too.. Data is pretty normalized for the most part.

I have 1 user that the POS use to connect to the remote server (currently a test server) with SQL 2012 Express, and the POS handles the authentication to the data and access..

Will the server look at the connection as 1 user, or each connetion as a separate client, even though I use one SQL username to connect?



Ez Logic
Michigan
 
Upvote 0 Downvote
Craigber, thank you for your answer... Indeed that is great point.. i shall look at that for sure.

especially the data should reside on separate 1TB (which is plenty for the type of application).

Currently, i have 200 stores that have been using the legacy POS (older version of this new release) and all are in VFP dbc/DBFs.


the combined data for all 200 stores, and about 8 years of work/data/records...is about 20GB (for all the files and CDX and DBFs)

and the old data structure was not as normalized as it should have been, at all! the older app was written about 9 years ago in vfp9, though it was being updated all the time..

now, we are moving all the stores current 200 stores to SQL, one central server, and getting away with the off-line sync stuff we used to do.. that was such a headache...



Ez Logic
Michigan
 
Upvote 0 Downvote
If the store lose internet connection, they have the Mifi modems, LTE speed, they are Wireless Retailer.. (MetroPCS/Sprint/AT&T etc..), so internet connection is not an issue.. and most cases the LTE or the 4G is faster than their current U-Verse internet etc..

currently, the POS (in development/test) is being connecting via ip in our connection string such as, in my SQL Class object in VFP:

loConString = "driver={SQL Server};server=xxx.xxx.xxx.xxx;7411;Database=EzLogicDB;Uid=EBaa2b20c9xx1;Pwd=whatever;"
loObj.cConnectString = loConString
loObj.connect(loObj.cConnectString)

etc.. etc..

So, we made the sql listen to a port and we did a port fwd to it..

what else could we do?


Ez Logic
Michigan
 
Upvote 0 Downvote
I moved a 10 GB DBF/DBC database to sql server, which became 65GB as a server database. Don't ask me why, we did use varchar.

Also I know from my customer the data traffic even excluding backups is quite large. This is about 200 users and I don't know the server specs, but it's not a single server anyway. Don't just think in terms of db size.

CALs are needed per person, AFAIK. You really insall CAL packs at the client side. The definition should be easy to find: In a per core licensing you don't need cals. But if you plan licensing the sql server and a single CAL for a single SQL Server application user, only one connection per time can be made. This is surely not, what you should do.

Bye, Olaf.
 
Upvote 0 Downvote
>CAL packs at the client side
or at the server side, actually. Anyway, you install packs.

Bye, Olaf.
 
Upvote 0 Downvote
Connecting to the database that way is quick and easy, but not secure by any means. Web services is a better solution, but require more changes to your code.

Craig Berntson
MCSD, Visual C# MVP,
 
Upvote 0 Downvote
Seems like the per core license is the best option

Ez Logic
Michigan
 
Upvote 0 Downvote
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Question
SQL Server data processing with VFP 8 1
Replies
8
Views
422
bharons
bharons
bharons
  • Locked
  • Question
Update Data SQL server Using Create Cursor VFP
Replies
13
Views
894
bharons
bharons
Steve-vfp9user
  • Question
WhatsAPP and VFP9 SP2 2
Replies
4
Views
376
Paul Gibson
Paul Gibson
rbeitler
  • Question
VFP File Lock Issues with Windows Server 1
Replies
10
Views
350
Chris Miller
Chris Miller
bharons
  • Locked
  • Question
update data table sql server using form vfp 2
Replies
13
Views
514
bharons
bharons

Part and Inventory Search

Sponsor

Back
Top