Anyone knows a software that compares 2 dbs and synchronize even if replication existNadim
I found that on internet,check it out
----
--
-- sp_CompareDB
--
-- The SP compares structures and data in 2 databases.
-- 1. Compares if all tables in one database have analog (by name) in second
database
-- Tables not existing in one of databases won't be used for data comparing
-- 2. Compares if structures for tables with the same names are the same.
Shows structural
-- differences like:
-- authors
-- Column Phone: in db1 - char(12), in DB2 - char(14)
-- sales
-- Column Location not in db2
-- Tables, having different structures, won't be used for data comparing.
However if the tables
-- contain columns of the same type and different length (like Phone in the
example above) or
-- tables have compatible data types (have the same type in syscolumns -
char and nchar,
-- varchar and nvarchar etc) they will be allowed for data comparing.
-- 3. Data comparison itself.
-- 3.1 Get information about unique keys in the tables. If there are unique
keys then one of them
-- (PK is a highest priority candidate for this role) will be used to
specify rows with
-- different data.
-- 3.2 Get information about all data columns in the table and form
predicates that will be
-- used to compare data.
-- 3.3 Compare data with the criteria:
-- a. if some unique keys from the table from first database do not exist in
second db (only
-- for tables with a unique key)
-- b. if some unique keys from the table from second database do not exist
in first db (only
-- for tables with a unique key)
-- c. if there are rows with the same values of unique keys and different
data in other
-- columns (only for tables with a unique key)
-- d. if there are rows in the table from first database that don't have a
twin in the
-- table from second db
-- e. if there are rows in the table from second database that don't have a
twin in the
-- table from first db
----
--
-- Parameters:
-- 1. @.db1 - name of first database to compare
-- 2. @.db2 - name of second database to compare
-- 3. @.TabList - list of tables to compare. if empty - all tables in the
databases should be
-- compared
-- 4. @.NumbToShow - number of rows with differences to show. Default - 10.
-- 5. @.OnlyStructure - flag, if set to 1, allows to avoid data comparing.
Only structures should
-- be compared. Default - 0
-- 6. @.NoTimestamp - flag, if set to 1, allows to avoid comparing of columns
of timestamp
-- data type. Default - 0
-- 7. @.VerboseLevel - if set to 1 allows to print querues used for data
comparison
----
--
-- Created by Viktor Gorodnichenko (c)
-- Created on: July 5, 2001
----
--
CREATE PROC sp_CompareDB
@.db1 varchar(128),
@.db2 varchar(128),
@.OnlyStructure bit = 0,
@.TabList varchar(8000) = '',
@.NumbToShow int = 10,
@.NoTimestamp bit = 0,
@.VerboseLevel tinyint = 0
AS
if @.OnlyStructure <> 0
set @.OnlyStructure = 1
if @.NoTimestamp <> 0
set @.NoTimestamp = 1
if @.VerboseLevel <> 0
set @.VerboseLevel = 1
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
declare @.sqlStr varchar(8000)
set nocount on
-- Checking if there are specified databases
declare @.SrvName sysname
declare @.DBName sysname
set @.db1 = RTRIM(LTRIM(@.db1))
set @.db2 = RTRIM(LTRIM(@.db2))
set @.SrvName = @.@.SERVERNAME
if CHARINDEX('.',@.db1) > 0
begin
set @.SrvName = LEFT(@.db1,CHARINDEX('.',@.db1)-1)
if not exists (select * from master.dbo.sysservers where srvname = @.SrvName)
begin
print 'There is no linked server named '+@.SrvName+'. End of work.'
return
end
set @.DBName = RIGHT(@.db1,LEN(@.db1)-CHARINDEX('.',@.db1))
end
else
set @.DBName = @.db1
exec ('declare @.Name sysname select @.Name=name from
['+@.SrvName+'].master.dbo.sysdatabases where name = '''+@.DBName+'''')if
@.@.rowcount = 0
begin
print 'There is no database named '+@.db1+'. End of work.'
return
end
set @.SrvName = @.@.SERVERNAME
if CHARINDEX('.',@.db2) > 0
begin
set @.SrvName = LEFT(@.db2,CHARINDEX('.',@.db2)-1)
if not exists (select * from master.dbo.sysservers where srvname = @.SrvName)
begin
print 'There is no linked server named '+@.SrvName+'. End of work.'
return
end
set @.DBName = RIGHT(@.db2,LEN(@.db2)-CHARINDEX('.',@.db2))
end
else
set @.DBName = @.db2
exec ('declare @.Name sysname select @.Name=name from
['+@.SrvName+'].master.dbo.sysdatabases where name = '''+@.DBName+'''')
if @.@.rowcount = 0
begin
print 'There is no database named '+@.db2+'. End of work.'
return
end
print Replicate('-',LEN(@.db1)+LEN(@.db2)+25)
print 'Comparing databases '+@.db1+' and '+@.db2
print Replicate('-',LEN(@.db1)+LEN(@.db2)+25)
print 'Options specified:'
print ' Compare only structures: '+CASE WHEN @.OnlyStructure = 0 THEN 'No'
ELSE 'Yes' END
print ' List of tables to compare: '+CASE WHEN LEN(@.TabList) = 0 THEN ' All
tables' ELSE @.TabList END
print ' Max number of different rows in each table to show:
'+LTRIM(STR(@.NumbToShow))
print ' Compare timestamp columns: '+CASE WHEN @.NoTimestamp = 0 THEN 'No'
ELSE 'Yes' END
print ' Verbose level: '+CASE WHEN @.VerboseLevel = 0 THEN 'Low' ELSE 'High'
END
----
--
-- Comparing structures
----
--
print CHAR(10)+Replicate('-',36)
print 'Comparing structure of the databases'
print Replicate('-',36)
if exists (select * from tempdb.dbo.sysobjects where name like
'#TabToCheck%')
drop table #TabToCheck
create table #TabToCheck (name sysname)
declare @.NextCommaPos int
if len(@.TabList) > 0
begin
while 1=1
begin
set @.NextCommaPos = CHARINDEX(',',@.TabList)
if @.NextCommaPos = 0
begin
set @.sqlstr = 'insert into #TabToCheck values('''+@.TabList+''')'
exec (@.sqlstr)
break
end
set @.sqlstr = 'insert into #TabToCheck
values('''+LEFT(@.TabList,@.NextCommaPos-1)+''')'
exec (@.sqlstr)
set @.TabList = RIGHT(@.TabList,LEN(@.TabList)-@.NextCommaPos)
end
end
else -- then will check all tables
begin
exec ('insert into #TabToCheck select name from '+@.db1+'.dbo.sysobjects
where type = ''U''')
exec ('insert into #TabToCheck select name from '+@.db2+'.dbo.sysobjects
where type = ''U''')
end
-- First check if at least one table specified in @.TabList exists in db1
exec ('declare @.Name sysname select @.Name=name from '+@.db1+'.dbo.sysobjects
where name in (select * from #TabToCheck)')
if @.@.rowcount = 0
begin
print 'No tables in '+@.db1+' to check. End of work.'
return
end
-- Check if tables existing in db1 are in DB2 (all tables or specified in
@.TabList)
if exists (select * from tempdb.dbo.sysobjects where name like
'#TabNotInDB2%')
drop table #TabNotInDB2
create table #TabNotInDB2 (name sysname)
insert into #TabNotInDB2
exec ('select name from '+@.db1+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@.db2+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @.@.rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@.db1+', but do not exist in
'+@.db2+':'
select * from #TabNotInDB2
end
delete from #TabToCheck where name in (select * from #TabNotInDB2)
drop table #TabNotInDB2
if exists (select * from tempdb.dbo.sysobjects where name like
'#TabNotInDB1%')
drop table #TabNotInDB1
create table #TabNotInDB1 (name sysname)
insert into #TabNotInDB1
exec ('select name from '+@.db2+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@.db1+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @.@.rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@.db2+', but do not exist in
'+@.db1+':'
select * from #TabNotInDB1
end
delete from #TabToCheck where name in (select * from #TabNotInDB1)
drop table #TabNotInDB1
-- Comparing structures of tables existing in both dbs
print CHAR(10)+'Checking if there are tables existing in both databases
having structural differences ...'+CHAR(10)
if exists (select * from tempdb.dbo.sysobjects where name like
'#DiffStructure%')
drop table #DiffStructure
create table #DiffStructure (name sysname)
set @.sqlStr='
declare @.TName1 sysname, @.TName2 sysname, @.CName1 sysname, @.CName2 sysname,
@.TypeName1 sysname, @.TypeName2 sysname,
@.CLen1 smallint, @.CLen2 smallint, @.Type1 sysname, @.Type2 sysname, @.PrevTName
sysname
declare @.DiffStructure bit
declare Diff cursor fast_forward for
select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,
d1c.length, d2c.length, d1c.type, d2c.type
from ('+@.db1+'.dbo.sysobjects d1o
JOIN '+@.db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type =
''U'' --only tables in both dbs
and d1o.name in (select * from #TabToCheck)
JOIN '+@.db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id
JOIN '+@.db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)
FULL JOIN ('+@.db2+'.dbo.sysobjects d2o
JOIN '+@.db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type =
''U'' --only tables in both dbs
and d2o.name in (select * from #TabToCheck)
JOIN '+@.db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id
JOIN '+@.db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)
ON d1o.name = d2o.name and d1c.name = d2c.name
WHERE (not exists
(select * from '+@.db2+'.dbo.sysobjects d2o2
JOIN '+@.db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id
JOIN '+@.db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype
where d2o2.type = ''U''
and d2o2.name = d1o.name
and d2c2.name = d1c.name
and d2t2.name = d1t.name
and d2c2.length = d1c.length)
OR not exists
(select * from '+@.db1+'.dbo.sysobjects d1o2
JOIN '+@.db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id
JOIN '+@.db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype
where d1o2.type = ''U''
and d1o2.name = d2o.name
and d1c2.name = d2c.name
and d1t2.name = d2t.name
and d1c2.length = d2c.length))
order by coalesce(d1o.name,d2o.name), d1c.name
open Diff
fetch next from Diff into @.TName1, @.TName2, @.CName1, @.CName2, @.TypeName1,
@.TypeName2,
@.CLen1, @.CLen2, @.Type1, @.Type2
set @.PrevTName = ''''
set @.DiffStructure = 0
while @.@.fetch_status = 0
begin
if Coalesce(@.TName1,@.TName2) <> @.PrevTName
begin
if @.PrevTName <> '''' and @.DiffStructure = 1
begin
insert into #DiffStructure values (@.PrevTName)
set @.DiffStructure = 0
end
set @.PrevTName = Coalesce(@.TName1,@.TName2)
print @.PrevTName
end
if @.CName2 is null
print '' Colimn ''+RTRIM(@.CName1)+'' not in '+@.db2+'''
else
if @.CName1 is null
print '' Colimn ''+RTRIM(@.CName2)+'' not in '+@.db1+'''
else
if @.TypeName1 <> @.TypeName2
print '' Colimn ''+RTRIM(@.CName1)+'': in '+@.db1+' - ''+RTRIM(@.TypeName1)+'',
in '+@.db2+' - ''+RTRIM(@.TypeName2)
else --the columns are not null(are in both dbs) and types are equal,then
length are diff
print '' Colimn ''+RTRIM(@.CName1)+'': in '+@.db1+' -
''+RTRIM(@.TypeName1)+''(''+
LTRIM(STR(CASE when @.TypeName1=''nChar'' or @.TypeName1 = ''nVarChar'' then
@.CLen1/2 else @.CLen1 end))+
''), in '+@.db2+' - ''+RTRIM(@.TypeName2)+''(''+
LTRIM(STR(CASE when @.TypeName1=''nChar'' or @.TypeName1 = ''nVarChar'' then
@.CLen2/2 else @.CLen2 end))+'')''
if @.Type1 = @.Type2
set @.DiffStructure=@.DiffStructure -- Do nothing. Cannot invert predicate
else
set @.DiffStructure = 1
fetch next from Diff into @.TName1, @.TName2, @.CName1, @.CName2, @.TypeName1,
@.TypeName2,
@.CLen1, @.CLen2, @.Type1, @.Type2
end
deallocate Diff
if @.DiffStructure = 1
insert into #DiffStructure values (@.PrevTName)
'
exec (@.sqlStr)
if (select count(*) from #DiffStructure) > 0
begin
print CHAR(10)+'The table(s) have the same name and different structure in
the databases:'
select distinct * from #DiffStructure
delete from #TabToCheck where name in (select * from #DiffStructure)
end
else
print CHAR(10)+'There are no tables with the same name and structural
differences in the databases'+CHAR(10)+CHAR(10)
if @.OnlyStructure = 1
begin
print 'The option ''Only compare structures'' was specified. End of work.'
return
end
exec ('declare @.Name sysname select @.Name=d1o.name
from '+@.db1+'.dbo.sysobjects d1o, '+@.db2+'.dbo.sysobjects d2o
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)')
if @.@.rowcount = 0
begin
print 'There are no tables with the same name and structure in the databases
to compare. End of work.'
return
end
----
--
-- Comparing data
----
--
-- ##CompareStr - will be used to pass comparing strings into dynamic script
-- to execute the string
if exists (select * from tempdb.dbo.sysobjects where name like
'##CompareStr%')
drop table ##CompareStr
create table ##CompareStr (Ind int, CompareStr varchar(8000))
if exists (select * from tempdb.dbo.sysobjects where name like
'#DiffTables%')
drop table #DiffTables
create table #DiffTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like
'#IdenticalTables%')
drop table #IdenticalTables
create table #IdenticalTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like
'#EmptyTables%')
drop table #EmptyTables
create table #EmptyTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like
'#NoPKTables%')
drop table #NoPKTables
create table #NoPKTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')
truncate table #IndList1
else
create table #IndList1 (IndId int, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')
truncate table #IndList2
else
create table #IndList2 (IndId smallint, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
print Replicate('-',51)
print 'Comparing data in tables with indentical structure:'
print Replicate('-',51)
----
--
-- Cursor for all tables in dbs (or for all specified tables if parameter
@.TabList is passed)
----
--
declare @.SqlStrGetListOfKeys1 varchar(8000)
declare @.SqlStrGetListOfKeys2 varchar(8000)
declare @.SqlStrGetListOfColumns varchar(8000)
declare @.SqlStrCompareUKeyTables varchar(8000)
declare @.SqlStrCompareNonUKeyTables varchar(8000)
set @.SqlStrGetListOfKeys1 = '
declare @.sqlStr varchar(8000)
declare @.ExecSqlStr varchar(8000)
declare @.PrintSqlStr varchar(8000)
declare @.Tab varchar(128)
declare @.d1User varchar(128)
declare @.d2User varchar(128)
declare @.KeyAndStr varchar(8000)
declare @.KeyCommaStr varchar(8000)
declare @.AndStr varchar(8000)
declare @.Eq varchar(8000)
declare @.IndId int
declare @.IndStatus int
declare @.CurrIndId smallint
declare @.CurrStatus int
declare @.UKey sysname
declare @.Col varchar(128)
declare @.LastUsedCol varchar(128)
declare @.xType int
declare @.Len int
declare @.SelectStr varchar(8000)
declare @.ExecSql nvarchar(1000)
declare @.NotInDB1 bit
declare @.NotInDB2 bit
declare @.NotEq bit
declare @.Numb int
declare @.Cnt1 int
declare @.Cnt2 int
set @.Numb = 0
declare @.StrInd int
declare @.i int
declare @.PrintStr varchar(8000)
declare @.ExecStr varchar(8000)
declare TabCur cursor for
select d1o.name, d1u.name, d2u.name from '+@.db1+'.dbo.sysobjects d1o,
'+@.db2+'.dbo.sysobjects d2o,
'+@.db1+'.dbo.sysusers d1u, '+@.db2+'.dbo.sysusers d2u
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.uid = d1u.uid and d2o.uid = d2u.uid
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)
order by 1
open TabCur
fetch next from TabCur into @.Tab, @.d1User, @.d2User
while @.@.fetch_status = 0
begin
set @.Numb = @.Numb + 1
print Char(13)+Char(10)+LTRIM(STR(@.Numb))+''. TABLE: [''+@.Tab+''] ''
set @.ExecSql = ''SELECT @.Cnt = count(*) FROM
'+@.db1+'.[''+@.d1User+''].[''+@.Tab+'']''
exec sp_executesql @.ExecSql, N''@.Cnt int output'', @.Cnt = @.Cnt1 output
print CHAR(10)+STR(@.Cnt1)+'' rows in '+@.db1+'''
set @.ExecSql = ''SELECT @.Cnt = count(*) FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+'']''
exec sp_executesql @.ExecSql, N''@.Cnt int output'', @.Cnt = @.Cnt2 output
print STR(@.Cnt2)+'' rows in '+@.db2+'''
if @.Cnt1 = 0 and @.Cnt2 = 0
begin
exec ('' insert into #EmptyTables values(''''[''+@.Tab+'']'''')'')
goto NextTab
end
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
set @.NotInDB1 = 0
set @.NotInDB2 = 0
set @.NotEq = 0
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
truncate table #IndList1
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@.db1+'.dbo.sysobjects o,
'+@.db1+'.dbo.sysindexes i, '+@.db1+'.dbo.sysindexkeys k,
'+@.db1+'.dbo.syscolumns c
where i.id = o.id and o.name = @.Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
while @.@.fetch_status = 0
begin
if @.KeyAndStr <> ''''
begin
set @.KeyAndStr = @.KeyAndStr + '' and '' + CHAR(10)
set @.KeyCommaStr = @.KeyCommaStr + '', ''
end
if @.xType = 175 or @.xType = 167 or @.xType = 239 or @.xType = 231 -- char,
varchar, nchar, nvarchar
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
if @.xType = 173 or @.xType = 165 -- binary, varbinary
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN
0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4
ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 56 or @.xType = 127 or @.xType = 60 or @.xType = 122 -- int,
127 - bigint,60 - money, 122 - smallmoney
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 106 or @.xType = 108 -- int, decimal, numeric
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 62 or @.xType = 59 -- 62 - float, 59 - real
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 52 or @.xType = 48 or @.xType = 104 -- smallint, tinyint, bit
begin
set @.KeyAndStr = @.KeyAndStr + '' CASE WHEN d1.[''+@.UKey+''] is null THEN
99999 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 99999 ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 36 -- 36 - id
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@.UKey+''] END''
end
else if @.xType = 61 or @.xType = 58 -- datetime, smalldatetime
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d1.[''+@.UKey+''],109) END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d2.[''+@.UKey+''],109) END ''
end
else if @.xType = 189 -- timestamp (189)
begin
set @.KeyAndStr = @.KeyAndStr + '' d1.[''+@.UKey+'']=d2.[''+@.UKey+''] ''
end
else if @.xType = 98 -- SQL_variant
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
set @.KeyCommaStr = @.KeyCommaStr + '' d1.''+@.UKey
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
if @.IndId <> @.CurrIndId
begin
insert into #IndList1 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList1 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)'
set @.SqlStrGetListOfKeys2 = '
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
truncate table #IndList2
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@.db2+'.dbo.sysobjects o,
'+@.db2+'.dbo.sysindexes i, '+@.db2+'.dbo.sysindexkeys k,
'+@.db2+'.dbo.syscolumns c
where i.id = o.id and o.name = @.Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
while @.@.fetch_status = 0
begin
if @.KeyAndStr <> ''''
begin
set @.KeyAndStr = @.KeyAndStr + '' and '' + CHAR(10)
set @.KeyCommaStr = @.KeyCommaStr + '', ''
end
if @.xType = 175 or @.xType = 167 or @.xType = 239 or @.xType = 231 -- char,
varchar, nchar, nvarchar
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
if @.xType = 173 or @.xType = 165 -- binary, varbinary
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN
0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4
ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 56 or @.xType = 127 or @.xType = 60 or @.xType = 122 -- int,
127 - bigint,60 - money, 122 - smallmoney
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 106 or @.xType = 108 -- int, decimal, numeric
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 62 or @.xType = 59 -- 62 - float, 59 - real
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 52 or @.xType = 48 or @.xType = 104 -- smallint, tinyint, bit
begin
set @.KeyAndStr = @.KeyAndStr + '' CASE WHEN d1.[''+@.UKey+''] is null THEN
99999 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 99999 ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 36 -- 36 - id
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@.UKey+''] END''
end
else if @.xType = 61 or @.xType = 58 -- datetime, smalldatetime
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d1.[''+@.UKey+''],109) END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d2.[''+@.UKey+''],109) END ''
end
else if @.xType = 189 -- timestamp (189)
begin
set @.KeyAndStr = @.KeyAndStr + '' d1.[''+@.UKey+'']=d2.[''+@.UKey+''] ''
end
else if @.xType = 98 -- SQL_variant
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
set @.KeyCommaStr = @.KeyCommaStr + '' d1.''+@.UKey
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
if @.IndId <> @.CurrIndId
begin
insert into #IndList2 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList2 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)
set @.KeyCommaStr = null
select @.KeyCommaStr=i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr
where (i1.IndStatus & 2048)<> 0 and (i2.IndStatus & 2048)<>0
if @.KeyCommaStr is null
set @.KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)
set @.KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr =
@.KeyCommaStr)
if @.KeyCommaStr is null
set @.KeyCommaStr = ''''
if @.KeyAndStr is null
set @.KeyAndStr = ''
set @.SqlStrGetListOfColumns = '
set @.AndStr = ''''
set @.StrInd = 1
declare Cols cursor local fast_forward for
select c.name, c.xtype, c.length from '+@.db1+'.dbo.sysobjects o,
'+@.db1+'.dbo.syscolumns c
where o.id = c.id and o.name = @.Tab
and CHARINDEX(c.name, @.KeyCommaStr) = 0
open Cols
fetch next from Cols into @.Col, @.xType, @.len
while @.@.fetch_status = 0
begin
if @.xType = 175 or @.xType = 167 or @.xType = 239 or @.xType = 231 -- char,
varchar, nchar, nvarchar
begin
set @.Eq =
''ISNULL(d1.[''+@.Col+''],''''!#null$'''')=ISNULL(d2.[''+@.Col+''],''''!#null$
'''') ''
end
if @.xType = 173 or @.xType = 165 -- binary, varbinary
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN
0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4
ELSE d2.[''+@.Col+''] END ''
end
else if @.xType = 56 or @.xType = 127 or @.xType = 60 or @.xType = 122 -- int,
127 - bigint,60 - money, 122 - smallmoney
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN 971428763405345098745
ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 971428763405345098745 ELSE
d2.[''+@.Col+''] END ''
end
else if @.xType = 106 or @.xType = 108 -- int, decimal, numeric
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN
71428763405345098745098.8723 ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 71428763405345098745098.8723 ELSE
d2.[''+@.Col+''] END ''
end
else if @.xType = 62 or @.xType = 59 -- 62 - float, 59 - real
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN 8764589764.22708E237 ELSE
d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 8764589764.22708E237 ELSE
d2.[''+@.Col+''] END ''
end
else if @.xType = 52 or @.xType = 48 or @.xType = 104 -- smallint, tinyint, bit
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN 99999 ELSE
d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 99999 ELSE d2.[''+@.Col+''] END ''
end
else if @.xType = 36 -- 36 - id
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@.Col+''] END''
end
else if @.xType = 61 or @.xType = 58 -- datetime, smalldatetime
begin
set @.Eq =
''CASE WHEN d1.[''+@.Col+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d1.[''+@.Col+''],109) END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d2.[''+@.Col+''],109) END ''
end
else if @.xType = 34
begin
set @.Eq =
''ISNULL(DATALENGTH(d1.[''+@.Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@.Col+'']),0
) ''
end
else if @.xType = 35 or @.xType = 99 -- text (35),ntext (99)
begin
set @.Eq = ''ISNULL(SUBSTRING(d1.[''+@.Col+''],1,DATALENGTH(d1.[''+@.Col+
''])),''''!#null$'''')=ISNULL(SUBSTRING(
d2.[''+@.Col+''],1,DATALENGTH(d2.[''+
@.Col+''])),''''!#null$'''') ''
end
else if @.xType = 189
begin
if '+STR(@.NoTimestamp)+' = 0
set @.Eq = ''d1.[''+@.Col+'']=d2.[''+@.Col+''] ''
else
set @.Eq = ''1=1''
end
else if @.xType = 98 -- SQL_variant
begin
set @.Eq =
''ISNULL(d1.[''+@.Col+''],''''!#null$'''')=ISNULL(d2.[''+@.Col+''],''''!#null$
'''') ''
end
if @.AndStr = ''''
set @.AndStr = @.AndStr + CHAR(10) + '' '' + @.Eq
else
if len(@.AndStr) + len('' and '' + @.Eq)<8000
set @.AndStr = @.AndStr + '' and '' + CHAR(10) + '' '' + @.Eq
else
begin
set @.StrInd = @.StrInd + 1
Insert into ##CompareStr values(@.StrInd,@.AndStr)
set @.AndStr = '' and '' + @.Eq
end
fetch next from Cols into @.Col, @.xType, @.len
end
deallocate Cols '
set @.SqlStrCompareUKeyTables = '
if @.KeyAndStr <> ''''
begin
set @.SelectStr = ''SELECT ''+ @.KeyCommaStr+'' INTO ##NotInDb2 FROM
'+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@.KeyAndStr+'')''
if '+STR(@.VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@.db1+', but are not in
db2:''+CHAR(10)+
REPLACE (@.SelectStr, ''into ##NotInDB2'','''')
exec (@.SelectStr)
if @.@.rowcount > 0
set @.NotInDB2 = 1
set @.SelectStr = ''SELECT ''+@.KeyCommaStr+'' INTO ##NotInDB1 FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM
'+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@.KeyAndStr+'')''
if '+STR(@.VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@.db2+', but are not in
'+@.db1+':''+CHAR(10)+
REPLACE (@.SelectStr, ''into ##NotInDB1'','''')
exec (@.SelectStr)
if @.@.rowcount > 0
set @.NotInDB1 = 1
-- if there are non-key columns
if @.AndStr <> ''''
begin
set @.PrintStr = '' Print ''
set @.ExecStr = '' exec (''
set @.SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+ @.KeyCommaStr+'' INTO ##NotEq FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d1 ''+
'' INNER JOIN '+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d2 ON
''+CHAR(10)+@.KeyAndStr+CHAR(10)+''WHERE not('')
-- Adding last string in temp table containing a comparing string to execute
set @.StrInd = @.StrInd + 1
Insert into ##CompareStr values(@.StrInd,@.AndStr+'')'')
set @.i = 1
while @.i <= @.StrInd
begin
set @.SqlStr = @.SqlStr + '' declare @.Str''+LTRIM(STR(@.i))+'' varchar(8000)
''+
''select @.Str''+LTRIM(STR(@.i))+''=CompareStr FROM ##CompareStr WHERE ind =
''+STR(@.i)
if @.ExecStr <> '' exec (''
set @.ExecStr = @.ExecStr + ''+''
if @.PrintStr <> '' Print ''
set @.PrintStr = @.PrintStr + ''+''
set @.ExecStr = @.ExecStr + ''@.Str''+LTRIM(STR(@.i))
set @.PrintStr = @.PrintStr + '' REPLACE(@.Str''+LTRIM(STR(@.i))+'','''' into
##NotEq'''',''') ''
set @.i = @.i + 1
end
set @.ExecStr = @.ExecStr + '') ''
set @.ExecSqlStr = @.SqlStr + @.ExecStr
set @.PrintSqlStr = @.SqlStr +
'' Print CHAR(10)+''''To find rows that are different in non-key
columns:'''' ''+
@.PrintStr
if '+STR(@.VerboseLevel)+' = 1
exec (@.PrintSqlStr)
exec (@.ExecSqlStr)
if @.@.rowcount > 0
set @.NotEq = 1
end
else
if '+STR(@.VerboseLevel)+' = 1
print CHAR(10)+''There are no non-key columns in the table''
truncate table ##CompareStr
if @.NotInDB1 = 1 or @.NotInDB2 = 1 or @.NotEq = 1
begin
print CHAR(10)+''Data are different''
if @.NotInDB2 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''These key values exist in '+@.db1+', but do not exist in '+@.db2+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB2''
exec (@.SelectStr)
end
if @.NotInDB1 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''These key values exist in '+@.db2+', but do not exist in '+@.db1+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB1''
exec (@.SelectStr)
end
if @.NotEq = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''Row(s) with these key values contain differences in non-key columns:
''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotEq''
exec (@.SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@.Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@.Tab+'']'''')'')
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')
drop table ##NotEq
end
else '
set @.SqlStrCompareNonUKeyTables = '
begin
exec (''insert into #NoPKTables values(''''[''+@.Tab+'']'''')'')
set @.PrintStr = '' Print ''
set @.ExecStr = '' exec (''
set @.SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB2 FROM '+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d1 WHERE not
exists ''+CHAR(10)+
'' (SELECT * FROM '+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d2 WHERE '')
set @.StrInd = @.StrInd + 1
Insert into ##CompareStr values(@.StrInd,@.AndStr+'')'')
set @.i = 1
while @.i <= @.StrInd
begin
set @.SqlStr = @.SqlStr + '' declare @.Str''+LTRIM(STR(@.i))+'' varchar(8000)
''+
''select @.Str''+LTRIM(STR(@.i))+''=CompareStr FROM ##CompareStr WHERE ind =
''+STR(@.i)
if @.ExecStr <> '' exec (''
set @.ExecStr = @.ExecStr + ''+''
if @.PrintStr <> '' Print ''
set @.PrintStr = @.PrintStr + ''+''
set @.ExecStr = @.ExecStr + ''@.Str''+LTRIM(STR(@.i))
set @.PrintStr = @.PrintStr + '' REPLACE(@.Str''+LTRIM(STR(@.i))+'','''' into
##NotInDB2'''',''') ''
set @.i = @.i + 1
end
set @.ExecStr = @.ExecStr + '') ''
set @.ExecSqlStr = @.SqlStr + @.ExecStr
set @.PrintSqlStr = @.SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@.db1+', but are not in
'+@.db2+':'''' ''+
@.PrintStr
if '+STR(@.VerboseLevel)+' = 1
exec (@.PrintSqlStr)
exec (@.ExecSqlStr)
if @.@.rowcount > 0
set @.NotInDB2 = 1
delete from ##CompareStr where ind = 1
set @.PrintStr = '' Print ''
set @.ExecStr = '' exec (''
set @.SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB1 FROM '+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d1 WHERE not
exists ''+CHAR(10)+
'' (SELECT * FROM '+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d2 WHERE '')
set @.i = 1
while @.i <= @.StrInd
begin
set @.SqlStr = @.SqlStr + '' declare @.Str''+LTRIM(STR(@.i))+'' varchar(8000)
''+
''select @.Str''+LTRIM(STR(@.i))+''=CompareStr FROM ##CompareStr WHERE ind =
''+STR(@.i)
if @.ExecStr <> '' exec (''
set @.ExecStr = @.ExecStr + ''+''
if @.PrintStr <> '' Print ''
set @.PrintStr = @.PrintStr + ''+''
set @.ExecStr = @.ExecStr + ''@.Str''+LTRIM(STR(@.i))
set @.PrintStr = @.PrintStr + '' REPLACE(@.Str''+LTRIM(STR(@.i))+'','''' into
##NotInDB1'''',''') ''
set @.i = @.i + 1
end
set @.ExecStr = @.ExecStr + '') ''
set @.ExecSqlStr = @.SqlStr + @.ExecStr
set @.PrintSqlStr = @.SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@.db2+', but are not in
'+@.db1+':'''' ''+
@.PrintStr
if '+STR(@.VerboseLevel)+' = 1
exec (@.PrintSqlStr)
exec (@.ExecSqlStr)
if @.@.rowcount > 0
set @.NotInDB1 = 1
truncate table ##CompareStr
if @.NotInDB1 = 1 or @.NotInDB2 = 1
begin
print CHAR(10)+''Data are different''
if @.NotInDB2 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@.db1+', but do not exist in '+@.db2+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB2''
exec (@.SelectStr)
end
if @.NotInDB1 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@.db2+', but do not exist in '+@.db1+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB1''
exec (@.SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@.Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@.Tab+'']'''')'')
end
end
if exists (select * from tempdb.dbo.sysobjects where name like
''##NotInDB1%'')
drop table ##NotInDB1
if exists (select * from tempdb.dbo.sysobjects where name like
''##NotInDB2%'')
drop table ##NotInDB2
NextTab:
fetch next from TabCur into @.Tab, @.d1User, @.d2User
end
deallocate TabCur
'
exec (@.SqlStrGetListOfKeys1+@.SqlStrGetListOfK
eys2+@.SqlStrGetListOfColumns+
@.SqlStrCompareUKeyTables+@.SqlStrCompareN
onUKeyTables)
print ' '
SET NOCOUNT OFF
if (select count(*) from #NoPKTables) > 0
begin
select name as 'Table(s) without Unique key:' from #NoPKTables
end
if (select count(*) from #DiffTables) > 0
begin
select name as 'Table(s) with the same name & structure, but different
data:' from #DiffTables
end
else
print CHAR(10)+'No tables with the same name & structure, but different
data'+CHAR(10)
if (select count(*) from #IdenticalTables) > 0
begin
select name as 'Table(s) with the same name & structure and identical data:'
from #IdenticalTables
end
if (select count(*) from #EmptyTables) > 0
begin
select name as 'Table(s) with the same name & structure and empty in the
both databases:' from #EmptyTables
end
drop table #TabToCheck
drop table ##CompareStr
drop table #DiffTables
drop table #IdenticalTables
drop table #EmptyTables
drop table #NoPKTables
drop table #IndList1
drop table #IndList2
return
GO
"Nadim Wakim" <nadimlb@.cyberia.net.lb> wrote in message
news:O$j55AkGFHA.2936@.TK2MSFTNGP15.phx.gbl...
>
2012年3月27日星期二
2012年3月25日星期日
Anyone got sqlexec v.2.01 ?
We got our own key. but i lost this software...
My company dont' want to upgrade it.
Any people can give a help ?
The simplest way would be to contact "SnoopSoft" the company that produces
SqlExec http://www.snoopsoft.com/sqlexec/index.html
Cristian Lefter, SQL Server MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:utLx4mGVFHA.3544@.TK2MSFTNGP10.phx.gbl...
> We got our own key. but i lost this software...
> My company dont' want to upgrade it.
> Any people can give a help ?
>
|||You might mean SQLExecMS from laplas-soft, if this is the case then an
upgrade within any major version is free for that product, just ask for a
new key.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:utLx4mGVFHA.3544@.TK2MSFTNGP10.phx.gbl...
> We got our own key. but i lost this software...
> My company dont' want to upgrade it.
> Any people can give a help ?
>
My company dont' want to upgrade it.
Any people can give a help ?
The simplest way would be to contact "SnoopSoft" the company that produces
SqlExec http://www.snoopsoft.com/sqlexec/index.html
Cristian Lefter, SQL Server MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:utLx4mGVFHA.3544@.TK2MSFTNGP10.phx.gbl...
> We got our own key. but i lost this software...
> My company dont' want to upgrade it.
> Any people can give a help ?
>
|||You might mean SQLExecMS from laplas-soft, if this is the case then an
upgrade within any major version is free for that product, just ask for a
new key.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:utLx4mGVFHA.3544@.TK2MSFTNGP10.phx.gbl...
> We got our own key. but i lost this software...
> My company dont' want to upgrade it.
> Any people can give a help ?
>
2012年2月18日星期六
Any Fuzzy Software ( Other than SOUNDEX / NYSIIS )?
Hi All
We are using soundex (and later tried Nysiis) for fuzzy name search
software. But we faced a lot of problems the search accuracy was not very
good also we saw a lot of misses of relevant names.
There are many problems other than precision and accuracy, with soundex and
NYSIIS.
e.g.
Look for Smith and it will come-up with around 250 very popular last names.
That dones not help much when a user is searching for "John Smith". Also, it
does not return Creighton for Kryton as the search string.
I googled a little and saw soming called NamiX. Without contacting the
company ( arizcon.com ) directly, I wanted to get feedback from newsgroups
or people who are experts at this. Has anyone used this software? If so, Is
it as good as they claim?
Thanks a million in advance.
Steve Creighton
(please remove .antispam from email address) or post back your answers to
this groupSteve,
This is not really the group for this kind of question but Name match
software or simply text matching software can be reasonably cheap or very
expensive. The best I've used (~$100K price tag for prod software only) was
Ascentials Quality Stage Server. Just so I related this to SQL Server after
all what group is this posted in?
A company I consulted for had a need to match names address pairs to members
in thier SQL DB. They already owned and used Quality Server for other
purposes. They eventually decided to write an extensive TSQL stored
procedure that checked for about 20 different combinations of things like
soundex of last name plus state plus first three letters of street address.
The client was statisfied with the match rate and they could understand it
well. The moral of the story here is if absolute highest accurate match
rate is essential then use a package writen by someone with a PHD in
statistics. If over 99% is all your looking for, write it in something you
can support and maintain.
Ray
"Evaluating Fuzzy SW" <evalsofty@.hotmail.com> wrote in message
news:Ze6Ed.8827$c13.2366@.bgtnsc04-news.ops.worldnet.att.net...
> Hi All
>
> We are using soundex (and later tried Nysiis) for fuzzy name search
> software. But we faced a lot of problems the search accuracy was not very
> good also we saw a lot of misses of relevant names.
> There are many problems other than precision and accuracy, with soundex
> and
> NYSIIS.
> e.g.
> Look for Smith and it will come-up with around 250 very popular last
> names.
> That dones not help much when a user is searching for "John Smith". Also,
> it
> does not return Creighton for Kryton as the search string.
> I googled a little and saw soming called NamiX. Without contacting the
> company ( arizcon.com ) directly, I wanted to get feedback from newsgroups
> or people who are experts at this. Has anyone used this software? If so,
> Is
> it as good as they claim?
> Thanks a million in advance.
> Steve Creighton
> (please remove .antispam from email address) or post back your answers to
> this group
2012年2月13日星期一
antivirus software question
I currently exclude the directories that contain the sql database files
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.
Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>
|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>
|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.
Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>
|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>
|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>
antivirus software question
I currently exclude the directories that contain the sql database files
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>>I currently exclude the directories that contain the sql database files
>>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>>scans. Actually I do this for any database files (act, quickbooks, ms
>>access) but I was recently thinking about this and I realized that I'm not
>>really sure if its necessary?
>> sticking with SQL server 2000/2005 since that is what this forum is for,
>> is it true that I should be excluding these directories (the ones with
>> the database and log files) from my antivirus software's protection?
>> and just in case someone is also familiar with the needs of any of the
>> other databases I mentioned, please feel free to comment on them as well.
>> All those other systems are flat file based database systems. I don't
>> know if that makes a difference with respect to antivirus software.
>|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>> Each AntiVirus software scans computers differently. To generally say
>> that one wont affect your SQL server when anther might would be
>> misleading.
>> You should test scanning folders with your particular software to see it
>> affects your SQL server. A decision should be made by the business owner
>> as to whether scanning that directory is mandated or not.
>> There are tons of known issues using Antivirus in a clutered envirnoment
>> so special attention should be givin in this situation.
>> here are two great articles...
>> "Guidelines for choosing antivirus software to run on the computers that
>> are running SQL Server"
>> http://support.microsoft.com/?kbid=309422
>> "Antivirus Software May Cause Problems with Cluster Services"
>> http://support.microsoft.com/kb/250355/
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "djc" <noone@.nowhere.com> wrote in message
>> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>>I currently exclude the directories that contain the sql database files
>>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>>scans. Actually I do this for any database files (act, quickbooks, ms
>>access) but I was recently thinking about this and I realized that I'm
>>not really sure if its necessary?
>> sticking with SQL server 2000/2005 since that is what this forum is for,
>> is it true that I should be excluding these directories (the ones with
>> the database and log files) from my antivirus software's protection?
>> and just in case someone is also familiar with the needs of any of the
>> other databases I mentioned, please feel free to comment on them as
>> well. All those other systems are flat file based database systems. I
>> don't know if that makes a difference with respect to antivirus
>> software.
>>
>
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>>I currently exclude the directories that contain the sql database files
>>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>>scans. Actually I do this for any database files (act, quickbooks, ms
>>access) but I was recently thinking about this and I realized that I'm not
>>really sure if its necessary?
>> sticking with SQL server 2000/2005 since that is what this forum is for,
>> is it true that I should be excluding these directories (the ones with
>> the database and log files) from my antivirus software's protection?
>> and just in case someone is also familiar with the needs of any of the
>> other databases I mentioned, please feel free to comment on them as well.
>> All those other systems are flat file based database systems. I don't
>> know if that makes a difference with respect to antivirus software.
>|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>> Each AntiVirus software scans computers differently. To generally say
>> that one wont affect your SQL server when anther might would be
>> misleading.
>> You should test scanning folders with your particular software to see it
>> affects your SQL server. A decision should be made by the business owner
>> as to whether scanning that directory is mandated or not.
>> There are tons of known issues using Antivirus in a clutered envirnoment
>> so special attention should be givin in this situation.
>> here are two great articles...
>> "Guidelines for choosing antivirus software to run on the computers that
>> are running SQL Server"
>> http://support.microsoft.com/?kbid=309422
>> "Antivirus Software May Cause Problems with Cluster Services"
>> http://support.microsoft.com/kb/250355/
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "djc" <noone@.nowhere.com> wrote in message
>> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>>I currently exclude the directories that contain the sql database files
>>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>>scans. Actually I do this for any database files (act, quickbooks, ms
>>access) but I was recently thinking about this and I realized that I'm
>>not really sure if its necessary?
>> sticking with SQL server 2000/2005 since that is what this forum is for,
>> is it true that I should be excluding these directories (the ones with
>> the database and log files) from my antivirus software's protection?
>> and just in case someone is also familiar with the needs of any of the
>> other databases I mentioned, please feel free to comment on them as
>> well. All those other systems are flat file based database systems. I
>> don't know if that makes a difference with respect to antivirus
>> software.
>>
>
antivirus software question
I currently exclude the directories that contain the sql database files
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>
(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
scans. Actually I do this for any database files (act, quickbooks, ms
access) but I was recently thinking about this and I realized that I'm not
really sure if its necessary?
sticking with SQL server 2000/2005 since that is what this forum is for, is
it true that I should be excluding these directories (the ones with the
database and log files) from my antivirus software's protection?
and just in case someone is also familiar with the needs of any of the other
databases I mentioned, please feel free to comment on them as well. All
those other systems are flat file based database systems. I don't know if
that makes a difference with respect to antivirus software.Each AntiVirus software scans computers differently. To generally say that
one wont affect your SQL server when anther might would be misleading.
You should test scanning folders with your particular software to see it
affects your SQL server. A decision should be made by the business owner as
to whether scanning that directory is mandated or not.
There are tons of known issues using Antivirus in a clutered envirnoment so
special attention should be givin in this situation.
here are two great articles...
"Guidelines for choosing antivirus software to run on the computers that are
running SQL Server"
http://support.microsoft.com/?kbid=309422
"Antivirus Software May Cause Problems with Cluster Services"
http://support.microsoft.com/kb/250355/
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I currently exclude the directories that contain the sql database files
>(mdf, ldf) from my antivirus 'real time protection' and scheduled sweep
>scans. Actually I do this for any database files (act, quickbooks, ms
>access) but I was recently thinking about this and I realized that I'm not
>really sure if its necessary?
> sticking with SQL server 2000/2005 since that is what this forum is for,
> is it true that I should be excluding these directories (the ones with the
> database and log files) from my antivirus software's protection?
> and just in case someone is also familiar with the needs of any of the
> other databases I mentioned, please feel free to comment on them as well.
> All those other systems are flat file based database systems. I don't know
> if that makes a difference with respect to antivirus software.
>|||thanks for the reply Warren. Are you refering to stability/corruption
effects or only performance effects? My main concern is if there may be
stability/corruption issues due to the antivirus, not performance. I could
gage the performance easily enough after making a change but its the
possible corruption and/or stability issues that my not be immediately
apparrent (and possibly realized to late!) that worry me. Does this change
anything? or is this still a per product issue that requires individual
testing? ...I'm always looking for that nice, simple, yet broad, rule thats
easy to remember : )
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
> Each AntiVirus software scans computers differently. To generally say
> that one wont affect your SQL server when anther might would be
> misleading.
> You should test scanning folders with your particular software to see it
> affects your SQL server. A decision should be made by the business owner
> as to whether scanning that directory is mandated or not.
> There are tons of known issues using Antivirus in a clutered envirnoment
> so special attention should be givin in this situation.
> here are two great articles...
> "Guidelines for choosing antivirus software to run on the computers that
> are running SQL Server"
> http://support.microsoft.com/?kbid=309422
> "Antivirus Software May Cause Problems with Cluster Services"
> http://support.microsoft.com/kb/250355/
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "djc" <noone@.nowhere.com> wrote in message
> news:ezYIrdB3GHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||I am referring to both stability, performance and security. Installing
AntiVirus on a SQL server increases surface area which has security risks.
But what I am really trying to say is you need to test it with your
particular anti virus software. For me, I dont want anything actively
scanning my SQL server files for a thousand reasons. Antivirus software can
certainly create a bottleneck due to the program requiring too much CPU
time -- especially if the server is also running an e-mail server or program
that requires a lot of antivirus resources (not necessarily a best
practice). It can also create file I/O problems if real-time scanning is
enabled for all files that are opened. I definitely recommend running
malware protection on a SQL Server (or any database server for that matter),
but be sure to exclude your database directories (or specific database
files) from the real-time protection if you can. Also, keep an eye out on
processor utilization to make sure the malware protection is not eating up
all the resources.
I have seen countless forums on how AV software locks up your DB files and
put the database in Suspect Mode or causing SQL server to fail.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"djc" <noone@.nowhere.com> wrote in message
news:%23QtRDrB3GHA.4484@.TK2MSFTNGP02.phx.gbl...
> thanks for the reply Warren. Are you refering to stability/corruption
> effects or only performance effects? My main concern is if there may be
> stability/corruption issues due to the antivirus, not performance. I could
> gage the performance easily enough after making a change but its the
> possible corruption and/or stability issues that my not be immediately
> apparrent (and possibly realized to late!) that worry me. Does this change
> anything? or is this still a per product issue that requires individual
> testing? ...I'm always looking for that nice, simple, yet broad, rule
> thats easy to remember : )
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:%23EaUchB3GHA.4312@.TK2MSFTNGP02.phx.gbl...
>
AntiVirus Software on SQL Server?
As a DBA, I was taught that running Anti-virus software on a database server
was not a very good thing to do. My network admin is planning a Windows 2000
migration and would like all machines across the domain to run AV software.
Can anyone tell me about their experience running AV software on their
database server, and whether this is a good idea or not.
The box runs SQL 6.5sp5a (soon to be 2k) and does not touch the outside
world at all.
Thanks for your help,
CurtisAs much as you may want to keep anti-virus software away
from a SQL box, you'll soon realize that that's a lost
battle. When there is a virus crisis, whatever arguments
you may have to avoid anti-virus on your SQL box, you'll
end being defeated badly.
My experience is to simply give up on avoiding virus scan
completely, and ask for the SQL files being excluded from
the virus scan. I typically ask the security folks to
exclude the following files from being scanned: *.MDF,
*.LDF, *.NDF, *.BAK, *.TRN, and *.BKP.
The primary reason you don't want anti-virus scanning is
that the virus scan software may get hold of a SQL
data/log file and prevents SQL Server from opening it,
resulting in the database being put into the suspect mode.
Usually, these data/log files are open all the time, and
therefore not an issue. But sometimes we may need to shut
down SQL Server instance or detach a database. Before we
restart SQL Server or attach the database, if the virus
software gets ahead of us, we are screwed.
There are also some issues with SQL Server running in a
cluster.
Check out: Q309422 and Q250355
Linchi
>--Original Message--
>As a DBA, I was taught that running Anti-virus software
on a database server
>was not a very good thing to do. My network admin is
planning a Windows 2000
>migration and would like all machines across the domain
to run AV software.
>Can anyone tell me about their experience running AV
software on their
>database server, and whether this is a good idea or not.
>The box runs SQL 6.5sp5a (soon to be 2k) and does not
touch the outside
>world at all.
>Thanks for your help,
>Curtis
>
>.
>
was not a very good thing to do. My network admin is planning a Windows 2000
migration and would like all machines across the domain to run AV software.
Can anyone tell me about their experience running AV software on their
database server, and whether this is a good idea or not.
The box runs SQL 6.5sp5a (soon to be 2k) and does not touch the outside
world at all.
Thanks for your help,
CurtisAs much as you may want to keep anti-virus software away
from a SQL box, you'll soon realize that that's a lost
battle. When there is a virus crisis, whatever arguments
you may have to avoid anti-virus on your SQL box, you'll
end being defeated badly.
My experience is to simply give up on avoiding virus scan
completely, and ask for the SQL files being excluded from
the virus scan. I typically ask the security folks to
exclude the following files from being scanned: *.MDF,
*.LDF, *.NDF, *.BAK, *.TRN, and *.BKP.
The primary reason you don't want anti-virus scanning is
that the virus scan software may get hold of a SQL
data/log file and prevents SQL Server from opening it,
resulting in the database being put into the suspect mode.
Usually, these data/log files are open all the time, and
therefore not an issue. But sometimes we may need to shut
down SQL Server instance or detach a database. Before we
restart SQL Server or attach the database, if the virus
software gets ahead of us, we are screwed.
There are also some issues with SQL Server running in a
cluster.
Check out: Q309422 and Q250355
Linchi
>--Original Message--
>As a DBA, I was taught that running Anti-virus software
on a database server
>was not a very good thing to do. My network admin is
planning a Windows 2000
>migration and would like all machines across the domain
to run AV software.
>Can anyone tell me about their experience running AV
software on their
>database server, and whether this is a good idea or not.
>The box runs SQL 6.5sp5a (soon to be 2k) and does not
touch the outside
>world at all.
>Thanks for your help,
>Curtis
>
>.
>
Antivirus software on SQL Server 2000
I am preparing to install SQL Server 2000 on a Windows 2003 server. There will be no file or print sharing services or in general no other processes running on this server except those required by SQL Server. The SQL Server is behind a filewall and will only be accessed internally. Should I install McAfee or Norton antivirus software on this machine? If so, are there any special configuration options that I should consider?Check out this KB article.
INF: Consideration for a Virus Scanner on a Computer That Is Running SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;309422&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Lane Lesperance" <LLesperance@.cooperpower.com> wrote in message
news:ekCZHsCwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I am preparing to install SQL Server 2000 on a Windows 2003 server. There
will be no file or print sharing services or in general no other processes
running on this server except those required by SQL Server. The SQL Server
is behind a filewall and will only be accessed internally. Should I install
McAfee or Norton antivirus software on this machine? If so, are there any
special configuration options that I should consider?
>|||See http://support.microsoft.com/default.aspx?scid=kb;en-
us;309422
In general, it is not recommended to do so if you don't
have to on a SQL box due to the things you mention (i.e.
not a file server, etc.). If you do, filter the SQL
DB/log files (not the executables), and if it's a cluster,
filter the \MSCS and if it exists, \MSDtc folders on the
quorum.
>--Original Message--
>I am preparing to install SQL Server 2000 on a Windows
2003 server. There will be no file or print sharing
services or in general no other processes running on this
server except those required by SQL Server. The SQL Server
is behind a filewall and will only be accessed internally.
Should I install McAfee or Norton antivirus software on
this machine? If so, are there any special configuration
options that I should consider?
>
>.
>
INF: Consideration for a Virus Scanner on a Computer That Is Running SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;309422&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Lane Lesperance" <LLesperance@.cooperpower.com> wrote in message
news:ekCZHsCwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I am preparing to install SQL Server 2000 on a Windows 2003 server. There
will be no file or print sharing services or in general no other processes
running on this server except those required by SQL Server. The SQL Server
is behind a filewall and will only be accessed internally. Should I install
McAfee or Norton antivirus software on this machine? If so, are there any
special configuration options that I should consider?
>|||See http://support.microsoft.com/default.aspx?scid=kb;en-
us;309422
In general, it is not recommended to do so if you don't
have to on a SQL box due to the things you mention (i.e.
not a file server, etc.). If you do, filter the SQL
DB/log files (not the executables), and if it's a cluster,
filter the \MSCS and if it exists, \MSDtc folders on the
quorum.
>--Original Message--
>I am preparing to install SQL Server 2000 on a Windows
2003 server. There will be no file or print sharing
services or in general no other processes running on this
server except those required by SQL Server. The SQL Server
is behind a filewall and will only be accessed internally.
Should I install McAfee or Norton antivirus software on
this machine? If so, are there any special configuration
options that I should consider?
>
>.
>
AntiVirus Software for SQL Cluster Servers
Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
software I should use. Any information will be greatly appreciated.
I like TrendMicro, whatever you get - exclude the MSCS and data directories,
if not the entire shared disks at a minimum.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||I have never used TrendMicro. Should I order a specific version? will it be
aware of the cluster?
Thanks for your help!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> I like TrendMicro, whatever you get - exclude the MSCS and data directories,
> if not the entire shared disks at a minimum.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
>
|||The latest OfficeScan is cluster aware. Don't let the silly name fool you,
it work for both workstations and servers.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...[vbcol=seagreen]
>I have never used TrendMicro. Should I order a specific version? will it be
> aware of the cluster?
> Thanks for your help!
> Yuhong
> "Rodney R. Fournier [MVP]" wrote:
|||Great. Thanks so much for your information!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> The latest OfficeScan is cluster aware. Don't let the silly name fool you,
> it work for both workstations and servers.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...
>
>
|||We use McAfee VirusScan on our clusters. It seems to be working fine. As
Rodney mentioned, do exclude the SQL Server files from scan.
Linchi
"Yuhong" wrote:
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||Thanks, Linchi! I will look into these products.
"Linchi Shea" wrote:
[vbcol=seagreen]
> We use McAfee VirusScan on our clusters. It seems to be working fine. As
> Rodney mentioned, do exclude the SQL Server files from scan.
> Linchi
> "Yuhong" wrote:
|||In addition, exclude any backup file locations from virus scan, either on
the local box or on a network share. You don't want to see what happens to
a SQL server if an AV program detects a virus in the backup data stream. It
isn't pretty.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||OK. Thanks!
Do I need to exclude the MSDTC cluster resource drive from the scan also?
"Geoff N. Hiten" wrote:
> In addition, exclude any backup file locations from virus scan, either on
> the local box or on a network share. You don't want to see what happens to
> a SQL server if an AV program detects a virus in the backup data stream. It
> isn't pretty.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
|||I would!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:2CA26428-B8A6-47FB-8DEC-F031CB34F449@.microsoft.com...[vbcol=seagreen]
> OK. Thanks!
> Do I need to exclude the MSDTC cluster resource drive from the scan also?
>
> "Geoff N. Hiten" wrote:
software I should use. Any information will be greatly appreciated.
I like TrendMicro, whatever you get - exclude the MSCS and data directories,
if not the entire shared disks at a minimum.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||I have never used TrendMicro. Should I order a specific version? will it be
aware of the cluster?
Thanks for your help!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> I like TrendMicro, whatever you get - exclude the MSCS and data directories,
> if not the entire shared disks at a minimum.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
>
|||The latest OfficeScan is cluster aware. Don't let the silly name fool you,
it work for both workstations and servers.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...[vbcol=seagreen]
>I have never used TrendMicro. Should I order a specific version? will it be
> aware of the cluster?
> Thanks for your help!
> Yuhong
> "Rodney R. Fournier [MVP]" wrote:
|||Great. Thanks so much for your information!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> The latest OfficeScan is cluster aware. Don't let the silly name fool you,
> it work for both workstations and servers.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...
>
>
|||We use McAfee VirusScan on our clusters. It seems to be working fine. As
Rodney mentioned, do exclude the SQL Server files from scan.
Linchi
"Yuhong" wrote:
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||Thanks, Linchi! I will look into these products.
"Linchi Shea" wrote:
[vbcol=seagreen]
> We use McAfee VirusScan on our clusters. It seems to be working fine. As
> Rodney mentioned, do exclude the SQL Server files from scan.
> Linchi
> "Yuhong" wrote:
|||In addition, exclude any backup file locations from virus scan, either on
the local box or on a network share. You don't want to see what happens to
a SQL server if an AV program detects a virus in the backup data stream. It
isn't pretty.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||OK. Thanks!
Do I need to exclude the MSDTC cluster resource drive from the scan also?
"Geoff N. Hiten" wrote:
> In addition, exclude any backup file locations from virus scan, either on
> the local box or on a network share. You don't want to see what happens to
> a SQL server if an AV program detects a virus in the backup data stream. It
> isn't pretty.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
|||I would!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:2CA26428-B8A6-47FB-8DEC-F031CB34F449@.microsoft.com...[vbcol=seagreen]
> OK. Thanks!
> Do I need to exclude the MSDTC cluster resource drive from the scan also?
>
> "Geoff N. Hiten" wrote:
anti-virus software
I'd like to hear if most people using anti-virus software
on the SQL production server? Also, what are the
performance implications of doing this?
TIA,
JB
JB,
I would recommend that you do not scan the SQL Server data or log file
directories, otherwise it should be OK. Depends on the anti-virus
product that you are using. I haven't had any problems with Sophos -
generally the anti-virus products these days are quite good, there used
to be stability and performance problems with them a few years ago. If
you had asked this question a few years ago I would have recommended you
don't install one, but I think it's OK now.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
JB wrote:
> I'd like to hear if most people using anti-virus software
> on the SQL production server? Also, what are the
> performance implications of doing this?
> TIA,
> JB
|||I agree with Mark, don't virus check your sql data and log files. At best it
can lead to performance problems...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OveS0WAlEHA.2500@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> JB,
> I would recommend that you do not scan the SQL Server data or log file
> directories, otherwise it should be OK. Depends on the anti-virus
> product that you are using. I haven't had any problems with Sophos -
> generally the anti-virus products these days are quite good, there used
> to be stability and performance problems with them a few years ago. If
> you had asked this question a few years ago I would have recommended you
> don't install one, but I think it's OK now.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> JB wrote:
on the SQL production server? Also, what are the
performance implications of doing this?
TIA,
JB
JB,
I would recommend that you do not scan the SQL Server data or log file
directories, otherwise it should be OK. Depends on the anti-virus
product that you are using. I haven't had any problems with Sophos -
generally the anti-virus products these days are quite good, there used
to be stability and performance problems with them a few years ago. If
you had asked this question a few years ago I would have recommended you
don't install one, but I think it's OK now.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
JB wrote:
> I'd like to hear if most people using anti-virus software
> on the SQL production server? Also, what are the
> performance implications of doing this?
> TIA,
> JB
|||I agree with Mark, don't virus check your sql data and log files. At best it
can lead to performance problems...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OveS0WAlEHA.2500@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> JB,
> I would recommend that you do not scan the SQL Server data or log file
> directories, otherwise it should be OK. Depends on the anti-virus
> product that you are using. I haven't had any problems with Sophos -
> generally the anti-virus products these days are quite good, there used
> to be stability and performance problems with them a few years ago. If
> you had asked this question a few years ago I would have recommended you
> don't install one, but I think it's OK now.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> JB wrote:
anti-virus software
I'd like to hear if most people using anti-virus software
on the SQL production server? Also, what are the
performance implications of doing this?
TIA,
JBJB,
I would recommend that you do not scan the SQL Server data or log file
directories, otherwise it should be OK. Depends on the anti-virus
product that you are using. I haven't had any problems with Sophos -
generally the anti-virus products these days are quite good, there used
to be stability and performance problems with them a few years ago. If
you had asked this question a few years ago I would have recommended you
don't install one, but I think it's OK now.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
JB wrote:
> I'd like to hear if most people using anti-virus software
> on the SQL production server? Also, what are the
> performance implications of doing this?
> TIA,
> JB|||I agree with Mark, don't virus check your sql data and log files. At best it
can lead to performance problems...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OveS0WAlEHA.2500@.TK2MSFTNGP09.phx.gbl...
> JB,
> I would recommend that you do not scan the SQL Server data or log file
> directories, otherwise it should be OK. Depends on the anti-virus
> product that you are using. I haven't had any problems with Sophos -
> generally the anti-virus products these days are quite good, there used
> to be stability and performance problems with them a few years ago. If
> you had asked this question a few years ago I would have recommended you
> don't install one, but I think it's OK now.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> JB wrote:
> > I'd like to hear if most people using anti-virus software
> > on the SQL production server? Also, what are the
> > performance implications of doing this?
> > TIA,
> > JB
on the SQL production server? Also, what are the
performance implications of doing this?
TIA,
JBJB,
I would recommend that you do not scan the SQL Server data or log file
directories, otherwise it should be OK. Depends on the anti-virus
product that you are using. I haven't had any problems with Sophos -
generally the anti-virus products these days are quite good, there used
to be stability and performance problems with them a few years ago. If
you had asked this question a few years ago I would have recommended you
don't install one, but I think it's OK now.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
JB wrote:
> I'd like to hear if most people using anti-virus software
> on the SQL production server? Also, what are the
> performance implications of doing this?
> TIA,
> JB|||I agree with Mark, don't virus check your sql data and log files. At best it
can lead to performance problems...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OveS0WAlEHA.2500@.TK2MSFTNGP09.phx.gbl...
> JB,
> I would recommend that you do not scan the SQL Server data or log file
> directories, otherwise it should be OK. Depends on the anti-virus
> product that you are using. I haven't had any problems with Sophos -
> generally the anti-virus products these days are quite good, there used
> to be stability and performance problems with them a few years ago. If
> you had asked this question a few years ago I would have recommended you
> don't install one, but I think it's OK now.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> JB wrote:
> > I'd like to hear if most people using anti-virus software
> > on the SQL production server? Also, what are the
> > performance implications of doing this?
> > TIA,
> > JB
AntiVirus Software
We have McAfee VirusScan installed on our database server. I would like to
configure the system not to scan *.bak, *.mdf, *.ldf. My question:
1. Is this the right configuration ?
2. By doing so, any potential security breach ?
3. By doing so, will the SQL box performance improve a bit?
Furthermore, we will have a new clustered SQL insatlled later on in our data
center, any recommendation on antiVirus configuration on SAN ?
Much appreciated.Hi
By not allowing the file extensions to be scanned will mean any file in any
directory will be able to have this extension, you may want to look at
excluding by directory, which if would leave less possibilities for a rogue
file if your permissions are tight enough, a combination of both would be
even tighter!!.
You may want to run MBSA to see if it recomends anything to be improved.
http://support.microsoft.com/defaul...kb;en-us;309422 also
recommends .ndf files but database files can have any extension name so make
sure that any database file extension is included in an exclude list. The
article also gives recommendations for SAN discs.
Make sure that your database do not have the autoclose property set.
If you have full text searching then you should look at not scanning
"C:\Program Files\Microsoft SQL Server\MSSQL\FTData"
A-V software can cause problems such as
http://support.microsoft.com/defaul...kb;en-us;170338
The performance effect of the a-v software will depend to some extent on the
hardware you are running, make sure that when it is running it is not too
resource hungry and your disc have not become a bottleneck.
John
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com...
> We have McAfee VirusScan installed on our database server. I would like to
> configure the system not to scan *.bak, *.mdf, *.ldf. My question:
> 1. Is this the right configuration ?
> 2. By doing so, any potential security breach ?
> 3. By doing so, will the SQL box performance improve a bit?
> Furthermore, we will have a new clustered SQL insatlled later on in our
> data
> center, any recommendation on antiVirus configuration on SAN ?
> Much appreciated.|||Most anti-virus products will allow you to bypass the scanning of specific
directories.You can selectively disallow the scanning of the data file and
transaction log directories.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com...
> We have McAfee VirusScan installed on our database server. I would like to
> configure the system not to scan *.bak, *.mdf, *.ldf. My question:
> 1. Is this the right configuration ?
> 2. By doing so, any potential security breach ?
> 3. By doing so, will the SQL box performance improve a bit?
> Furthermore, we will have a new clustered SQL insatlled later on in our
> data
> center, any recommendation on antiVirus configuration on SAN ?
> Much appreciated.|||I don't know what type of virus McAfee would be looking for in a mdf or ldf
file. If it scans the entire GB sized files from top to bottom while
referncing every possible virus signature pattern, it would result in a lot
of wasted I/O, CPU cycles, and possibly locking issues.
First make sure you have installed the version specifically designed to run
on a server. Also, it may only be prudent to have it scan only those file
types typically infected by viruses.
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com...
> We have McAfee VirusScan installed on our database server. I would like to
> configure the system not to scan *.bak, *.mdf, *.ldf. My question:
> 1. Is this the right configuration ?
> 2. By doing so, any potential security breach ?
> 3. By doing so, will the SQL box performance improve a bit?
> Furthermore, we will have a new clustered SQL insatlled later on in our
> data
> center, any recommendation on antiVirus configuration on SAN ?
> Much appreciated.|||On Fri, 30 Dec 2005 01:57:02 -0800, Lan <Lan@.discussions.microsoft.com> wrot
e:
in <AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com>
>We have McAfee VirusScan installed on our database server. I would like to
>configure the system not to scan *.bak, *.mdf, *.ldf. My question:
>1. Is this the right configuration ?
>2. By doing so, any potential security breach ?
>3. By doing so, will the SQL box performance improve a bit?
>Furthermore, we will have a new clustered SQL insatlled later on in our dat
a
>center, any recommendation on antiVirus configuration on SAN ?
>Much appreciated.
I've found McAfee to be about the worst choice for anti virus, especially on
a
server. It's bloated, heavy handed, and almost amateurish in its ability to
discriminate between what constitutes a threat and what doesn't.
For ease of use, small footprint, and utter effectiveness have a look at AVG
.
Although I'm disheartened by the fact that they've been acquired by microsof
t,
it's been almost a year now and I haven't seen any overtly negative changes
yet.
Stefan Berglund
configure the system not to scan *.bak, *.mdf, *.ldf. My question:
1. Is this the right configuration ?
2. By doing so, any potential security breach ?
3. By doing so, will the SQL box performance improve a bit?
Furthermore, we will have a new clustered SQL insatlled later on in our data
center, any recommendation on antiVirus configuration on SAN ?
Much appreciated.Hi
By not allowing the file extensions to be scanned will mean any file in any
directory will be able to have this extension, you may want to look at
excluding by directory, which if would leave less possibilities for a rogue
file if your permissions are tight enough, a combination of both would be
even tighter!!.
You may want to run MBSA to see if it recomends anything to be improved.
http://support.microsoft.com/defaul...kb;en-us;309422 also
recommends .ndf files but database files can have any extension name so make
sure that any database file extension is included in an exclude list. The
article also gives recommendations for SAN discs.
Make sure that your database do not have the autoclose property set.
If you have full text searching then you should look at not scanning
"C:\Program Files\Microsoft SQL Server\MSSQL\FTData"
A-V software can cause problems such as
http://support.microsoft.com/defaul...kb;en-us;170338
The performance effect of the a-v software will depend to some extent on the
hardware you are running, make sure that when it is running it is not too
resource hungry and your disc have not become a bottleneck.
John
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com...
> We have McAfee VirusScan installed on our database server. I would like to
> configure the system not to scan *.bak, *.mdf, *.ldf. My question:
> 1. Is this the right configuration ?
> 2. By doing so, any potential security breach ?
> 3. By doing so, will the SQL box performance improve a bit?
> Furthermore, we will have a new clustered SQL insatlled later on in our
> data
> center, any recommendation on antiVirus configuration on SAN ?
> Much appreciated.|||Most anti-virus products will allow you to bypass the scanning of specific
directories.You can selectively disallow the scanning of the data file and
transaction log directories.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com...
> We have McAfee VirusScan installed on our database server. I would like to
> configure the system not to scan *.bak, *.mdf, *.ldf. My question:
> 1. Is this the right configuration ?
> 2. By doing so, any potential security breach ?
> 3. By doing so, will the SQL box performance improve a bit?
> Furthermore, we will have a new clustered SQL insatlled later on in our
> data
> center, any recommendation on antiVirus configuration on SAN ?
> Much appreciated.|||I don't know what type of virus McAfee would be looking for in a mdf or ldf
file. If it scans the entire GB sized files from top to bottom while
referncing every possible virus signature pattern, it would result in a lot
of wasted I/O, CPU cycles, and possibly locking issues.
First make sure you have installed the version specifically designed to run
on a server. Also, it may only be prudent to have it scan only those file
types typically infected by viruses.
"Lan" <Lan@.discussions.microsoft.com> wrote in message
news:AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com...
> We have McAfee VirusScan installed on our database server. I would like to
> configure the system not to scan *.bak, *.mdf, *.ldf. My question:
> 1. Is this the right configuration ?
> 2. By doing so, any potential security breach ?
> 3. By doing so, will the SQL box performance improve a bit?
> Furthermore, we will have a new clustered SQL insatlled later on in our
> data
> center, any recommendation on antiVirus configuration on SAN ?
> Much appreciated.|||On Fri, 30 Dec 2005 01:57:02 -0800, Lan <Lan@.discussions.microsoft.com> wrot
e:
in <AAF5085D-DF64-4E0E-A4F2-7C34D0AC468F@.microsoft.com>
>We have McAfee VirusScan installed on our database server. I would like to
>configure the system not to scan *.bak, *.mdf, *.ldf. My question:
>1. Is this the right configuration ?
>2. By doing so, any potential security breach ?
>3. By doing so, will the SQL box performance improve a bit?
>Furthermore, we will have a new clustered SQL insatlled later on in our dat
a
>center, any recommendation on antiVirus configuration on SAN ?
>Much appreciated.
I've found McAfee to be about the worst choice for anti virus, especially on
a
server. It's bloated, heavy handed, and almost amateurish in its ability to
discriminate between what constitutes a threat and what doesn't.
For ease of use, small footprint, and utter effectiveness have a look at AVG
.
Although I'm disheartened by the fact that they've been acquired by microsof
t,
it's been almost a year now and I haven't seen any overtly negative changes
yet.
Stefan Berglund
订阅：
博文 (Atom)