SQL Server DBA - Database Administration Guru

latest project on pets dogs and puppies

2010-01-21T17:36:00.002-05:00

The last database i designed as a freelance database administrator is for dogs and puppy adoption site with all the fun stuff about dogs. I own two German sheperd dogs myself which are really cool to play with every evening.

The learning part about this project while working on puppies for sale website was the amount of text stored in the database. The huge amount of text data related to breed information facts and description. The existing developer who initially designed the database used varchar MAX for almost all the fields. Now the main part for me was to track all the data and see if i can fix some of this cowboy style database design.

Looks like the client is happy and all of the sudden site performance have increased, seperation of data and making table size small can really help which many front end developers does not understand specially when every query is select * from table. :)

MySQL Hosting

2009-04-09T15:03:00.003-04:00

MySQL is one of the most popular open source database system available for free (community edition). One of the biggest benefit of MySQL compared to other open source / free database systems is the availablity on all major operating system. Many companies are moving to MySQL server and demand of MySQL Hosting is increasing each day. There are several hosting providers out there offering cheap to expensive hosting for php and mysql but the reliability is the biggest concern.
I will recomment www.mysqlhosting.us for mysql hosting, only problem with them is that they offer windows based mysql hosting and no web hosting. The biggest benefit is free mysql database administration services.

SQL Server Hosting

2009-04-07T14:01:00.002-04:00

If you are using SQL Server in hosting enviorment then like all other web site owners you are looking for a better hosting company. There are many hosting companies out there which can provide you with database hosting in shared and dedicated enviorment but they lack expertise in sql server database administration and management.

In my recent research i found one sql server hosting provider company which also provides database administration services including design, monitoring and performance tunning. They do not support all features but hardly any small to medium size business is using all sql server features.

social networking and dating

2008-10-27T15:23:00.003-04:00

The sudden popup of all these new social networking and dating sites is crazy, now even for particular ethnic group, city or state.

All these sites are collecting more data then any other corporate collects on it's customers. The site My Life, Match.com, Arab Dating, eharmony and Indian Pakistani Dating and other social networking sites owned by large companies like google the orkut.

Is there are any end to it, these sites are from dating to friendship to social networking like each and every thing you can do after signup. :)

the fun of horoscopes

2008-10-27T14:40:00.004-04:00

So recently i optimized a database for an astrology website... huh ? wow it was a strange experience i am not a big believer of horoscope and astrology but i did bought two services from them. Just to be in good faith and future business :)

My experience did change my mind a little it, the dates were very close. Now i wonder how this thing work. But hey it was fun and i will say not a waste of money at all. :)

Youtube and SQL Server

2007-12-10T12:03:00.000-05:00

A friend of mine is creating few computer educational videos to post on Youtube, its kind of cool. There are many free video sites which can be used to educate users. If we check Google there are many online searches for education videos specially related to computers and helping others is always a good thing.

Real Estate Search Engine Optimization

2007-12-02T11:15:00.000-05:00

Another good page to learn about real estate search engine optimization, the one page foucs only on real estate services. Many sites are doing it on side or not providing enough information to do it by your self.

Visit it online real estate search engine optimization its on squidoo

If you wana learn more about SEO than there are some other sites as well to learn about search engine optimization.

VMWare Training

2007-12-01T23:41:00.000-05:00

Can we really use VMWare ESX server for database hosting ? I am personally not very sure so, starting the training to find out by my self. Many of my clients are using 8 way processing and as per my knowledge Virtual server only support 4 processor now the database which require optimization on 8 way processing how it will perform on 4 way processing. I will find out.

Real Estate SEO - Real Estate Search Engine Optimization

2007-11-17T22:10:00.000-05:00

Recently i needed a company to do my website search engine optimization, a real estate related website. I ofcourse i started my research online with keywords as real estate search engine optimization, real estate marketing and real estate seo to see who comes on top. The company which can come on top of results i had feeling they can bring my site on first page too, but that was not the case i had bad experience spent few thousand dollars and months but no results.

The next i found website4me.com real estate seo a old company famous for web templates not on first page for many keywords but excuse was too busy to work on there own website. Any way i again thought to give them a try because of 100% money back guarantee and after 2 months my site first started poping up on 4th page then 2nd and now on first acctually at 8th search result. Very impressive the art of search engine promotion.

I liked Florida Search Engine Optimization by http://www.website4me.com/ Try them for few months, results were very good.

Check if file exists using sql

2007-08-22T13:55:00.000-04:00

-- using the scripting object
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @objFSys int
declare @i int
declare @File varchar(1000)
select @File = @Path + @FileName
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
if @i = 1
print 'exists'
else
print 'not exists'
exec sp_OADestroy @objFSys

-- using xp_cmdshell
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @cmd varchar(1000)
create table #a(s varchar(1000))
select @cmd = 'dir /B ' + @Path + @FileName
insert #a exec master..xp_cmdshell @cmd
if exists (select * from #a where s = @FileName)
print 'exists'
else
print 'not exists'
drop table #a

-- using xp_fileexists
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @i int
declare @File varchar(1000)
select @File = @Path + @FileName
exec master..xp_fileexist @File, @i out
if @i = 1
print 'exists'
else
print 'not exists'

Service Oriented Architecture - SOA Definition

2007-08-10T15:48:00.000-04:00

Easy to the point Definition:

The Service Oriented Architecture (SOA) is a design philosophy which defines how a solution should be build.

SOA design approach allow organizations with multiple technologies and platforms to build solutions which are re-useable and accessible across all systems without rewriting of code. All business technologies and functions are treated as services, these services are build to last for long time but service configurations are build for change. This approach creates a loosely-coupled systems which can be changed without spending more money. A loosely coupled system allow business processes to change more quickly as they are not dependent on underlying IT infrastructure.

A service oriented application means writing a highly dynamic, collaborative, stable, dependable application. A application which can act as service to all other business processes/systems without rewriting. The service should not be written for any specific system it should be consumable by any system. The service should use standard protocols (SOAP, XML) to exchange messages between each other. The service should be re-useable, work independently and must be manageable.
Web Services, WSDL, Remote Scripting are common examples of SOA.

Kill SQL Server connections by username

2007-08-10T11:06:00.000-04:00

You can use following SQL Script to kill connections by a single user, make sure you set the username in @UName variable.

SET NOCOUNT ON
DECLARE @UName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
Set @UName = 'USERNAME'
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE loginame=@UName
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE loginame=@UName
END

SOA - Service Oriented Architecture

2007-08-09T20:31:00.000-04:00

Today in the meeting the director declared we will be using SOA design approch for all new development and in next few years all our systems will be using SOA. I guess IBM sales person did a good job, SOA is the future. I dont know our business logics never changes atleast not in last couple of years we are still using multiple technologies from SAP to cobol and one of our core application is still running on IBM AS400 which we have no plans to upgrade (none i know of). The new IT budget close to $90 million is approved so we need to spend money some where i think its a good time for me to ask for a raise in hourly rate too, well i have bills too...

One group of people gets confused that SOA or service Oriented Architecture is some kind of software which we can buy get training on it and boom its all done. The other group takes it as web service or re-useable componenet like COM objects, lot of questions were raised that we are still using it, but i guess hardly any one knew what is service oriented architecture of application is the IBM sales personals tried to define it in easy way that its the future and all enterprise business are using it.

SOA is a design concept for businesses in which all service providers (computer systems) use common standard protocol to talk to each other. The protocol which the sytems use is called SOAP very much like web services a common example i have seen is implemented by Paypal to accepting credits cards on merchant own websites.

I am still researching in more depth on SOA i guess i have used it in many applications with out knowing. I will write on it more later.

Kill all connections to SQL Server database using a script, kill sql server connections

2007-08-02T16:24:00.000-04:00

As a SQL Server DBA there will be time when you want to kill all the connections to database which can be for restore or to perform other database tasks. As a DBA you can put the database in single mode or dbo use only, but most of the time you are performing the task as automated process using scripts. You can kill all connections to SQL Server database using the following script, make sure you replace the database name by setting @DBName variable value.

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DATABASE_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

The above script can be used in SQL Query Analyzer or in SQL Server Agent Job.

Note: The job can not kill its own connection, if you are using SQL Sever Query analyzer to kill connections to the database then try to issue a use statement against any other database on top of the query, else you will receive an error.

USE master
GO

Its always a good idea to notify users before killing the connection to database using a script, by restarting the computer or any other way.

DONT KILL CONNECTIONS LIKE THIS :
Wrong way of killing the connections to database is by taking out the network wire, or by disabling network devices.

Fix Database Users Logins in SQL Server

2007-07-30T11:54:00.000-04:00

Every time a DBA restores the database, they need to fix logins in SQL Server. I refresh databases using a job which runs step by step. All i have to do is enable the job and create a schedule for the time i want refresh to take place.

Step 1.
Backup the production database.

Step 2.
Copy the database backup from production server to development or QA.

Step 3.
Kill all the connections in SQL Server. I use this Kill SQL Connection script.

Step 4.
Restore the database on development or QA SQL Server.

Step 5.
Fix Logins, remap them other wise users will not be able to login.

If its a development box then there are few more steps. I have to script out the new objects to make sure i am not going to overwrite any development work including sql server stored procedures and view etc.

Here is the script i use.

USE DATABASE_NAME
GO
DECLARE
@User_Name varchar(255),
@Adhoc_SQL nvarchar(2000)
DECLARE User_Cursor CURSOR FOR
select
sysusers.[name]
from
sysusers
JOIN
master..syslogins sl
ON
sysusers.[name] = sl.[name]
where
sysusers.issqluser = 1
and
(
sysusers.sid is not null
and
sysusers.sid <> 0x0
)
and
suser_sname(sysusers.sid) is null
AND
sysusers.[name] <> 'dbo'

OPEN User_Cursor
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Adhoc_SQL = 'EXEC sp_change_users_login '+
'@Action = ''Auto_Fix'', ' +
'@UserNamePattern = ''' + @User_Name + ''''
print @Adhoc_SQL
EXEC sp_ExecuteSQL @Adhoc_SQL
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
END
CLOSE User_Cursor
DEALLOCATE User_Cursor

Make sure you replace the database name in the script. You can also run the above SQL Script in Query Analyzer of SQL Server.

SQL Server 2008 Release

2007-07-28T15:50:00.000-04:00

Microsoft creates products faster then people are making babies in highly populated countries. Half of the SQL Server users have not upgraded to SQL Server 2005 and now microsoft is releasing new version SQL Server 2008. I guess releasing a new version is the best excuse of not fixing problems in current version. I have not read whats new in 2008 but i am sure there will be alot to impress my directors and purchase officers to spend money on it.

I dont know honestly, its my first blog. If you dont know which version of SQL Server you are using, type this command in Query Analyzer

SQL Command : SELECT @@VERSION