Database benchmark

We are in the process of moving our databases from one server to another and
want to know what are some best to capture some performance benchmarks of
existing systems and compare it with the migrated database on the new server
so that at the end of the day performace is at best similar or better on the
new hardware.
Thanks
Hi Hassan
The obvious ones are to use perfmon and SQL Profiler over a variety of
periods/tests and produce comparative graphs/figures. You may also want to
replay profiles as a backend test.
If you are using a tool such as load runner or Visual Studio Team Test they
will collect their own stats that could be use to give a front end view.
As you are moving to a new system you should get a baseline using SQLIOSim
to make sure there are no issues with the new environment and have values
that you can use to compare against for future tests.
John
"Hassan" wrote:

> We are in the process of moving our databases from one server to another and
> want to know what are some best to capture some performance benchmarks of
> existing systems and compare it with the migrated database on the new server
> so that at the end of the day performace is at best similar or better on the
> new hardware.
> Thanks
>
>

Database benchmark

We are in the process of moving our databases from one server to another and
want to know what are some best to capture some performance benchmarks of
existing systems and compare it with the migrated database on the new server
so that at the end of the day performace is at best similar or better on the
new hardware.
ThanksHi Hassan :)
The obvious ones are to use perfmon and SQL Profiler over a variety of
periods/tests and produce comparative graphs/figures. You may also want to
replay profiles as a backend test.
If you are using a tool such as load runner or Visual Studio Team Test they
will collect their own stats that could be use to give a front end view.
As you are moving to a new system you should get a baseline using SQLIOSim
to make sure there are no issues with the new environment and have values
that you can use to compare against for future tests.
John
"Hassan" wrote:
> We are in the process of moving our databases from one server to another and
> want to know what are some best to capture some performance benchmarks of
> existing systems and compare it with the migrated database on the new server
> so that at the end of the day performace is at best similar or better on the
> new hardware.
> Thanks
>
>sql

Database benchmark

We are in the process of moving our databases from one server to another and
want to know what are some best to capture some performance benchmarks of
existing systems and compare it with the migrated database on the new server
so that at the end of the day performace is at best similar or better on the
new hardware.
ThanksHi Hassan
The obvious ones are to use perfmon and SQL Profiler over a variety of
periods/tests and produce comparative graphs/figures. You may also want to
replay profiles as a backend test.
If you are using a tool such as load runner or Visual Studio Team Test they
will collect their own stats that could be use to give a front end view.
As you are moving to a new system you should get a baseline using SQLIOSim
to make sure there are no issues with the new environment and have values
that you can use to compare against for future tests.
John
"Hassan" wrote:

> We are in the process of moving our databases from one server to another a
nd
> want to know what are some best to capture some performance benchmarks of
> existing systems and compare it with the migrated database on the new serv
er
> so that at the end of the day performace is at best similar or better on t
he
> new hardware.
> Thanks
>
>

Database backup process

My database is on a hired shared server, where the use of BACKUP DATABASE is
prohibited.
Therefore I need to copy the database to my own server using DTS - does DTS
have a different name in SQL Server 2005? - and then run BACKUP DATABASE to
make a .bak file that I can backup elsewhere to provide belt and braces.
What is the cheapest version of SQL Server 2005 that has DTS? Express,
which is free, appears not to have it; does the Developer version have it?
Or to I have to buy the Standard Edition at great expense?
Thanking you in anticipation for your advice.
Roger
If you can not use BACKUP then try the Copy Database Wizard instead.
Right-click your database, select Tasks, copy Database ...
Hope this helps,
Ben Nevarez
"Roger Withnell" wrote:

> My database is on a hired shared server, where the use of BACKUP DATABASE is
> prohibited.
> Therefore I need to copy the database to my own server using DTS - does DTS
> have a different name in SQL Server 2005? - and then run BACKUP DATABASE to
> make a .bak file that I can backup elsewhere to provide belt and braces.
> What is the cheapest version of SQL Server 2005 that has DTS? Express,
> which is free, appears not to have it; does the Developer version have it?
> Or to I have to buy the Standard Edition at great expense?
> Thanking you in anticipation for your advice.
> Roger
>

Database backup process

My database is on a hired shared server, where the use of BACKUP DATABASE is
prohibited.
Therefore I need to copy the database to my own server using DTS - does DTS
have a different name in SQL Server 2005? - and then run BACKUP DATABASE to
make a .bak file that I can backup elsewhere to provide belt and braces.
What is the cheapest version of SQL Server 2005 that has DTS? Express,
which is free, appears not to have it; does the Developer version have it?
Or to I have to buy the Standard Edition at great expense?
Thanking you in anticipation for your advice.
RogerIf you can not use BACKUP then try the Copy Database Wizard instead.
Right-click your database, select Tasks, copy Database ...
Hope this helps,
Ben Nevarez
"Roger Withnell" wrote:
> My database is on a hired shared server, where the use of BACKUP DATABASE is
> prohibited.
> Therefore I need to copy the database to my own server using DTS - does DTS
> have a different name in SQL Server 2005? - and then run BACKUP DATABASE to
> make a .bak file that I can backup elsewhere to provide belt and braces.
> What is the cheapest version of SQL Server 2005 that has DTS? Express,
> which is free, appears not to have it; does the Developer version have it?
> Or to I have to buy the Standard Edition at great expense?
> Thanking you in anticipation for your advice.
> Roger
>|||> Therefore I need to copy the database to my own server using DTS - does DTS
> have a different name in SQL Server 2005?
Yes, SQL Server Integration Services, aka Integration Services, aka IS, aka SSIS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
news:A3F285D1-60C2-4BE6-AC62-6FFBBDA7806B@.microsoft.com...
> My database is on a hired shared server, where the use of BACKUP DATABASE is
> prohibited.
> Therefore I need to copy the database to my own server using DTS - does DTS
> have a different name in SQL Server 2005? - and then run BACKUP DATABASE to
> make a .bak file that I can backup elsewhere to provide belt and braces.
> What is the cheapest version of SQL Server 2005 that has DTS? Express,
> which is free, appears not to have it; does the Developer version have it?
> Or to I have to buy the Standard Edition at great expense?
> Thanking you in anticipation for your advice.
> Roger
>

database (Restoring...)

I'm trying to backup a database and restore it on an another server.
this backup process need to run every 2 hours.
all what I need from the backup batch is to create the backup file, which I
will copy to the other server and restore it from there.
my question is: after the backup run, and since "NORECOVERY" is specified,
the database display (Restoring...) , unless I run "RESTORE DATABASE test".
Does this restore statement affect the database in anyway other than make it
accessible again and restore it to its original status.
ALTER DATABASE test SET RECOVERY FULL
BACKUP DATABASE test TO test_device
BACKUP LOG test TO test_device
BACKUP LOG test TO test_device WITH NORECOVERY;
restore database test with RECOVERY;
thanks.This behavior is perfectly normal and expected. The RECOVERY step completes
the final log restore by rolling back incomplete transactions and rolling
forward all completed transactions. Of course, you cannot restore more log
files after this step, but the database is now ready to use.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"zino" <zino@.noemail.noemail> wrote in message
news:EB0F065E-9A2A-49EF-8147-E754354F924C@.microsoft.com...
> I'm trying to backup a database and restore it on an another server.
> this backup process need to run every 2 hours.
> all what I need from the backup batch is to create the backup file, which
> I
> will copy to the other server and restore it from there.
> my question is: after the backup run, and since "NORECOVERY" is
> specified,
> the database display (Restoring...) , unless I run "RESTORE DATABASE
> test".
> Does this restore statement affect the database in anyway other than make
> it
> accessible again and restore it to its original status.
>
> ALTER DATABASE test SET RECOVERY FULL
> BACKUP DATABASE test TO test_device
> BACKUP LOG test TO test_device
> BACKUP LOG test TO test_device WITH NORECOVERY;
> restore database test with RECOVERY;
>
> thanks.

database (Restoring...)

I'm trying to backup a database and restore it on an another server.
this backup process need to run every 2 hours.
all what I need from the backup batch is to create the backup file, which I
will copy to the other server and restore it from there.
my question is: after the backup run, and since "NORECOVERY" is specified,
the database display (Restoring...) , unless I run "RESTORE DATABASE test".
Does this restore statement affect the database in anyway other than make it
accessible again and restore it to its original status.
ALTER DATABASE test SET RECOVERY FULL
BACKUP DATABASE test TO test_device
BACKUP LOG test TO test_device
BACKUP LOG test TO test_device WITH NORECOVERY;
restore database test with RECOVERY;
thanks.This behavior is perfectly normal and expected. The RECOVERY step completes
the final log restore by rolling back incomplete transactions and rolling
forward all completed transactions. Of course, you cannot restore more log
files after this step, but the database is now ready to use.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"zino" <zino@.noemail.noemail> wrote in message
news:EB0F065E-9A2A-49EF-8147-E754354F924C@.microsoft.com...
> I'm trying to backup a database and restore it on an another server.
> this backup process need to run every 2 hours.
> all what I need from the backup batch is to create the backup file, which
> I
> will copy to the other server and restore it from there.
> my question is: after the backup run, and since "NORECOVERY" is
> specified,
> the database display (Restoring...) , unless I run "RESTORE DATABASE
> test".
> Does this restore statement affect the database in anyway other than make
> it
> accessible again and restore it to its original status.
>
> ALTER DATABASE test SET RECOVERY FULL
> BACKUP DATABASE test TO test_device
> BACKUP LOG test TO test_device
> BACKUP LOG test TO test_device WITH NORECOVERY;
> restore database test with RECOVERY;
>
> thanks.

Data Validation Challenge

We have data migration process that transfers around 450 million records from
one DB to Another DB. There is a validation that takes place for the migrated
data that is time consuming (few days). The following validations takes place
on migrated data
NULL check
Length Check
Numeric Precision Check
So its looping through 450(rows) million * 30 (columns) times. so it takes
forever to complete the validation process and moreover the space
requirements also growing exponentially :-(. I would like to know is there a
better approach for validation of this kind. we are planning to try partition
approach. If there is any better way please help with your recommendations.
Regards,
Murali
Which tool are you using?
"Murali" wrote:

> We have data migration process that transfers around 450 million records from
> one DB to Another DB. There is a validation that takes place for the migrated
> data that is time consuming (few days). The following validations takes place
> on migrated data
> NULL check
> Length Check
> Numeric Precision Check
> So its looping through 450(rows) million * 30 (columns) times. so it takes
> forever to complete the validation process and moreover the space
> requirements also growing exponentially :-(. I would like to know is there a
> better approach for validation of this kind. we are planning to try partition
> approach. If there is any better way please help with your recommendations.
> Regards,
> Murali
>

Data Validation Challenge

We have data migration process that transfers around 450 million records fro
m
one DB to Another DB. There is a validation that takes place for the migrate
d
data that is time consuming (few days). The following validations takes plac
e
on migrated data
NULL check
Length Check
Numeric Precision Check
So its looping through 450(rows) million * 30 (columns) times. so it takes
forever to complete the validation process and moreover the space
requirements also growing exponentially :-(. I would like to know is there a
better approach for validation of this kind. we are planning to try partitio
n
approach. If there is any better way please help with your recommendations.
Regards,
MuraliWhich tool are you using?
"Murali" wrote:

> We have data migration process that transfers around 450 million records f
rom
> one DB to Another DB. There is a validation that takes place for the migra
ted
> data that is time consuming (few days). The following validations takes pl
ace
> on migrated data
> NULL check
> Length Check
> Numeric Precision Check
> So its looping through 450(rows) million * 30 (columns) times. so it takes
> forever to complete the validation process and moreover the space
> requirements also growing exponentially :-(. I would like to know is there
a
> better approach for validation of this kind. we are planning to try partit
ion
> approach. If there is any better way please help with your recommendations
.
> Regards,
> Murali
>

Data Transformation Services.Linked server Error while connecting to Oracle

Hi,

I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.

But when I give import data and give the parameters to hook up the Oracle Server its

throwing a "Linked Server Error and says insufficient previleges"...

Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?

Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error

Thanks in Advance

Kings Indian

Have you set a username and password on the DTS Package?
|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||.|||

Kings Indian wrote:

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

Hello again Kings Indian, my apologise for the time it took to getting back to you.

Ok the best thing to do is go into Design mode of the DTS package.

Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.

I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791

You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.

Anyway I hope this works for you, if not come back and we shall see if we can progress further.

hth

Pace

Data Transformation Services.Linked server Error while connecting to Oracle

Hi,

I am in the process of doing DB migration from Oracle 9i to SQL Server..I am able to connect to SQL Server.

But when I give import data and give the parameters to hook up the Oracle Server its

throwing a "Linked Server Error and says insufficient previleges"...

Is there any problem with my Oracle id? Using my Oracle id i was able to take a read and write in to the oracle db.. Secondly is there any other thing that should in taken in to consideration when configuring SQL Server DTS while connecting to Oracle to avoid this linked server problem?

Also there isnt a connectivity problem if I directly connect to Oracle using SQL Plus..The server is running fine. But only when I try connecting it through DTS to import data, its not connecting and throwing this error

Thanks in Advance

Kings Indian

Have you set a username and password on the DTS Package?
|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

|||.|||

Kings Indian wrote:

Pace_uk wrote:

Have you set a username and password on the DTS Package?

Could you explain a bit more on how to do that?

The things I did was I connected to the SQL Server and then clicked the DTS menu and clicked import data and tried providing configurations of the Oracle and it threw this Error..I am basically a front end programmer new to this migration stuff and admin related stuff.

Can you please list out the steps that needs to be addressed apart the aforementioned ones I’ve tried out.

Thanks in Advance

Hello again Kings Indian, my apologise for the time it took to getting back to you.

Ok the best thing to do is go into Design mode of the DTS package.

Once you are in and happy with what the package will do, click the "package" menu at the top left of the designer. Here you may choose the "Save As" option, once you have selected this you can see that you can save the package to run under the windows authentication method or more importantly in your case, there is an alternative. Save as SQL Authentication, here you may provide the DBO account that you wish the package to run as.

I found this link for you that you may find useful; http://www.databasejournal.com/features/mssql/article.php/3404791

You may also want to look DTS up in SQL Books Online, there is a lot of documentation on the subject.

Anyway I hope this works for you, if not come back and we shall see if we can progress further.

hth

Pace

Data Transformation - hanging

I'm running a DTS package on SQL Server. The source is MS Access and the
target is Oracle.

On a "Drop Table" command the process just hangs. There are no foreign keys on
the table. Several tables have already been processed successfully by this
time.

I think I've ruled out corruption by dropping and recreating the target
database on Oracle.

Any ideas?

M ManHi

There may be some lock on the table from a different process. I assume that
you are using this as a temporary table, therefore why not create these in
your SQL Server database rather than the destination one?

John

"MMan37x" <mman37x@.cs.com> wrote in message
news:20041015203641.02596.00002058@.mb-m13.news.cs.com...
> I'm running a DTS package on SQL Server. The source is MS Access and the
> target is Oracle.
> On a "Drop Table" command the process just hangs. There are no foreign
keys on
> the table. Several tables have already been processed successfully by
this
> time.
> I think I've ruled out corruption by dropping and recreating the target
> database on Oracle.
> Any ideas?
> M Man