Database comparision

Language : VB.NET
Database : MSSQLserver2000
i have developed a tool to compare two databases, but it is taking long time to compare. First it will take the whole table into main memory(order by primary key). Same procedure for table2 of second database. Then it will start comparison by taking row by row. i tested with one third party tool, which is taking only 2 min to compare 800000 records of a table. My tool is taking 1 hour,40 mins to do that. Is there any other optimized method?

Regards,

Loka

Did you consider that instead of checking column by column, that you 'might' be able to compare a checksum() of the column, or even of the entire row?|||

Hi,

The checksum idea is quite good - it should help you speed up the process. Just a couple of more thoughts on the subject:

(a) Any implementation with a client application would require an access to the data in some form - even if you use a server-side cursor, you would have to fetch the data for comparison and it will travel (maybe even through network if you're not running the app local to SQL Server) to the application. Thus the best approach is to implement the comparison inside SQL itself - as stored procedures, queries, views, etc. Be careful though - the comparison may consume SQL resources and starve existing processes and clients. You should be able to find a ballance between the server impact and the comparison task performance.

(b) You could take advantage of some of the new T-SQL enhancements in SQL 2005 like "intersect" and "except".

(c) If in the end, a standalone application proves to be needed, use OLEDB or ODBC directly with SQL Native Client to achieve maximum performance. Plan carefully for the types of cursors you need (ideally a simple firehose should be the fastest), the potential for blocking or other isolation issues, the proximity and network utilization between client and server, etc.

HTH,

Jivko Dobrev - MSFT

--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Some other random ideas:

1. bcp out the tables and do a file compare of the resulting output files, if nothing is different this should be really fast.

2. Comparing rows in bcp out might actually be faster than scanning records using a recordset for sparse differences, hard to tell. For example, say you bcp out the tables into c:\foo1.txt and c:\foo2.txt. Then you can scan a single line at a time from each file and just memcmp the entire line in one command. If there is a missing pkey, they you have to scan each file to resync the keys but in general it should work pretty fast I think.

3. Computing a checksum on the server side should be faster than trying to download the data and checksum downloaded data I think.

Database compare

Language : VB.NET
Database : MSSQLserver2000
i have developed a tool to compare two databases, but it is taking long time to compare. First it will take the whole table into main memory(order by primary key). Same procedure for table2 of second database. Then it will start comparison by taking row by row. i tested with one third party tool, which is taking only 2 min to compare 800000 records of a table. My tool is taking 1 hour,40 mins to do that. Is there any other optimized method?

Regards,
Lokait is likely that you wrote inefficient code. without providing us the information in Brett's sticky at the top of this page, you are unlikely to get any replies you find helpful.|||Are you taking advantage of the CHECKSUM and BINARY_CHECKSUM functions for comparing records?|||BINARY_CHECKSUM rocks! We use it for this very same situation, but use a stored proc that does a full outer join (to grab missing rows from both tables also, in addition to catching data differences with the BINARY_CHECKSUM) and this morning's run took 4.26 minutes to pass/compare two tables on two different servers each with 12,224,072 rows.

yep, we bad, we bad...|||...as long as you don't need ABSOLUTE EXACT ACCURACY, binary_checksum is very efficient.|||I seem to recall some trouble with it, but your comment irritates the hell out of...err...*blush* I mean, piques my interest :)

How can you be more absolute, exactly accurate than a binary comparison? Guess I need to go look at BOL again ;0

I was tripped up a time or two because it actually seems to be a byte-by-byte comparison. As I recall, if you are comparing two columns named "price", and one is defined as a FLOAT, and one as a MONEY, and both contain 2.45, the BINARY COMPARE will fail.

Also, even if both your columns DATE contain '12/21/2006', but one is defined as a datetime, and the other a smalldatetime, the BINARY COMPARE will fail.

In both cases, if you think about it, it makes sense. from the BINARY perspective the bits involved are going to be different because the variable size is different. But I still got tripped up with them early on.|||Thanks for your quick response... We implemented a tool to compare the databases in VB.NET and we should not put any stored procedure in the any of databases. Is there any other way to compare long datatypes in VB.NET.
if there is 10 lakh records in each database then main emory is not at all sufficient to take the whole table at a time. Please suggest me a better approach to solve this issue..|||How can you be more absolute, exactly accurate than a binary comparison? Because it is possible for two different strings to yield the same binary checksum value. In some scenarios, it is almost likely to happen.

We implemented a tool to compare the databases in VB.NET and we should not put any stored procedure in the any of databases.Your system could create a temporary stored procedure in the database and then delete it upon completion.|||Binary Checksum does exactly what the name implies, it computes a binary checksum. You can compute a sum from a row that is almost 8 Kb long, and even longer if you use TEXT or IMAGE columns. The checksum is a whole bunch smaller than the actual data being summed, so there are some combinations of data that will produce the same checksum value.

Binary Checksum is good, but it is a long way from being perfect (no checksum can be perfect in that sense).

-PatP|||Is it possible to create temporary stored procedure in the remote server?
"Binary check sum" is database function or .Net function? what is the syntax?
If you have sample stored procedure to compare two databases, please give it to me.

Regards,
Loka|||First it will take the whole table into main memory
Very bad idea. What do you do if the two tables do not fit into memory?|||Yes that's a problem, how to overcome this?|||Is it possible to create temporary stored procedure in the remote server?
"Binary check sum" is database function or .Net function? what is the syntax?
If you have sample stored procedure to compare two databases, please give it to me.

Regards,
Loka
BINARY_CHECKSUM is a SQLSVR database function.
You can create a stored procedure on the database at the start of your process using a simple CREATE PROCEDURE statement, and then drop it when you are finished with DROP PROCEDURE. The login used will need to have sufficient permissions to create procedures.|||Yes that's a problem, how to overcome this?I would recommend a cursor.

...just funnin' ya guys.

That's the purpose of the stored procedure, which can just select and (outer) join the two tables to find any differences.

And yup, you guys that dis' the checksum, binary or otherwise...I suppose you gotta know thy data, and thy checksum algorithm, and see if the risk is worth the benefit. As Mr. Pat pointed out, as soon as you start using a checksum, you introduce the possibility/probability that a checksum comparison will not catch a difference in data. There is some thought out there that the more data you scrunch into your checksum fields/components, the less likely to have this problem show up, but I am not sure I buy that at face value.

As with most things in life, it depends.

...and that is as close to philosophical as I am willing to get on Christmas Eve Eve Eve.

Database Column encryption

Hi is anyone out there using any third party tools to do
column level encryption.
If so, what tool are you using. And what pitfalls have
you encountered while using them.
Thanks in advance.
JohnJohn,
We use wiCrypt from www.whamware.com. No pitfalls to report.
Tom
"John Oberkehr" wrote:

quote:

---

> Hi is anyone out there using any third party tools to do
> column level encryption.
> If so, what tool are you using. And what pitfalls have
> you encountered while using them.
> Thanks in advance.
> John

---

|||John, you do also have the option of column-level security and views (leave
out the column). I know that's not exactly what you asked though.
Eric
"John Oberkehr" <anonymous@.discussions.microsoft.com> wrote in message
news:093001c3b9ce$2c47cb50$a001280a@.phx.gbl...

quote:

---

> Hi is anyone out there using any third party tools to do
> column level encryption.
> If so, what tool are you using. And what pitfalls have
> you encountered while using them.
> Thanks in advance.
> John

---

|||We use XP_CRYPT www.activecrypt.comsql

Database backup using .LDF & .MDF files

Hi,

I am developing a tool that takes snapshot of a particular database.

By snapshot I mean, attaching the .LDF & .MDF files.

Would you please let me know how to access these files & take a backup of the same.

And also how to restore them back. (using c# programming)

Thanks,

Archana A.A.

MDF (Microsoft data file) and LDF (Log data file), belong to one folder, while Backup file which is .Bak it goes into a different subfolder. Run a search for SMO (sql server management object), it is the way to deal with SQL Server programmatically. The link below shows SMO based backup code. I am assuming you know if all three files get corrupted you will loose your database. Run a search for SMO in the BOL (books online). Hope this helps.

http://www.sqldbatips.com/showarticle.asp?ID=37

sql

Database analysis

Hi,
Is there a tool to give me a report on all tables contained within a
microsoft DB. Looking to see total rows in each table, total size of
table.
Many Thanks
GarethOn Sep 18, 1:52 pm, payneless <gpayne_one...@.hotmail.com> wrote:
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
you can try the below query
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'titles'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),rows int, reserved
varchar(100),data varchar(100),index_size varchar(100), unused
varchar(100))
INSERT INTO #t
EXEC sp_MSforeachtable @.command1="sp_spaceused '?'"
SELECT * FROM #t
DROP TABLE #t
Thanks
Vijayasekar|||If you are in a SQL Server 2005 Environment and SSMS is installed then go to
a database and right click on it. Choose Reports from the popup menu and
select "Disk Usage by Table" or try other choices for alternative results.
--
Ekrem Önsoy
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>|||If you are in SQL Server install SQL Server 2005 Performance Dashboard
Reports
http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>

Database Admin Tool Prerequisites

The prerequisites for the Database Admin Tool says:
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?
Hi
"Carel" wrote:

> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John
|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John
|||Hi Carel
"Carel" wrote:

> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQL
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John

Database Admin Tool Prerequisites

The prerequisites for the Database Admin Tool says:
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?Hi
"Carel" wrote:

> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
>
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John|||Hi Carel
"Carel" wrote:

> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQ
L
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John

Database Activity Monitoring. Any tool known tool for Ms SQL Server 2005 or 2000?

Hi all,

Does anyone knows if exists any tool (from microsoft or other vendor) that allows us to monitor all the activity that is generated in SQL server?

I mean Network appliances or servers that monitor database activity and log activity that is external to the database server, and can generate alerts based on unusual behavior or policy violations. Often rely on a local agent to monitor activity.

I am looking for a tool that not searchs on .log files, or creates triggers.

Any suggestion? any known tool? Pls, send me your answers to lc@.quantumsystem.com

Thanks

You can run Profiler externally, put the output into SQL Tables, set Triggers and Alerts to fire from the data.

And the traces can be conformed and customized in just about any way you desire.

|||

Ok, this is not a possible solution.

We are in a bank, so we need to secure our sql. We need no one to cheat the server or the log files (that is why i asked for a non standard sql procedure).

I want some tool from an external provider.

does anyone knows some tool? Remember i dont want tools that reads the log file, or that just add triggers to databases and tables.

We do not want a hardware based solution too...

I hope you can help me.

Regards,

|||

If you don't want to export to a log file... and you don't want to introduce triggers... what options are left?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Paul:

there are some tools (one at least) that runs some monitors (as software services) that allows you to monitor each SQL server, without the option for administrators or DBAs to stop them.

This monitors are connected to central repositories that store all the audited data.

You can audit everything: from select sentences until COPY databases commands, or whatever happens inside the database.

The thing is that i am trying to search , to see if there is another tool, instead the one that we have.

ThankS!

Data Transformation Services (DTS)(Bulk Insert)

Hi All,

I'm using DTS package, a tool to transfer data from a txt file to database(Bulk Insert).

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.It seems that the Bulk Insert task supports only OLE DB connections for the destination database. But I want to use sql server authentication as OLEDB connection requires windows authentication.

So can the bulk insert be done using SQLServer authentication ? if yes then please help me.

I have given the code snippet below.

Code Sample:

Dim oPackage As New DTS.Package2()
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask
Try
oConnection = oPackage.Connections.New("SQLOLEDB")
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSBulkInsertTask")
oCustomTask = oTask.CustomTask
With oConnection
oConnection.Catalog = "pubs"
oConnection.DataSource = "(local)"
oConnection.ID = 1
oConnection.UseTrustedConnection = True
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"
End With
oPackage.Connections.Add(oConnection)
oConnection = Nothing
With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = "c:\dts\authors.txt"
.ConnectionID = 1
.DestinationTableName = "pubs..authors"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
oStep.TaskName = oCustomTask.Name
With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With
oPackage.Execute()
Catch ex As Exception
MsgBox("Error: " & CStr(Err.Number) & vbCrLf_
& Err.Description, vbExclamation, oPackage.Name)
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try

This is really a SSIS forum not DTS. There is a newsgroup for DTS, with a web/forum style interface.

Corrected Snippet

oConnection.UseTrustedConnection = False ' Must be false to use SQL Security
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"