SQL/DBMS ninjas please help! How can I replicate my current abilities at a new company from scratch?

January 23, 2008 4:13 PM

Some colleagues and I will soon be leaving our current employer and starting a new company. How can I best replicate our current relational database/SQL abilities from scratch?

A little background: I am far from an expert in this field. I am more an end user. My daily routine consists of sitting down at my desk, firing up Exceed, and then creating/running various scripts and tables to get my bosses the information they need.

We don't have (and I don't need) a fancy front end. I spend all day creating/inserting/updating tables on the fly, writing my own SQL code, inserting/selecting out data I need, etc. I log in and start querying away to my heart's content. I work in the financial industry - most of the tables I'll be creating and joining with will contain anywhere from a few thousand to millions of rows.

What I don't do (or understand): syslogs errors, problems with the number of locks, etc. The types of things are handled by our DBAs, of which I am clearly not.

I know at my office we currently use Adaptive Server Enterprise/12.5.4/EBF [aka Sybase?].

What I really need is a general behind the scenes idea of what our hardware/software/DBA needs would be going forward. I'm literally at a loss - I've seen the various posts on here regarding pros/cons of one DMBS vs. another, and it's only muddling my brain. How can I quickly get set up (say, in a 2-3 week timeframe? or is that unreasonable?) and still have something that meets my needs.

If I haven't posted enough information and this is too vague to answer, let me know what other types of info would help. Thanks in advance!

posted by raz5 to computers & internet (11 comments total)

I'm not sure excatly what you're asking, are you wanting a basic overview of how database software works? Or are you wanting an overview of administering the server?
posted by Syntoad at 4:20 PM on January 23, 2008

Much of the server management is specific to the database software you'll be using. Are you planning to stay with Adaptive Server? If not, and you are planning to build your own from scratch:

1. You need to come up with a data model and learn enough generic DBMS internals to be able to create and manage your objects.
2. Depending on the DBMS software you choose you will need to learn about server issues: system management, allocation, space management, performance and network issues etc.

Are you asking which DBMS software you should choose? (e.g. MySQL, SQL Server, Oracle etc.)
Regardless you'll have to learn 1) Is that something you're familiar with or are you asking how to learn that too?
posted by vacapinta at 4:27 PM on January 23, 2008

First, look at open-source tools. They're free. PostgresSQL is good with large datasets and joins and you can download it and install it on your desktop.

MS-SQL bought Sybase and is an equivalent product these days, but it's hell to get set up and to administer.

Second, you learn the way the DBA did: By need. "Hmn, I don't know what this error is. I guess I'll google it." ... or you buy a book from a bookstore.
posted by SpecialK at 4:31 PM on January 23, 2008

Since you seem to know enough SQL to create, populate, and query tables, I assume you just want to get the database setup and maintenance done with the least cost and effort. And I'm assuming you have some startup capital but don't want to overspend. Avoid the commercial databases: Oracle, Sybase, SQL Server. Avoid Windows. Don't buy hardware. Contract for a managed colocated server and firewall (and backup services) with a reputable enterprise-grade vendor such as NTT; they'll install Red Hat Linux on it and give you the root password. Have them configure the firewall to allow remote database access. You'll create user accounts, install and start PostgreSQL with network access allowed, and disable root SSH; any decent freelance Linux admin can do this for you in a couple of hours. Now you can install any of a variety of desktop Postgres clients on your laptop or workstation, connect to the database server, and run your queries. With just a couple of clients Postgres is essentially maintenance-free and scales to very large tables.
posted by nicwolff at 4:45 PM on January 23, 2008

[Might want to remove your full name from your profile.]
posted by loiseau at 4:55 PM on January 23, 2008

sounds like you know enough for a startup. google anything you don't know. go with a rdms that has heaps of users, sql server/posgress/oracle/sybase- or wahatever you know. You won't need a dba till you get big.

Be careful the company you are leaving doesn't try to sue your startup like the company I left tried to sue my company.
posted by mattoxic at 5:42 PM on January 23, 2008

You may want to consider getting someone like RackSpace involved. You can offload the actual hosting of the database to this company and be assured that a competent "database guy" will be available 24-7 to help you with the server-side things, while allowing you to concentrate on the data and the queries.

I would also second that you may want to consider open-source alternatives to the very expensive proprietary databases. MySQL is extremely flexible, and if you get a competent database administrator involved from the ground, you can get a new solution up and running fairly soon.
posted by odinsdream at 6:08 PM on January 23, 2008

With just a couple of clients Postgres is essentially maintenance-free and scales to very large tables.

I would second the recommendation of Postgres here. For the workload you've described (large database, light on inserts/updates, heavy on complex queries) I would recommend you find a Postgres DBA to tune the initial install - by default it is extremely conservative with memory use, and you can get a huge performance boost just by telling it to actually use all the memory that's available.
posted by russm at 6:35 PM on January 23, 2008

Disclosure: I work in banking and write software for our own internal use. I sit across from our DBA, and database locks are a frequent conversational item. We use MSSQL for our transactional warehouse, Postgres for anything that's web-connected.

I disagree with the anti-Microsoft folks here. MS-SQL is dead-simple to get up and running, and the stored procedure language is basically the same as Oracle. That's good news if you plan on ever hiring someone to write or maintain that code. Say what you want about Winblows or Micro$oft, but leave MSSQL out of it. MSSQL is quite simply a really good fucking product. Unfortunately, it costs a crapload of money.

Locking is serious stuff... You can mitigate against this by specifying NOLOCK in your SELECT queries (MSSQL), and by avoiding tempdb like the plague. There are all kinds of different locks--shared locks, index locks, and the really bad table-level locks, which can bring your system to its knees in a multi-car-pileup of pending transactions.

Postgres has its own stored procedure language, so any existing SPs will have to be re-written. But Postgres has the advantage of being free. That's a hell of an advantage.

How can I quickly get set up (say, in a 2-3 week time frame? or is that unreasonable?) and still have something that meets my needs.

You'll have to normalize the fuck out of it, which can take months. Hardware-wise, you can definitely benefit from boatloads of RAM, which means you'll need a 64-bit operating system to take advantage. 16 gigs would be nice, but 12 is easily do-able. You'll need a motherboard that can support that much RAM. Make sure it's ECC, as well.
posted by Civil_Disobedient at 8:58 PM on January 23, 2008

I recommend avoiding MS SQL Server not because of any flaws in the software but because it's expensive and requires Windows which is expensive and leads a startup into a whole world of expensive commercial software whose administrators all seem to need expensive training and certification...
posted by nicwolff at 7:34 AM on January 24, 2008

First, thanks to everyone who's responded. Much appreciated.

Since you seem to know enough SQL to create, populate, and query tables, I assume you just want to get the database setup and maintenance done with the least cost and effort. And I'm assuming you have some startup capital but don't want to overspend.

nicwolff, this really sums things up best. though realistically speaking, the cost of some data services i'll have to purchase will likely dwarf the cost of setting things up on the DB side. you mentioned not to buy hardware: really? the services you and others mentioned will allow me to, for example, get some raw data in over my PC, save it on a network drive, and then through my linux env access it and fbcp/load it into a table? (i don't mean to sound surprised - i'm just looking for confirmation. wouldn't want to walk down that road and then realize that there are things i can do now that i can't do in the new setup)
russm and odinsdreams, your comments are really helpful as well

mattoxic: you said i won't need a dba till you get big. Is that really possible? I've had situations occur at times where I start a query/update only to realize I screwed up my joins - often in these cases even after i kill the query it still shows up as active in an sp_whor - in such cases I have to call our DBA and have them kill the query on their end. how could i live without a DBA in such situations?

to clarify some other comments, in case they change things at all: i'll definitely be heavy on updates, no question about it. right now all the sql work I do is in a linux environment, through red hat, and it's been good to me. looking to stick with that sort of setup. i make zero use of stored procedures. won't have any web connectivity needs.

thanks again to everyone
posted by raz5 at 2:24 PM on January 27, 2008