Part 2 of 2: After You've Signed Up
Welcome to part two of this article in which Jon Reade tackles the issue of choosing a web host for your database-driven web site. In the first part he looked at questions to ask before committing your web site to a new host, and will now look at measuring whether your difficult choice of selecting a new host has been the right decision.
Whether you are a DBA, a database or web developer, you will be looking for a host which offers excellent technical support and customer service, reliability and good up-time figures, as well as an adequate amount of disk space for the web application and a useful size database. The good news is that there are plenty of hosts which offer good value web space with a SQL Server backend. The bad news is that choosing just one from the many thousands that will be the right one for your needs, can be a daunting and stressful task. Once you have read through part one, completed your research and made a choice, you need to ensure you're getting what you wanted.
Once you're connected, check what service packs your new web host is running, both on Windows and SQL Server. The technical staff that set up new customer packages and support them should be experts on Internet security as their business depends upon it. However, they're also the first line of attack for hackers, so they need to be on top of the latest service packs and hot-fixes.
SQL Server Security
Check out what you can do with your hosted SQL Server. You should really be asking these questions before you sign on the dotted line, but it's good to be sure you have what you expect before you pay your first month's hosting costs. Does it allow the use of extended stored procs, such as xp_cmdshell? Does your account have a null password? Can you see other people's data? Can you see other databases? If so, can you work out who they belong to? If the answer to any of these questions is yes, other people can probably see or manipulate your data. A good web host will give you a database name which is in a sequence of numbers and letters, and makes it impossible for an outsider to know which database belongs to you. You might get a database name such as db1732, and the next customer to buy a database will be db1733 - you may be able to see that they exist but you won't have a clue who they are. More importantly, you will be anonymous to them too.
Encrypting Sensitive Data
Very persistent hackers are not unknown to buy an account with a particular host in order to target a particular business. Ask yourself this question: if you're not happy with other businesses seeing your data, are you happy with the host seeing it? If not, you need to consider encrypting at least your sensitive data. Encrypting your own data can lull you into a false sense of security, so ask the host what encryption components they provide, and try to find reviews of them. It may not be just embarrassing if it becomes public knowledge that your company has leaked sensitive information, especially if it is customer data. It may irreversibly damage your company's reputation and put you as the DBA in an awkward position with your job, so raise the issue.
Once you have your web and database package all ready to go, you can then verify your new server's configuration. How much memory does your new database server have? How many customers does it support? When the company I work for joined our last web host, only 30 customer databases were supported on a SQL Server 2000 box with dual CPUs and 2GB of RAM. But through either incompetence or cut costs, the database numbers grew, and the database server got cut down to a single processor box with less than half a GB of RAM. Eventually OS paging took over and performance dropped though the floor.
If you can run it, xp_msver offers some useful information which you may want to review each month. Alternatively, sp_who/sp_who2, and sp_databases provide you with some useful information about connections and databases hosted. Also check where your transaction log is located - sp_helpdb '' reveals whether it is sitting on the same drive as your (and probably many other) data files - it should be on a separate drive if the host's DBAs know what they're doing and you want to get the best performance out of your database.
You will want to look at how well the web and database servers are performing once you have created your web application and database. You'll probably not be able to connect to SQL Server using Performance Monitor, but you can use sp_monitor, or set up a job to query the spt_monitor table which will give you an indication if your server's being thrashed.
Monitoring it over a 24x7 period is a good idea, as it will give you a better feel for how heavily loaded it is over a typical week. Your company's web developers should have a good idea how well their application is performing through some basic tests, and know whether or not there is a lag in response time. You may also want to consider employing a third party monitoring service to tell you how responsive your web site is from various points in the world and over a longer time period than your developers can cover - these cost money but give a more global perspective and save in-house work. If your web site is particularly busy and a lot of revenue is generated from it, such a service could be a worthy investment, even if it is done every few months, just to make sure things are continuously ticking over at a steady rate.
Web Site Monitoring
You want to make sure your users have a good experience using your web site and database as much as possible. If you have a permanent Internet connection, then set up and schedule a SQL Server job on a local 24x7 server which will periodically kick off a query on your new database host. This should be a query which is typical to one which your site might execute. In addition, the job should also store down the time before and after the query executes. By taking the difference between these two times for a week or so, you can easily plot the times and days of the week when your new host's database server is under maximum load.
Using this data, you can then test if the database load is causing problems for your site at peak times by visiting it yourself and testing how responsive it is, particularly concentrating on pages which involve longer running selects and updates. Combining this information with your web logs gives you a powerful tool with which you can spot if visitors are dropping out from your site during these times because of slow database or web server response times.
Contingency and Communication
Try to overlap your existing and new hosting plans. If you experience problems, your web site and database can continue to operate until they are resolved. It's better to loose a month's hosting costs than loose customers. A slow site is better than no site.
Move a copy of the site and database over to the new host a few weeks before the "public" changeover. This allows you to iron out changeover problems, and thoroughly test what will soon be your new live database and web site. Both the DBA and the webmaster should be involved in this process.
Tell your users when it's going to happen, and have a page ready which tells them that the site is currently down for moving, when they can expect to see it back up, and how to contact you if it isn't. Use newsletters to let your more dedicated users and customers know what you're doing and why. Tell them your web site may be unavailable for a few hours or a day or two, but that the downtime is in their long term best interests as you are moving the site to give them better customer service and that they are your number one priority.
Make sure you keep using the tactics above to continuously review how well your host is delivering on its promise and serving up web pages to your customers. I have experienced hosts that change ownership, replace employees and outsource abroad, downgrade customer support services, move servers to other countries and have far too frequent downtime. At first when you sign up things may be running really well, but if you find the host starts to let you down, then consider moving, particularly if your host is unresponsive to complaints. My company is now with a host which we are currently very happy with. But it took many years and several changes and upheavals to find the right one, but we continue to monitor their performance.
- SQL Server Connectivity - can you connect to your database using Enterprise Manager and Query Analyzer?
- Console Management - make sure you can administer you web site online so you can add or remove services and check on your billing status, bandwidth consumption and web logs.
- Backups - how often are they performed and what is backed up?
- Disk Space - not just for the web application, but how much space does your database get? Think about how much it is going to grow over the years.
- Bandwidth - what do you get with your package and what happens if you exceed this?
- Dependencies - work out with your webmaster if any code is dependent on a specific version of an operating system or database.
- Geographic Considerations - time zones, exchange rates, language, technical support times, regional settings, collation sequence - what are the advantages and disadvantages of these?
- Outage Times for SQL Server and IIS - what is their SLA on uptime and is it enough for your needs?
- Host Reliability- have they been in business long enough to have a steady and dedicated customer following?
- Windows or Unix? - make sure you get the support you need for your OS.
- Finding a Host - ask around colleagues and friends and search the net for customer reviews.
- Payment - make sure you can pay monthly or be able to get your money back if you need to swap host. And have the ability to move your site quickly. Do they offer a 30-day no-quibble money back guarantee? This is a big weight in favor of a host when making a choice.
- Host or Reseller - go for the original every time!
- Domain Names - register your domain names yourself, and then point the tags to the new host and retain control so you can switch with minimum loss of business through downtime.
- Service Packs - ask how often and when they're applied before signing and check afterwards.
- SQL Server Security - remain anonymous and make sure you can't see other customer databases and that they can't see yours. And make sure your account has a password.
- Encrypting Sensitive Data - protect yourself and your customers by encrypting sensitive data. Check what encryption components your host offers.
- Server Configuration - check that SQL Server is configured in the way you expected it to be.
- Server Monitoring - monitor the responsiveness of your database server and your web site. Keep the records, they may be useful evidence for your host to troubleshoot a problem.
- Web Site Monitoring - execute regular queries from your web site against the database to ensure your users are getting a fast enough response.
- Contingency and Communication - assume the worst will happen. Keep you customers informed before you move your site and provide alternative channels of communication with your business in case of a disaster.
- Be Agile - watch your host and be prepared to move to another if they do not meet your expectations.