Friday, July 24, 2009

A Google-like Full Text Search

Thanks to Internet search engines like Google and Yahoo!, your search application users are more sophisticated and demanding than ever. Your application users have a wealth of search application knowledge that they're probably not even aware of. Ask one of them to find a website dedicated to their favorite band or movie and odds are they'll have their browser pointed to that page in a matter of seconds.

You can tap into the self-education that comes from countless hours spent at home trying to locate information on the Web and turn it to your advantage in your own SQL Server-based search applications. With just a little bit of code you can help reduce your training costs and give your users an easy to use interface that will make them want to use your search applications. In this article I'll explain how to convert Google-style queries to SQL Server's full-text search CONTAINS predicate syntax.

Tuesday, July 21, 2009

Saving Time with Proactive DBA Monitoring

A typical day for the DBA usually starts with an overall health check of the database servers in the network. No matter how big or small the environment, the database administrator usually finds himself doing more or less the same type of checks every day.

If the DBA is responsible for ten production SQL Server instances, each hosting three databases on average - it would be thirty production databases to look after. Each morning the DBA would want to know if the instances are all up and running and accepting user connections. Next, he would probably want to see if overnight backups were all successful. When that is ascertained, perhaps the attention would turn to scheduled jobs. There may be regular processes that are critical to the business – like an SSIS package or a reporting job. If nothing has failed in that front, it would be time to check the Windows Event Log and the SQL Error Log for any suspicious looking error messages. Finally, it will be prudent to see if replication has failed or mirroring has frozen or log shipping has broken.

These are only part of the day-to-day health checks and yet when done manually, can take a significant amount of time.

There are a number of tasks that you as the DBA can automate to make life easier and save time. Smart DBAs would always try to proactively check database and server health without waiting for something to happen or someone else reporting about them.

In this article I will try to present some ideas about how some of the daily database health checks can be automated and turned into proactive monitoring. Although database monitoring is not a new idea and there are quite a few third party tools in the market, it may be cost prohibitive for some organisations. The alternative is to build a custom monitoring solution.

Thursday, July 9, 2009

Map IP Address to a Geographical Location

On the Internet there is the concept of web site analytics that helps track all the visitors' activities and usage patterns. One of the dimensions to track is the geographical information of the visitors, which can be obtained by using the IP address information that is collected when a user lands on a Website. In this article, I will describe a simple process that enables your reporting system to display the geographical information of the visitors.

Wednesday, July 8, 2009

Automating Performance Monitor Statistics Collection for SQL Server and Windows

Problem
You have about 100 SQL Servers installed in your production environment. You have performance problems on few of the servers, but they happen during the time when you are not watching the servers. So, how can you automate performance statistics collection on all the servers around the clock so we have the statistics for 24/7/365.

Solution
You can use the built-in windows utility "logman" that comes free.

Logman is the command line utility for Perfmon. Thru logman, it is easy to automate perfmon data collection which I will show you below.