Database Consultancy

[simon4amiee]

Possibly a long term thing, but with regards to the database. I have observed for months now the ongoing problems and changes which constantly occur. The explanation I am given is that to transfer over to a server would be time consuming and may have many many unforeseen impacts.

My suggestion is if sometime in the future if there is small amount of money available, we employee I professional database developer/consultant, who with many years experience would may be able to optimise it so (a) it runs faster, and (b) would be able to integrate new methodologies.

Having a top developer working alongside our developer would only benefit us, and by spending some money to employ someone for a few weeks/a month short term may dramatically assist our department long term.

Do consultancy firms/developers charge for the initial visit?

What is the rough price per week to have a developer in?

[Astaroth]

What is "the database"?

Different consultants have different charging and billing practices. Some will absorb the cost of winning clients by having a higher per day fee, others will charge for initial meetings but then lower costs afterwards. Most fit in the first camp.

Most IT consultants I've known charge a day rate of somewhere between £500 and £1,000. Their level of skill/ experience is obviously a big driver of the cost but the shorter the contract the higher their fees too.

[edmondscommerce]

what database is it you are running?

This is a total "how long is a piece of string" question, but broadly I think you will pay top dollar for a top DB admin.

[simon4amiee]

Its a huge access database, but is being transfered over bit by bit onto a sql server by the developer and is constantly having problems with speed, and performance, and when something gets changed it always has the ripple effect on other things we generate from the database.

Imagine the NHS one trust we have half a million patients on it, then 1.75 million sample details and thats just 2 tables.

The developer is very clever and reads books after books after books, I am not up to that level, but I can see its not time effective to learn as you go. Although the database does many great things Im sure a proper developer could speed it up and point out its many floors!

[awebapart.com]

As a rule of thumb, applications designed specifically for MS Access are better suited for single user local databases, they don't scale very well up to networked MS Access databases, and they don't scale well up to SQL Server.

You are dealing with 2 different architecture models, a client-server model with SQLServer, and a file-server (http://msdn.microsoft.com/en-us/library/aa165278%28office.10%29.aspx) model with MS Access. If the overall application has been designed for the local file server model with MS Access, then you will run into problems trying to just replace the database. Often it is not just a case of changing the database, but also changing the application too, to change it to an application that is designed specifically for a client/server model.

The most common example of where MS Access databases don't scale to SQLServer is having forms in your application which browse/scroll large record sets. This can work for local access databases, not so good for networked MS Access, and definitely not so good browsing cursors (http://www.codeproject.com/KB/database/sqldodont.aspx) for SQL Server.

[LicensedToTrade]

After reading the first post I felt like I had joined half way into a conversation. Sort of like when you start discussing something you have been thinking about in your head without giving your audience the benefit of filling them in on what exactly you are talking about. I'm lost :|

[DBMark]

Its a huge access database, but is being transfered over bit by bit onto a sql server by the developer and is constantly having problems with speed, and performance, and when something gets changed it always has the ripple effect on other things we generate from the database.

Imagine the NHS one trust we have half a million patients on it, then 1.75 million sample details and thats just 2 tables.

The developer is very clever and reads books after books after books, I am not up to that level, but I can see its not time effective to learn as you go. Although the database does many great things Im sure a proper developer could speed it up and point out its many floors!

Hi. First thing I would stress is that in today's market you really shouldn't need to spend more than £300 a day for a good Database optimizer or admin. Just advertise on Gumtree and watch the applications roll in.
Second, the numbers in the database tables you quote really aren't that large. It's the way the database is structured, and especially the join conditions between fields in each table that affect performance (although the number of records required by each select affects performance too). It's perfectly possible to have a table of 10 million records but have a select bring back records in milli-seconds, so long as the indexes are set up correctly and you're not bringing back more than a small sub-set of the table.
Thirdly - use the database bloke to tell you how to design the database efficiently (he will of course have to liase with you and the developer). That's half the battle. The developer (if he or she is half decent) will then be able write code to extract the data in an optimal way. If they need help they can look on http://stackoverflow.com/ (search on tag sql-server) or on various sql server web sites. You can of course do a general google search on "sql optimization" but good luck wading through all those sites!
Lastly - if you do get someone in, try a week to start with. I can't be 100% sure as I'm not on site, but you should be able to get excellent advice, feedback and options within a week or maybe two of their starting and carrying out analysis (this obviously depends on how complex the system is, and what reporting requirements are made on the system) .
HTH ;)

[MartCactus]

Hi. First thing I would stress is that in today's market you really shouldn't need to spend more than £300 a day for a good Database optimizer or admin. Just advertise on Gumtree and watch the applications roll in.

Of course you'd need to check those for quality. Just because you get dozens of applications at bargain prices doesn't mean those people are going to be competent.

Also it appears OP wants someone who is able to visit in person (ie not someone sitting in a bedroom in Albania). That will affect cost too.

[DBMark]

Of course you'd need to check those for quality. Just because you get dozens of applications at bargain prices doesn't mean those people are going to be competent.

That applies to all services. The difference is that whereas with say a plumber you'd ask around your friends for a recommendation, with an IT contractor / consultant, you need to check their previous history/ employers and ask for a reference(s). Check out the IT guy's website. Together with your developer you should be able to ask the correct questions to weed out the novices. Even so, the OP should be able to get quite an experienced DB guy in the UK for 300 a day (I know because I'm one myself)

There is something else that the OP should be looking at - familiarity with the business rules and jargon that's used in their industry. It can obviously delay the initial analysis if the Contractor has to keep asking "What's a SHA?" or "Specialty - What does this refer to?". Naturally the jargon stuff can be picked up, but it adds to the time spent (and thus money).

[Interconnect IT]

There's DB guys, and there's DB guys.

And there's databases, and there's databases.

Setting up a simple relational DB isn't an especially difficult task to take on, but in complex implementations you often have tricky security views involved. Then there's analysing code - I know a very good DBA... £2k a day. But he can't code, so when he optimises a DB he can only work on the DB itself. So a report that takes 12hrs to run, he might be able to improve by 50%, whereas a good coder might take a look, refactor the code, and have it running in 30s.

I used to be that last guy, and I can assure you that if I was still doing it I wouldn't be looking for work on Gumtree - it would be finding me instead. Seriously - if you want somebody good, you have to do some work to find them because they don't need to find you. They may work at different price points - I never earned as much as some because I preferred interesting jobs over boring but highly paid ones.

The hard part is for a small business to be able to afford top guys - I generally advise that for most businesses their priority should be to find software that's already been written which solves their problems. In my view any small to medium business looking at custom code is fooling themselves unless they work in a very specialist niche. And if they're in that niche they're probably making plenty of money, so they should go ahead and get spending on top guys :-)

[stasilo]

There's DB guys, and there's DB guys.

And there's databases, and there's databases.

Setting up a simple relational DB isn't an especially difficult task to take on, but in complex implementations you often have tricky security views involved. Then there's analysing code - I know a very good DBA... £2k a day. But he can't code, so when he optimises a DB he can only work on the DB itself. So a report that takes 12hrs to run, he might be able to improve by 50%, whereas a good coder might take a look, refactor the code, and have it running in 30s.

I used to be that last guy, and I can assure you that if I was still doing it I wouldn't be looking for work on Gumtree - it would be finding me instead. Seriously - if you want somebody good, you have to do some work to find them because they don't need to find you. They may work at different price points - I never earned as much as some because I preferred interesting jobs over boring but highly paid ones.

The hard part is for a small business to be able to afford top guys - I generally advise that for most businesses their priority should be to find software that's already been written which solves their problems. In my view any small to medium business looking at custom code is fooling themselves unless they work in a very specialist niche. And if they're in that niche they're probably making plenty of money, so they should go ahead and get spending on top guys :-)
you draw a really fascinating picture of some mysterious "top guys" charging 2K a day. :)

not necessarily you need to hunt for top guys in the dark because "they don't need to find you". :)
most likely that tables are not indexed but it's not a rocket science. but could be not.
is that MS SQL you have?

[stasilo]

What is "the database"?

Different consultants have different charging and billing practices. Some will absorb the cost of winning clients by having a higher per day fee, others will charge for initial meetings but then lower costs afterwards. Most fit in the first camp.

Most IT consultants I've known charge a day rate of somewhere between £500 and £1,000. Their level of skill/ experience is obviously a big driver of the cost but the shorter the contract the higher their fees too.
Let me know which sql server you are running and PM if interested. I do web and database development.

[DBMark]

you draw a really fascinating picture of some mysterious "top guys" charging 2K a day. :)

not necessarily you need to hunt for top guys in the dark because "they don't need to find you". :)
most likely that tables are not indexed but it's not a rocket science. but could be not.
is that MS SQL you have?

I agree (to some extent) with stasilo. There are plenty of people around with extensive knowledge of databases who charge nothing like 2000 a day. There are a standard set of procedures to look at slowly executing code - yes it could be indexes not set up or not used properly. For example in many databases if you set up a composite index on 2 columns (col1, col2) and then use a select statement that only quotes col2, you're not getting the full benefits of that index (maybe better to create a second index). Also check that you're not performing a function on the field in the code - again if you are you're not getting the benefit of the index. You can get around by either a) denormalizing the data and saving the field again but with the function value already applied (or in some cases merely by splitting the text field into 2 - for example post code prefix and suffix, why search on where postcode like '%4EE' when you can search on postcodesuffix = '4EE') or b) rewriting the code more efficiently.
There are also other things that can effect efficiency, such as size of columns, bandwidth when using remote servers, number of columns in a table, making sure statistics are updated on tables etc. Running a Query Execution Plan on the statement can often show you straight away where the bottleneck may be.
For a load of optimization suggestions try this link (and forward onto your developer!)
http://stackoverflow.com/questions/18783/sql-what-are-your-favorite-performance-tricks
Lastly, if you really need someone with database experience but at a decent price, send me a PM. I'll be only too happy to assist.

[awebapart.com]

There's DB guys, and there's DB guys.

And there's databases, and there's databases.
Yes there's databases and there's datatabases... but unfortunately then there's Access, which is 3 things in 1, a front end development tool, middleware, and a database. And because Access mainly works well as a locally run single user database you often have to make changes to all 3 components if you want your migration up to SQLServer to work well.

I'm assuming the OP's original application is all Access, with Access used as the front end too, since the OP doesn't mention any other tools.
a good coder might take a look, refactor the code
So yes with typical Access migrations to SQLServer I agree that it is usually more than just a database change, but an application coding change and sometimes a change to the way the middleware is used too.

if... we employee I professional database developer/consultant... with many years experience...

Having a top developer working alongside our developer would only benefit us...

What is the rough price per week to have a developer in?
I think you are right to be talking about a developer, with a mix of skills, rather than just a database only person. With migration projects in general you do need a mix of skills, i.e. experience of both the old technology and the new technology you are migrating to, but with good Access to SQLServer migrations you also need the skills to make overall architecture changes (the way you use the database) and changes to the front end/middleware too. I doubt that your migration problems can easily be fixed by simply adding an index at the database end, database only performance issues can be fixed this way, but those types of fixes aren't usually enough to sort out the other complex issues you typically face with an Access to SQLServer migration.

A lot also depends on how the original application was developed and the skills of the original developer. With the easy to use tools that Access provides, it does open up Access development to the 'power user' level of user (i.e. not a professional developer) as well as the professional developer, so the quality of the solution can vary. With a move from file-server (http://msdn.microsoft.com/en-us/library/aa165278%28office.10%29.aspx) technology to client/server server technology you also need a different kind of mindset to make best use of the database in each case.

Ideally you need someone who specialises in Access to SQLServer migrations, and a quick google reveals that these database developers (http://www.aldex.co.uk/sqlupsizing.html) sound like they might know what they're talking about and might be worth contacting.

Another option is to advertise for a contractor on www.jobserve.com (http://www.jobserve.com) - if you search for jobs on this site selecting IT as the industry and Access SQLServer migration as the skill you will find some other companies wanting help with their Access to SQLServer migration projects too!

[garyk]

As said you could advertise for a contractor on Jobserve, typical rates for a good DBA are 300-400 per day, perhaps slightly more if you need someone with TransactSQL knowledge who can create stored procedures etc to handle some of your business logic.

At least you are moving in the right direction, As awebapart states Access should only ever be used for small scale implementations. Unfortuntely as you have found many small scale DBs grow over time and outgrow Access.

Just make sure you get something with solid commercial SQL Server experience, not just 'database' experience.

Gary

[DBMark]

As said you could advertise for a contractor on Jobserve, typical rates for a good DBA are 300-400 per day, perhaps slightly more if you need someone with TransactSQL knowledge who can create stored procedures etc to handle some of your business logic.

At least you are moving in the right direction, As awebapart states Access should only ever be used for small scale implementations. Unfortuntely as you have found many small scale DBs grow over time and outgrow Access.

Just make sure you get something with solid commercial SQL Server experience, not just 'database' experience.

Gary

I'd agree with that point. Someone who's worked with major RDBMS's such as Oracle and SQL*Server, as well as having done migration work from legacy projects (incidentally I have done all this for varied large clients), will be able to carry out the work a lot faster (thus saving you money) but also design the tables and give advice so that you'l be able to utilise your systems far better in the future and cope with new demands easier. Again (as I mentioned earlier) it will help to use someone who understands the jargon you use in your business. Not a show-stopper, but it can hold things up if the IT guy is totally unfamiliar with the jargon and what data's really important, as they will need to spend more time on the analysis/asking questions side of things.

[Interconnect IT]

@stasilo Most responding here have no idea whether they're looking at a major database with terabytes of very complicated data, or a simple address book.

My approach with these things is NOT to leap in full of confidence and cheapness because there's no way of knowing, for sure, what's being dealt with. It's for the OP to decide whether his problem is simple or complex and what level of staff to deal with. It looks like the requirements are more complex than can be assessed on this forum. Assuming that the DB is mission critical to the company you could have millions at stake. Getting an admin in from source like Gumtree which isn't known for its reliability is perhaps not the wisest approach.

[DBMark]

Getting an admin in from source like Gumtree which isn't known for its reliability is perhaps not the wisest approach.You're likely to get the same mixed bag (some experienced, some very smart, others not so) reply to a job ad on Gumtree as per Jobserve (or Monster or Totaljjobs or whatever site you prefer). Jobserve was held to be the best site for many years, but to be honest IT contractors and consultants look across various sites, often using aggregration sites such as Indeed.com, especially in the current market. You're going to get the same mixed bag of applicants whichever site you advertise on. I think the OP should be looking at 3 main criteria if they get someone in (in order of importance):
1) Experience in commercial database analysis and design. A bonus if they've worked on public organisations and/or healthcare places.
2) Experience and expertise in optimization, especially if it includes work on SQL Server and TSql
3) Experience on working on migration projects. Although I've worked with Access, I don't think this is a killer requirement, as the aim is to get the most out of the current new system (including possible new functionality) rather than replicate all that's currently done by the old system. There are of course resouces, such as the link given above by awebapart that helps with migration from Access, plus all the help Microsoft provide (which is good too, as Sql*Server is their product).
Funnily enough, I think if the OP gets their developer (well allows them time, which may not be an option I realise) to study the links given above, they may well be able to apply the lessons learnt and thus iron out the current bottlenecks. Only they can judge whether they'll need (or can justify) getting in outside help.

[DataWise]

Simon:
Imagine the NHS one trust we have half a million patients on it, then 1.75 million sample details and thats just 2 tables.Hi Simon. Can you post the headings of some of the tables (the list of column names for each table), along with about a dozen rows from each table? You don't need to post the entire database ( :) ), just, say, half a dozen table fragments.

Paul Reed

[Interconnect IT]

A couple of million rows in a table is nothing. There's databases with billions and even trillions of records in them - think financial transaction records, etc. Adds up.

[DataWise]

Yes I know. But I just want to see some table fragments, including the headings.

Paul Reed

[stasilo]

As said you could advertise for a contractor on Jobserve, typical rates for a good DBA are 300-400 per day, perhaps slightly more if you need someone with TransactSQL knowledge who can create stored procedures etc to handle some of your business logic.

At least you are moving in the right direction, As awebapart states Access should only ever be used for small scale implementations. Unfortuntely as you have found many small scale DBs grow over time and outgrow Access.

Just make sure you get something with solid commercial SQL Server experience, not just 'database' experience.

Gary
if someone charges you 300-400 per day and they don't know how to create stored procedures you should give him good kick in the head. it's a basic stuff.

[DataWise]

stasilo:
if someone charges you 300-400 per day and they don't know how to create stored procedures you should give him good kick in the head. it's a basic stuff.Deciding 'why' and 'when' to create stored procedures is far more important than knowing 'how' to create them.

Paul Reed

[DataWise]

Simon. I should have said this before. If there are any personal details in those fragments, then you should probably replace them with fictitious details, just in case! :) But make them viable.

Paul Reed

[Interconnect IT]

if someone charges you 300-400 per day and they don't know how to create stored procedures you should give him good kick in the head. it's a basic stuff.

If they're a DBA, that's correct.

If they're a developer, then that depends. I always worked with code that had to be cross platform. You can't believe how painful that was, but ultimately you had to use principles that were valid no matter which DB engine was in use. Building stored procs was a big no-no, and I managed ten years like that. Developers that did that would cause their clients no end of trouble when they did a migration - but most didn't seem to care, they were long gone by then :-(

[stasilo]

If they're a DBA, that's correct.

If they're a developer, then that depends. I always worked with code that had to be cross platform. You can't believe how painful that was, but ultimately you had to use principles that were valid no matter which DB engine was in use. Building stored procs was a big no-no, and I managed ten years like that. Developers that did that would cause their clients no end of trouble when they did a migration - but most didn't seem to care, they were long gone by then :-(
depends.
but a)we are talking about database professionals and b)any decent developer should be able to google and learn how to create procedures within few hours.

[Interconnect IT]

Don't forget, all, that what's being asked for is a DB developer, without a great deal of clarity regarding platforms in use, requirements, etc. What's really needed in the first step, in all certainty, is an analyst to work out what the direction should be.

[Tom McClelland]

Indeed, If what we're currently looking at is a complete application built in MSAccess probably the first requirement is for a developer to take the client-facing UI out of Access and into something like a VB.NET or C# winforms application that talks to the Access data and not touching the Access data storage solution at all.

THEN and only then think about migrating the data to another platform such as MSSQL. Access's ability to map internal table references onto external ones is very pretty, and can be staggeringly useful in certain situations, but is unlikely to give anything other than terrible performance in databases with millions of records, particularly in a native MSAccess application.

[glencooley.com]

OP. What is the frequency of this transfer? Is this a one time thing to move the data to a new system or is it a process that needs to be done at intervals?

Personally I would use either the Access to SQL Server upscaler dump all the records in a holding DB or Tables in SQL server and then write the import routines at that point. You could set up an SRSS service to import the data then run all the relevant routines to normalize the data to the SQL tables.

It simplifies things as you can transactionalise all of the processes to build in redundancy if there are errors.

You really need to explain the process more though as you may want someone with a mix of CLR experience and SQLS experienced rather a pure DBA.