Setting Up SQL Network

[younganimater35]

I'm setting up an SQL database at my house, awaiting my ISP to send me a static IP, I'm still learning how to setup the database, so if my terminology isn't correct, bear with me.

I have a few questions:
1. Should I invest in a better modem/router in order to handle the sql server and my 2 PCs? I'm using the one my ISP gave me. Arris BGW210 and I have the 1 gig plan.
2. What do I need to do for my other computers as far as DNS or DHCP?
3. With the static IP configured in my router, whats the best option for firewall/protection?

Thank You

 

[XavierMace]

SQL has nothing to do with DNS/DHCP so I'm not sure where you're going with that. If this SQL server is just for LAN usage, your modem is moot. If this is going to be externally accessible, that's a completely different list of questions.

 

[younganimater35]

Ok, I'm needing the database to be accessed from an external connection, hence the static IP, that raised the question how will my other computers be affected by assigning a static IP via my router.

 

[razel]

No worries. We were all noobs at one point and your question seems sincere.

1. Try your current modem/router combo 1st. What you'll need is being able to adjust QOS settings. That allows it to prioritize traffic. I doubt the one you have has enough settings to get what you need done, but try it 1st before spending money. By the way it's your upload the needs to be good and if you do have actual 1gig/s of data coming out constantly, I'm sure your ISP will charge you for overages or you're getting flooded.
2. You need to open the correct ports for your db in your modem/router and you'll want to reserve IP addresses for your db server as well. You can leave DHCP running as long as you can reserve IP.
3. Best option is just to open that port for your db only. IIRC MS SQL Server is 1433

 

[younganimater35]

i think my upload tests at around 250mb. ok that makes some more sense, perhaps i'm mistaken as to how exactly a static IP would work on a multiple machine network? You're saying that I need to open the ports that the server will use and then assign the IP to the machine, not the network?

 

[XavierMace]

If this is for a business, I would highly recommend paying an experienced person to do this as you're clearly new to networking in addition to SQL. Ask anyone who's become an accidental administrator, things never get done right the first time, and there's always something to fix.

Your remote users will connect to the external static IP on your edge device (router). The router will forward that traffic (based on the rule you'll need to setup) to the internal IP of the SQL server. The other computers on your local network will be accessing the SQL server with the internal IP or DNS name.

If this SQL server is is going to be busy enough that you need to QoS a gigabit connection, it needs to be in a datacenter. That said, given this is a home build, I'm about 99.9% certain the server isn't fast enough for that to be a concern. Your best option for a firewall is buying an actual firewall or building one yourself.

 

[younganimater35]

yeah i came to that decision as well, thanks for the help, i'll learn another way

 

[XavierMace]

Not trying to discourage you from learning, but learning on a production business system is absolutely NOT the way you want to learn.

 

[younganimater35]

all good, its for my families business, i'm trying to get them into the 21st century

 

[XavierMace]

If you can give us some details on what you're trying to accomplish we might be able to provide alternatives.

 

[mikegg]

You can host an SQL database on Amazon RDS for free for one year.

 

[younganimater35]

Basically were using an app that gathers info from different people, when they submit the data it can go into an Excel sheet via CSV files, it can also be submitted to (my) database where, it's my understanding, it will update and keep the Excel workbook live as opposed to me taking those individual CSV files and uploading them to Excel.

Most "free" hosting that I've found is free as long as you keep the DB less than 10gb, I don't mind paying so that's not the issue. We have about 30 employees we gather data from on a weekly basis so I don't think that would ever come close to taking up 10gb, and the state requires us to keep paper trail so I'd be printing and deleting the data regularly.

 

[sdifox]

Basically were using an app that gathers info from different people, when they submit the data it can go into an Excel sheet via CSV files, it can also be submitted to (my) database where, it's my understanding, it will update and keep the Excel workbook live as opposed to me taking those individual CSV files and uploading them to Excel.

Most "free" hosting that I've found is free as long as you keep the DB less than 10gb, I don't mind paying so that's not the issue. We have about 30 employees we gather data from on a weekly basis so I don't think that would ever come close to taking up 10gb, and the state requires us to keep paper trail so I'd be printing and deleting the data regularly.

Err which state doesn't recognise electronic record, especially if it is electronic by origin?

 

[mxnerd]

Err which state doesn't recognise electronic record, especially if it is electronic by origin?

Yeah, why would OP record the data, print it and then delete it?

Also don't think it's a good idea for OP to host business data at home when it must be accessed by 30 employees. Home network is very unreliable and could have many downtimes.

 

[younganimater35]

Texas, auto bussiness, there's a lot of changes happening with electronic information, titles, bills of sale, affidavits, etc that Texas has not adapted to. A company that facilitates part of our business is Cox Automotive and they are rolling out with the whole paperless model, our location in Dallas, despite the state requirements, will not be printing anything, we are now having to access that data and print it ourselves.

As to the down time, yes, I took that into consideration, the database is not my only avenue, I'm not ignorant lol, it's my family company and as I mentioned, I'm wanting to learn and having the database is not integral to the operation of our business.

 

[sdifox]

Texas, auto bussiness, there's a lot of changes happening with electronic information, titles, bills of sale, affidavits, etc that Texas has not adapted to. A company that facilitates part of our business is Cox Automotive and they are rolling out with the whole paperless model, our location in Dallas, despite the state requirements, will not be printing anything, we are now having to access that data and print it ourselves.

As to the down time, yes, I took that into consideration, the database is not my only avenue, I'm not ignorant lol, it's my family company and as I mentioned, I'm wanting to learn and having the database is not integral to the operation of our business.

Multi-state solutions are complicated when you are talking about compliance. Especially around privacy and security.

Normally you would have a frontend webapp that your users access. The webapp interacts with backend database.

Exposing the db to the internet directly is a really bad idea.

 

[manly]

The first rule of software systems is buy, don't build.

Multi-state solutions are complicated when you are talking about compliance. Especially around privacy and security.

Normally you would have a frontend webapp that your users access. The webapp interacts with backend database.

Exposing the db to the internet directly is a really bad idea.

Thanks, took a while for someone to state the obvious.