-1

I am trying to develop an inventory/payroll system for a factory. The information will be stored on a server, Windows Server 2008 R2, and SQL Server 2005. I'm going to store the records on the server, but will develop several front-end client forms in MS Access 2010. The information is currently in an MS Access 2010 database(local), and I'll eventually need to get this data on the server. I currently have a remote desktop access with username and password, however I can't figure out how to get things set up. I have SQL Server 2005 installed on my computer as well. How do I start to make a few test tables, things like that, etc.

Could one of you guys recommend an SQL Server 2005 tutorial? I'm just having a lot of trouble getting started on this project, not really sure what to do. I need to configure the database in such a way that it will allow MS Access 2010 to be the front end.

Thanks for any help I receive, if I need to clarify something please just ask.

Scriptonaut
  • 137
  • 6
  • 3
    I'd truly like to know the backstory to this. 1) Inventory/Payroll system, 2) Factory, 3) No one with significant experience in development, database architecture or systems administration is working on a project that touches the most important digital workflows and assets for the company. I haz a sad. =( – Wesley Apr 26 '12 at 03:43
  • There isn't much story, I'm being hired to develop these systems. I have lots of experience with database design, however I've never done it using these microsoft products. I always programmed them in the past(MySQL mostly, but a few others as well). Will you explain why you downvoted my thread and why it makes you sad? – Scriptonaut Apr 26 '12 at 03:45
  • I didn't downvote, but likely it was downvoted for being off topic. Tutorial requests and extremely broad questions or questions that don't have a well scoped topic are closed in double-time. – Wesley Apr 26 '12 at 03:46
  • Where should I post this then? I posted it over at StackOverflow and they directed me here. Tell me why this makes you sad, or why you don't believe me. I posted a similar thread last week and there was a guy who said the same thing. This is disheartening because I have to complete this job for this guy, on top of full time university and a part-time web-developer/admin job. I'm just looking for help man. – Scriptonaut Apr 26 '12 at 03:51
  • 1) Tutorial requests are always off topic for all StackExchange sites. 2) Overly broad topics are off topic for most if not all StackExchange sites. 3) It makes a lot of ServerFaulters frustrated / sad to see huge, important systems being worked on and developed by people whose questions reveal an enormous lack of understanding that jeopardizes the very solvency of the company. 4) We all started somewhere, so props for that. – Wesley Apr 26 '12 at 04:05
  • In the end, StackExchange might not be the best bet for something like this. Try a more discussion oriented site like DaniWeb or a SQL Server specific forum. Remember, Stack Exchange is not a forum, thus anything approaching discussion oriented questions aren't usually allowed. In the end Mark answered, but that was after he and I discussed the situation a bit. That's not a usual response. BTW, StackOverflow is ***horrible*** at knowing what belongs here on ServerFault, in spite of us communicating with them. – Wesley Apr 26 '12 at 04:06
  • Alright, I'll try to find a better forum for my question. Thanks for the help. – Scriptonaut Apr 26 '12 at 04:20
  • Here's my $0.02: EITHER: Find some off-the-shelf package that you can modify as a starting point, Convince the business owner that hand-rolled is NOT the way forward, OR Find someone you can subcontract to on oDesk, or something. Send it overseas for cheap labour to do the hard work, then take the top 10% or so for yourself. This is a *big* project, and it could take you years to find all the bugs in it. Why not just buy something? Is their use-case *that* specific? – Tom O'Connor Apr 26 '12 at 07:21

1 Answers1

3

Ok, I don't normally do this (post tutorials or hand-hold), but it sounds like you're at least trying to do the right thing by your employer, and I can respect that.

I suggest you start with a free version of SQL Server. Unless your software is wildly popular or you generate massive amounts of payroll data, you will probably never out-grow it.

The free version is called SQL Server 2008 R2 Express.

Now, SQL Server Express ships by default with TCP/IP connections disabled (don't stress too much about what that is, just know you need to enable them). You can find out how to do that here.

Now, what to do about that tutorial? Well, to be honest, a tutorial for SQL Server doesn't really cut it. Databases are very complex, and you need to understand at a bare minimum 3rd normal form and primary keys. Indexes and foriegn keys come a very close second.

There's an eBook from MSDN that might help, but it might be a bit too hardcore for a beginner and has a bit of assumed knowledge. Otherwise, Google might be able to help.

Accessing SQL Server from within Microsoft Access is actually very straight forward. It's even got a very useful scale-out wizard that will even copy your Access database into SQL Server and update all your links and copy all your data. Your first point of call (after installing SQL and enabling TCP/IP connections) might actually be looking at SQL server through the eyes of Access. It's likely to make things a bit less fuzzy.

The point of that is, although I could never condone using an Access back-end database in a work environment (I've just seen too many of those projects outgrow what an Access back-end can handle), if you need to hit the ground running, you can just start in Access and get developing, and then let Access take care of the migration to SQL for you.

Community
  • 1
Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
  • Thank you for the help. I already have the SQL server 2005 Express installed, I'll check if TCP/IP is enabled, if not I'll enable it. The thing is, I actually have a decent amount of experience with databases(I have a CS degree). I just haven't ever used these Microsoft products. The part that is making this difficult is having to use all the business's software, and making sure that everything will remain compatible. I know what 3rd normal form, primary keys, Indexes, and foreign keys are. I want to find the best configuration for a database of my needs. Thanks Again – Scriptonaut Apr 26 '12 at 04:19
  • May I suggest not starting with SQL 2005 unless you specifically have to? It's already 7 years old. SQL 2008 R2 is 2 years old (damn Microsofts naming schemes) and SQL Server 2012 has just hit the market, which will makes SQL Server 2005 *four* versions ago. – Mark Henderson Apr 26 '12 at 04:23
  • I feel like my employer is pretty strict as far as what we use, he is already using the SQL 2005 for other things on the same server. Do you think it's worth converting to 2008? I might be able to convince him if it's going to make a large difference. The database won't be very complex, there will be about 5 tables total, and the vast majority of queries will be uploading barcodes one at a time. I think there are around 50 employees max. I'm just giving you a little more information in case it helps you picture the whole setup. Thanks. – Scriptonaut Apr 26 '12 at 04:29
  • To go from 2005 to 2008 or 2012 is actually no work at all. You just install the newer version, unmount the database, and mount it in the new version. If you keep your instance name the same, you don't even need to configure your apps. Note: Once you attach a DB to a newer version, you can *never* attach it to the old version again. SQL Server has compatibility modes which are kept in-tact from the source version that ensures that any depreciated TSQL will still operate. Although 2005 was a mature product, there *are* three service packs for it. – Mark Henderson Apr 26 '12 at 04:31
  • Admittedly we only left 2005 last year, and there's not a huge difference for most applications, but the risk of EOL does make me nervous (and was the driving factor behind moving platforms). Once a Microsoft product hits EOL, it doesn't even get critical security updates. – Mark Henderson Apr 26 '12 at 04:32
  • [I just looked it up](http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL), SQL Server 2005 became unsupported in April 2011, and will become EOL in 2016. SP3 hit unsupported in January of 2012. – Mark Henderson Apr 26 '12 at 04:35
  • Alright, I think then I'll talk to the server manager and see if we can do it. I assume if we move to 2008/2012, and I back up the old databases, we can still go back by restoring the backups? I'm all for using a newer version, I just want to do what will be easiest/smoothest without sacrificing security/functionality. Thanks, you've been a lot of help, you seem to know a lot about databases. – Scriptonaut Apr 26 '12 at 04:48
  • Yep, take a backup before you upgrade and you'll be able to restore the backups into 2005. I work with medium sized databases for a living (in the 20-100gb range). There's some guys on here who work with databases in the terrabyte range. – Mark Henderson Apr 26 '12 at 05:24