Youtube and SQL Server

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

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

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

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

-- 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

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

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

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

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

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

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