SQL Server on the Web

SQL Server on the Web

icrosoft SQL Server is a great database for Web sites, especially Web sites deployed on NT and using Microsoft’s IIS server. It’s easy to install, relatively cheap, and interacts well with IIS. In addition, having all your tools from one vendor helps simplify obtaining support.

Although SQL Server is easy to use, it is also a sophisticated product that has requires maintenance and tuning to obtain the most from it. The trick in using SQL Server properly is to recognize these requirements and plan for them from the start of the project and not wait till issues arise in production. Here’s a checklist of items to cover to ensure that your project runs as smoothly as possible.

1. Check your Hardware and Software Configuration
As a “techie” I’m as enamoured by hardware as anyone. However, when defining the hardware requirements for SQL Server it is important to weigh where most performance gains can be realized. In many cases, too much emphasis is placed on having multiple CPUs and large amounts of memory without paying enough attention to the configuration of the disks. In general, the more disks and controllers you have, the faster your I/O will be. For a good view of how to increase hardware performance, take a look at the Microsoft SQL Web Site under TPC benchmarks. You can find diagrams of the systems used to produce the benchmarks. Some very respectable numbers were produced on systems with just one CPU.

When SQL Server is installed, it uses a default configuration that is suitable for the most minimal requirements. As an example, only sixteen megabytes of ram is allocated to it. The TEMPDB database is set to just 2 megabytes. Have someone knowledgeable look over your configuration and set it up according to your environment.

2. Create a Backup and Recovery Plan
Database backups are not done like file backups. There are specific commands that need to be executed that create a backup of the database. Make sure that you have someone responsible for running the backup and testing it periodically to ensure that it can be restored. This sounds obvious but I’ve seen it overlooked too many times. One client I worked with was using a very large service provider to host his site. He insisted that they were backing up his database for him. As it turned out, they had never done so and left all the database administration tasks to their clients. You should also be aware that under version 6.5 (seven will change this) performance on your database drops significantly while a database dump is being performed. Plan on scheduling it for your least busy hours.

It is important to have a written plan that will cover all contingencies. How will spare hardware be obtained in the event of a crash? Who will be responsible for keeping track of the backup tapes? Creating a plan before a crisis occurs will allow you to deal with the problem in a calm and efficient manner.

3. Create a Schedule for Maintenance
SQL Server needs several maintenance commands to be executed periodically. These commands are vital both for performance and to ensure that the database has not suffered any corruption.

For example, the command UPDATE STATISTICS tells SQL Server to sample the data in your tables. It uses this information to decide which indexes to use during the execution of SQL statements. The schedule for running this command depends on how much data is being inserted or updated in your system. If this command is not executed periodically, performance will begin to drop dramatically.

DBCC (database consistency check command) commands serve the same purpose as chkdsk does on a hard drive. It performs an internal check to make sure that the database is not corrupt. It doesn’t have to be run as often as update statistics. However, it should be executed at least once a month. Depending on the size of the database it can take several hours to execute. In addition some of them require no updates to be happening at the time.

4. Design Your Schema Correctly
Web sites seem to change constantly. Just try to find the same item at the Microsoft site twice in a row. However, a database schema can not be changed as easily as a Web page. You also can’t design a schema on the fly. A successful design process includes clearly defining what the information to be stored means and how it is to be used. A well-designed schema will be easy to work with and maintain. A hasty and ill-designed schema will be difficult or impossible to work with and probably will not be able to support the functional requirements.

5. A Word on Power Searches
A frequent feature on Web sites is the ability to do a power search of the data stored on that site. Just remember that the more “powerful” a search–for example, the more tables and rows that the query touches–the slower the response time will be. A query that touches every table in your database and every row will be slow no matter how well you’ve written your code. Try to limit queries to what is actually useful.You can also divide a power search into several smaller and less all-encompassing searches.

6. Make Sure You Use the Proper Indexes
Proper indexing is critical to performance. Two decisions must be made when deciding on indexes for a table: which columns to index, and which index should be clustered. A clustered index essentially orders the data in the table according to the index. Queries that use a clustered index can get a terrific performance boost. However, by definition, you can only have one clustered index per table.

7. Secure Your Server
Don’t, and I say again, don’t have your scripts execute under the sa id. The sa id has total control over the SQL Server environment. Instead, you should create an id that has the minimal rights necessary to get the job done and use that id for your scripts. Also remember to change the sa password immediately after installation (upon installation it has no password).

8. Obtain the Services of a Database Administrator
Even after the system is in production it is a good idea to have someone monitor performance, check the results of the maintenance routines and backups, and in general ensure that every thing is running smoothly. This does not mean that you have to hire someone full time for your system. Your host provider might be willing to provide these services for a fee. However, you want to make sure you have someone on hand before you have a sudden need for him.

devx-admin

devx-admin

Share the Post:
Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023,

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed are at the forefront because

Sunsets' Technique

Inside the Climate Battle: Make Sunsets’ Technique

On February 12, 2023, Luke Iseman and Andrew Song from the solar geoengineering firm Make Sunsets showcased their technique for injecting sulfur dioxide (SO₂) into the stratosphere as a means

AI Adherence Prediction

AI Algorithm Predicts Treatment Adherence

Swoop, a prominent consumer health data company, has unveiled a cutting-edge algorithm capable of predicting adherence to treatment in people with Multiple Sclerosis (MS) and other health conditions. Utilizing artificial

Personalized UX

Here’s Why You Need to Use JavaScript and Cookies

In today’s increasingly digital world, websites often rely on JavaScript and cookies to provide users with a more seamless and personalized browsing experience. These key components allow websites to display

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

why startups succeed

The Top Reasons Why Startups Succeed

Everyone hears the stories. Apple was started in a garage. Musk slept in a rented office space while he was creating PayPal with his brother. Facebook was coded by a

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists