Hi,
When you've upgraded your server (faster CPU, faster disks, more memory)
and when you've improved all queries and indexes, what's the next thing
to do when you're database server gets performance problems?
I've tried out spreading data and using multiple servers, but it seems
using 1) join queries on tables residing on different servers and 2)
using distributed transaction have negative impact on performance.
I wonder how large firms, storing terrabytes of data, running websites
with thousands of simultanuous users inserting, deleting and updating
data continuously, manage to stay out of performance troubles?
Is it just well prepared development, saying data is spread over
multiple servers in a way that the necessity of the servers have to
speak with each other is minimized?
Thanks in advance,
Kind regards,
Peter Van Wilrijk.The multiple server scenario only really gives a performance boost if you're
using distributed partitioned views, and using those requires a very specific
table design. Check out BOL for basic info on DPVs, plus there are plenty of
articles and whitepapers out there on their use.
You're correct, in most cases simply moving a table to another server and
linking to it will actually decrease performance. If you've done your
hardware tuning, OS and SQL Server tuning, you really need to dig down into
not only the database design, but the application design on top of that.
Companies with huge OLTP databases spend a great deal of time on optimizing
database and application design.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
processors.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||James, Joseph,
Thanks a lot for your advice.
I inventarised all database references with free trial of Diana, found
on the web. Now, I started changing database structure, duplicating
static tables, user defining functions and adapting database references
in stored procedures, so the most often used stored procedures don't
have to link.
Of course this requires table synchronization when those "static" tables
have to be updated. I'm writing routines for it. Perhaps it's not the
best solution ... but ... We're a small company with small budgets. So,
no much money for special hardware setups or for expensive training
courses on features as replication or federated sql servers.
Kind regards,
Peter van Wilrijk.
JosephPruiett wrote:
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
>>Hi,
>>When you've upgraded your server (faster CPU, faster disks, more memory)
>>and when you've improved all queries and indexes, what's the next thing
>>to do when you're database server gets performance problems?
>>I've tried out spreading data and using multiple servers, but it seems
>>using 1) join queries on tables residing on different servers and 2)
>>using distributed transaction have negative impact on performance.
>>I wonder how large firms, storing terrabytes of data, running websites
>>with thousands of simultanuous users inserting, deleting and updating
>>data continuously, manage to stay out of performance troubles?
>>Is it just well prepared development, saying data is spread over
>>multiple servers in a way that the necessity of the servers have to
>>speak with each other is minimized?
>>Thanks in advance,
>>Kind regards,
>>Peter Van Wilrijk.|||Thanks Joseph,
I thought clustering was there to handle (backup) hardware faillure, not
to improve performance. so your answer made me curious and I started
searching more info.
I found out ...
1) SAN = an array of disks (storage devices)
2) multiple servers can be accessed as 1 virtual SQL server
May I conclude that ...
1) clustering can be used as a kind of load balancing, meaning CPU and
RAM work (eg execution of stored procedures) is spread (at random?) on
the multiple servers in the cluster?
2) clustering always requires a shared disk system
3) since you work with a shared disk ... performance issues due to high
disk access isn't solved with clustering?
4) or can SAN be used as a load balancer for storage access, meaning SAN
can read and write simultaneously on multiple disks and act as if it's
virtual one disk?
I'm really curious about this.
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
JosephPruiett wrote:
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
>>Hi,
>>When you've upgraded your server (faster CPU, faster disks, more memory)
>>and when you've improved all queries and indexes, what's the next thing
>>to do when you're database server gets performance problems?
>>I've tried out spreading data and using multiple servers, but it seems
>>using 1) join queries on tables residing on different servers and 2)
>>using distributed transaction have negative impact on performance.
>>I wonder how large firms, storing terrabytes of data, running websites
>>with thousands of simultanuous users inserting, deleting and updating
>>data continuously, manage to stay out of performance troubles?
>>Is it just well prepared development, saying data is spread over
>>multiple servers in a way that the necessity of the servers have to
>>speak with each other is minimized?
>>Thanks in advance,
>>Kind regards,
>>Peter Van Wilrijk.|||> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
No. One SQL Server instance is only executing on one node (server) which at that moment is the only
node that can access the shared disk. Cluster is only fail-over. All types of load balancing takes
some work, whether it is using replication, distributed partitioned views or having several
instances and manually partitioning the information amongst them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message news:42C10BF2.2020205@.koopjeskrant.be...
> Thanks Joseph,
> I thought clustering was there to handle (backup) hardware faillure, not to improve performance.
> so your answer made me curious and I started searching more info.
> I found out ...
> 1) SAN = an array of disks (storage devices)
> 2) multiple servers can be accessed as 1 virtual SQL server
> May I conclude that ...
> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
> 2) clustering always requires a shared disk system
> 3) since you work with a shared disk ... performance issues due to high disk access isn't solved
> with clustering?
> 4) or can SAN be used as a load balancer for storage access, meaning SAN can read and write
> simultaneously on multiple disks and act as if it's virtual one disk?
> I'm really curious about this.
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
> JosephPruiett wrote:
>> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8 processors.
>> "Peter Van Wilrijk" wrote:
>>
>>Hi,
>>When you've upgraded your server (faster CPU, faster disks, more memory) and when you've improved
>>all queries and indexes, what's the next thing to do when you're database server gets performance
>>problems?
>>I've tried out spreading data and using multiple servers, but it seems using 1) join queries on
>>tables residing on different servers and 2) using distributed transaction have negative impact on
>>performance.
>>I wonder how large firms, storing terrabytes of data, running websites with thousands of
>>simultanuous users inserting, deleting and updating data continuously, manage to stay out of
>>performance troubles?
>>Is it just well prepared development, saying data is spread over multiple servers in a way that
>>the necessity of the servers have to speak with each other is minimized?
>>Thanks in advance,
>>Kind regards,
>>Peter Van Wilrijk.sql
Showing posts with label upgraded. Show all posts
Showing posts with label upgraded. Show all posts
Wednesday, March 28, 2012
moving to multiple servers?
Hi,
When you've upgraded your server (faster CPU, faster disks, more memory)
and when you've improved all queries and indexes, what's the next thing
to do when you're database server gets performance problems?
I've tried out spreading data and using multiple servers, but it seems
using 1) join queries on tables residing on different servers and 2)
using distributed transaction have negative impact on performance.
I wonder how large firms, storing terrabytes of data, running websites
with thousands of simultanuous users inserting, deleting and updating
data continuously, manage to stay out of performance troubles?
Is it just well prepared development, saying data is spread over
multiple servers in a way that the necessity of the servers have to
speak with each other is minimized?
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
The multiple server scenario only really gives a performance boost if you're
using distributed partitioned views, and using those requires a very specific
table design. Check out BOL for basic info on DPVs, plus there are plenty of
articles and whitepapers out there on their use.
You're correct, in most cases simply moving a table to another server and
linking to it will actually decrease performance. If you've done your
hardware tuning, OS and SQL Server tuning, you really need to dig down into
not only the database design, but the application design on top of that.
Companies with huge OLTP databases spend a great deal of time on optimizing
database and application design.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>
|||Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
processors.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>
|||James, Joseph,
Thanks a lot for your advice.
I inventarised all database references with free trial of Diana, found
on the web. Now, I started changing database structure, duplicating
static tables, user defining functions and adapting database references
in stored procedures, so the most often used stored procedures don't
have to link.
Of course this requires table synchronization when those "static" tables
have to be updated. I'm writing routines for it. Perhaps it's not the
best solution ... but ... We're a small company with small budgets. So,
no much money for special hardware setups or for expensive training
courses on features as replication or federated sql servers.
Kind regards,
Peter van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
|||Thanks Joseph,
I thought clustering was there to handle (backup) hardware faillure, not
to improve performance. so your answer made me curious and I started
searching more info.
I found out ...
1) SAN = an array of disks (storage devices)
2) multiple servers can be accessed as 1 virtual SQL server
May I conclude that ...
1) clustering can be used as a kind of load balancing, meaning CPU and
RAM work (eg execution of stored procedures) is spread (at random?) on
the multiple servers in the cluster?
2) clustering always requires a shared disk system
3) since you work with a shared disk ... performance issues due to high
disk access isn't solved with clustering?
4) or can SAN be used as a load balancer for storage access, meaning SAN
can read and write simultaneously on multiple disks and act as if it's
virtual one disk?
I'm really curious about this.
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
|||> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
No. One SQL Server instance is only executing on one node (server) which at that moment is the only
node that can access the shared disk. Cluster is only fail-over. All types of load balancing takes
some work, whether it is using replication, distributed partitioned views or having several
instances and manually partitioning the information amongst them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message news:42C10BF2.2020205@.koopjeskrant.be...[vbcol=seagreen]
> Thanks Joseph,
> I thought clustering was there to handle (backup) hardware faillure, not to improve performance.
> so your answer made me curious and I started searching more info.
> I found out ...
> 1) SAN = an array of disks (storage devices)
> 2) multiple servers can be accessed as 1 virtual SQL server
> May I conclude that ...
> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
> 2) clustering always requires a shared disk system
> 3) since you work with a shared disk ... performance issues due to high disk access isn't solved
> with clustering?
> 4) or can SAN be used as a load balancer for storage access, meaning SAN can read and write
> simultaneously on multiple disks and act as if it's virtual one disk?
> I'm really curious about this.
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
> JosephPruiett wrote:
When you've upgraded your server (faster CPU, faster disks, more memory)
and when you've improved all queries and indexes, what's the next thing
to do when you're database server gets performance problems?
I've tried out spreading data and using multiple servers, but it seems
using 1) join queries on tables residing on different servers and 2)
using distributed transaction have negative impact on performance.
I wonder how large firms, storing terrabytes of data, running websites
with thousands of simultanuous users inserting, deleting and updating
data continuously, manage to stay out of performance troubles?
Is it just well prepared development, saying data is spread over
multiple servers in a way that the necessity of the servers have to
speak with each other is minimized?
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
The multiple server scenario only really gives a performance boost if you're
using distributed partitioned views, and using those requires a very specific
table design. Check out BOL for basic info on DPVs, plus there are plenty of
articles and whitepapers out there on their use.
You're correct, in most cases simply moving a table to another server and
linking to it will actually decrease performance. If you've done your
hardware tuning, OS and SQL Server tuning, you really need to dig down into
not only the database design, but the application design on top of that.
Companies with huge OLTP databases spend a great deal of time on optimizing
database and application design.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>
|||Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
processors.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>
|||James, Joseph,
Thanks a lot for your advice.
I inventarised all database references with free trial of Diana, found
on the web. Now, I started changing database structure, duplicating
static tables, user defining functions and adapting database references
in stored procedures, so the most often used stored procedures don't
have to link.
Of course this requires table synchronization when those "static" tables
have to be updated. I'm writing routines for it. Perhaps it's not the
best solution ... but ... We're a small company with small budgets. So,
no much money for special hardware setups or for expensive training
courses on features as replication or federated sql servers.
Kind regards,
Peter van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
|||Thanks Joseph,
I thought clustering was there to handle (backup) hardware faillure, not
to improve performance. so your answer made me curious and I started
searching more info.
I found out ...
1) SAN = an array of disks (storage devices)
2) multiple servers can be accessed as 1 virtual SQL server
May I conclude that ...
1) clustering can be used as a kind of load balancing, meaning CPU and
RAM work (eg execution of stored procedures) is spread (at random?) on
the multiple servers in the cluster?
2) clustering always requires a shared disk system
3) since you work with a shared disk ... performance issues due to high
disk access isn't solved with clustering?
4) or can SAN be used as a load balancer for storage access, meaning SAN
can read and write simultaneously on multiple disks and act as if it's
virtual one disk?
I'm really curious about this.
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
|||> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
No. One SQL Server instance is only executing on one node (server) which at that moment is the only
node that can access the shared disk. Cluster is only fail-over. All types of load balancing takes
some work, whether it is using replication, distributed partitioned views or having several
instances and manually partitioning the information amongst them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message news:42C10BF2.2020205@.koopjeskrant.be...[vbcol=seagreen]
> Thanks Joseph,
> I thought clustering was there to handle (backup) hardware faillure, not to improve performance.
> so your answer made me curious and I started searching more info.
> I found out ...
> 1) SAN = an array of disks (storage devices)
> 2) multiple servers can be accessed as 1 virtual SQL server
> May I conclude that ...
> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
> 2) clustering always requires a shared disk system
> 3) since you work with a shared disk ... performance issues due to high disk access isn't solved
> with clustering?
> 4) or can SAN be used as a load balancer for storage access, meaning SAN can read and write
> simultaneously on multiple disks and act as if it's virtual one disk?
> I'm really curious about this.
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
> JosephPruiett wrote:
moving to multiple servers?
Hi,
When you've upgraded your server (faster CPU, faster disks, more memory)
and when you've improved all queries and indexes, what's the next thing
to do when you're database server gets performance problems?
I've tried out spreading data and using multiple servers, but it seems
using 1) join queries on tables residing on different servers and 2)
using distributed transaction have negative impact on performance.
I wonder how large firms, storing terrabytes of data, running websites
with thousands of simultanuous users inserting, deleting and updating
data continuously, manage to stay out of performance troubles?
Is it just well prepared development, saying data is spread over
multiple servers in a way that the necessity of the servers have to
speak with each other is minimized?
Thanks in advance,
Kind regards,
Peter Van Wilrijk.The multiple server scenario only really gives a performance boost if you're
using distributed partitioned views, and using those requires a very specifi
c
table design. Check out BOL for basic info on DPVs, plus there are plenty o
f
articles and whitepapers out there on their use.
You're correct, in most cases simply moving a table to another server and
linking to it will actually decrease performance. If you've done your
hardware tuning, OS and SQL Server tuning, you really need to dig down into
not only the database design, but the application design on top of that.
Companies with huge OLTP databases spend a great deal of time on optimizing
database and application design.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
processors.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||James, Joseph,
Thanks a lot for your advice.
I inventarised all database references with free trial of Diana, found
on the web. Now, I started changing database structure, duplicating
static tables, user defining functions and adapting database references
in stored procedures, so the most often used stored procedures don't
have to link.
Of course this requires table synchronization when those "static" tables
have to be updated. I'm writing routines for it. Perhaps it's not the
best solution ... but ... We're a small company with small budgets. So,
no much money for special hardware setups or for expensive training
courses on features as replication or federated sql servers.
Kind regards,
Peter van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>|||Thanks Joseph,
I thought clustering was there to handle (backup) hardware faillure, not
to improve performance. so your answer made me curious and I started
searching more info.
I found out ...
1) SAN = an array of disks (storage devices)
2) multiple servers can be accessed as 1 virtual SQL server
May I conclude that ...
1) clustering can be used as a kind of load balancing, meaning CPU and
RAM work (eg execution of stored procedures) is spread (at random?) on
the multiple servers in the cluster?
2) clustering always requires a shared disk system
3) since you work with a shared disk ... performance issues due to high
disk access isn't solved with clustering?
4) or can SAN be used as a load balancer for storage access, meaning SAN
can read and write simultaneously on multiple disks and act as if it's
virtual one disk?
I'm really curious about this.
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>|||> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execut
ion of
> stored procedures) is spread (at random?) on the multiple servers in the cluster'
?
No. One SQL Server instance is only executing on one node (server) which at
that moment is the only
node that can access the shared disk. Cluster is only fail-over. All types o
f load balancing takes
some work, whether it is using replication, distributed partitioned views or
having several
instances and manually partitioning the information amongst them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message news:42C10BF2.2020205@.koopjeskran
t.be...[vbcol=seagreen]
> Thanks Joseph,
> I thought clustering was there to handle (backup) hardware faillure, not t
o improve performance.
> so your answer made me curious and I started searching more info.
> I found out ...
> 1) SAN = an array of disks (storage devices)
> 2) multiple servers can be accessed as 1 virtual SQL server
> May I conclude that ...
> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM
work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the c
luster?
> 2) clustering always requires a shared disk system
> 3) since you work with a shared disk ... performance issues due to high di
sk access isn't solved
> with clustering?
> 4) or can SAN be used as a load balancer for storage access, meaning SAN c
an read and write
> simultaneously on multiple disks and act as if it's virtual one disk?
> I'm really curious about this.
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
> JosephPruiett wrote:
When you've upgraded your server (faster CPU, faster disks, more memory)
and when you've improved all queries and indexes, what's the next thing
to do when you're database server gets performance problems?
I've tried out spreading data and using multiple servers, but it seems
using 1) join queries on tables residing on different servers and 2)
using distributed transaction have negative impact on performance.
I wonder how large firms, storing terrabytes of data, running websites
with thousands of simultanuous users inserting, deleting and updating
data continuously, manage to stay out of performance troubles?
Is it just well prepared development, saying data is spread over
multiple servers in a way that the necessity of the servers have to
speak with each other is minimized?
Thanks in advance,
Kind regards,
Peter Van Wilrijk.The multiple server scenario only really gives a performance boost if you're
using distributed partitioned views, and using those requires a very specifi
c
table design. Check out BOL for basic info on DPVs, plus there are plenty o
f
articles and whitepapers out there on their use.
You're correct, in most cases simply moving a table to another server and
linking to it will actually decrease performance. If you've done your
hardware tuning, OS and SQL Server tuning, you really need to dig down into
not only the database design, but the application design on top of that.
Companies with huge OLTP databases spend a great deal of time on optimizing
database and application design.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
processors.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||James, Joseph,
Thanks a lot for your advice.
I inventarised all database references with free trial of Diana, found
on the web. Now, I started changing database structure, duplicating
static tables, user defining functions and adapting database references
in stored procedures, so the most often used stored procedures don't
have to link.
Of course this requires table synchronization when those "static" tables
have to be updated. I'm writing routines for it. Perhaps it's not the
best solution ... but ... We're a small company with small budgets. So,
no much money for special hardware setups or for expensive training
courses on features as replication or federated sql servers.
Kind regards,
Peter van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>|||Thanks Joseph,
I thought clustering was there to handle (backup) hardware faillure, not
to improve performance. so your answer made me curious and I started
searching more info.
I found out ...
1) SAN = an array of disks (storage devices)
2) multiple servers can be accessed as 1 virtual SQL server
May I conclude that ...
1) clustering can be used as a kind of load balancing, meaning CPU and
RAM work (eg execution of stored procedures) is spread (at random?) on
the multiple servers in the cluster?
2) clustering always requires a shared disk system
3) since you work with a shared disk ... performance issues due to high
disk access isn't solved with clustering?
4) or can SAN be used as a load balancer for storage access, meaning SAN
can read and write simultaneously on multiple disks and act as if it's
virtual one disk?
I'm really curious about this.
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
JosephPruiett wrote:[vbcol=seagreen]
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>|||> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execut
ion of
> stored procedures) is spread (at random?) on the multiple servers in the cluster'
?
No. One SQL Server instance is only executing on one node (server) which at
that moment is the only
node that can access the shared disk. Cluster is only fail-over. All types o
f load balancing takes
some work, whether it is using replication, distributed partitioned views or
having several
instances and manually partitioning the information amongst them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message news:42C10BF2.2020205@.koopjeskran
t.be...[vbcol=seagreen]
> Thanks Joseph,
> I thought clustering was there to handle (backup) hardware faillure, not t
o improve performance.
> so your answer made me curious and I started searching more info.
> I found out ...
> 1) SAN = an array of disks (storage devices)
> 2) multiple servers can be accessed as 1 virtual SQL server
> May I conclude that ...
> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM
work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the c
luster?
> 2) clustering always requires a shared disk system
> 3) since you work with a shared disk ... performance issues due to high di
sk access isn't solved
> with clustering?
> 4) or can SAN be used as a load balancer for storage access, meaning SAN c
an read and write
> simultaneously on multiple disks and act as if it's virtual one disk?
> I'm really curious about this.
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
> JosephPruiett wrote:
Monday, March 12, 2012
Moving SQL 2005 to new machine
I'm looking for help moving SQL Server to a new machine. The current
SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
x64).
I haven't seen any How To docs specifically for SQL 2005 on this topic,
but the information for 2000 seemed relevant enough. Following advice
I've read, my plan was to back up master, model, and msdb; then retore
master and then model and msdb; and then do the same for my user dbs.
To this point, I have not been able to restore master. This is how
it's gone:
Fresh install
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf in wrong path
Realize that 32-bit version of SQL is installed causing path difference
("\Program Files (x86)\")
Uninstall SQL and reinstall with SQL x64
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
while old machine had just "\MSSQL\")
SQL Server no longer starts (log shows "Could not open file" for
aforementioned file)
I'd be very grateful for some input. (btw, I've also attempted a
detach/attach approach, but wasn't able to detach system dbs easily.)
Thanks in advance.Hi
This article was updated to also cover SQL 2005:
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"matty2112@.hotmail.com" wrote:
> I'm looking for help moving SQL Server to a new machine. The current
> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
> x64).
> I haven't seen any How To docs specifically for SQL 2005 on this topic,
> but the information for 2000 seemed relevant enough. Following advice
> I've read, my plan was to back up master, model, and msdb; then retore
> master and then model and msdb; and then do the same for my user dbs.
> To this point, I have not been able to restore master. This is how
> it's gone:
> Fresh install
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
> Realize that 32-bit version of SQL is installed causing path difference
> ("\Program Files (x86)\")
> Uninstall SQL and reinstall with SQL x64
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
> while old machine had just "\MSSQL\")
> SQL Server no longer starts (log shows "Could not open file" for
> aforementioned file)
> I'd be very grateful for some input. (btw, I've also attempted a
> detach/attach approach, but wasn't able to detach system dbs easily.)
> Thanks in advance.
>|||Hi
I noticed in another post that there may be an issue with the resource DB if
you move the master database.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE7199E1-9C6D-412F-B83A-9D9F9756493B@.microsoft.com...[vbcol=seagreen]
> Hi
> This article was updated to also cover SQL 2005:
> http://support.microsoft.com/defaul...b;en-us;Q314546
> John
> "matty2112@.hotmail.com" wrote:
>
SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
x64).
I haven't seen any How To docs specifically for SQL 2005 on this topic,
but the information for 2000 seemed relevant enough. Following advice
I've read, my plan was to back up master, model, and msdb; then retore
master and then model and msdb; and then do the same for my user dbs.
To this point, I have not been able to restore master. This is how
it's gone:
Fresh install
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf in wrong path
Realize that 32-bit version of SQL is installed causing path difference
("\Program Files (x86)\")
Uninstall SQL and reinstall with SQL x64
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
while old machine had just "\MSSQL\")
SQL Server no longer starts (log shows "Could not open file" for
aforementioned file)
I'd be very grateful for some input. (btw, I've also attempted a
detach/attach approach, but wasn't able to detach system dbs easily.)
Thanks in advance.Hi
This article was updated to also cover SQL 2005:
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"matty2112@.hotmail.com" wrote:
> I'm looking for help moving SQL Server to a new machine. The current
> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
> x64).
> I haven't seen any How To docs specifically for SQL 2005 on this topic,
> but the information for 2000 seemed relevant enough. Following advice
> I've read, my plan was to back up master, model, and msdb; then retore
> master and then model and msdb; and then do the same for my user dbs.
> To this point, I have not been able to restore master. This is how
> it's gone:
> Fresh install
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
> Realize that 32-bit version of SQL is installed causing path difference
> ("\Program Files (x86)\")
> Uninstall SQL and reinstall with SQL x64
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
> while old machine had just "\MSSQL\")
> SQL Server no longer starts (log shows "Could not open file" for
> aforementioned file)
> I'd be very grateful for some input. (btw, I've also attempted a
> detach/attach approach, but wasn't able to detach system dbs easily.)
> Thanks in advance.
>|||Hi
I noticed in another post that there may be an issue with the resource DB if
you move the master database.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE7199E1-9C6D-412F-B83A-9D9F9756493B@.microsoft.com...[vbcol=seagreen]
> Hi
> This article was updated to also cover SQL 2005:
> http://support.microsoft.com/defaul...b;en-us;Q314546
> John
> "matty2112@.hotmail.com" wrote:
>
Moving SQL 2005 to new machine
I'm looking for help moving SQL Server to a new machine. The current
SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
x64).
I haven't seen any How To docs specifically for SQL 2005 on this topic,
but the information for 2000 seemed relevant enough. Following advice
I've read, my plan was to back up master, model, and msdb; then retore
master and then model and msdb; and then do the same for my user dbs.
To this point, I have not been able to restore master. This is how
it's gone:
Fresh install
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf in wrong path
Realize that 32-bit version of SQL is installed causing path difference
("\Program Files (x86)\")
Uninstall SQL and reinstall with SQL x64
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
while old machine had just "\MSSQL\")
SQL Server no longer starts (log shows "Could not open file" for
aforementioned file)
I'd be very grateful for some input. (btw, I've also attempted a
detach/attach approach, but wasn't able to detach system dbs easily.)
Thanks in advance.
Hi
This article was updated to also cover SQL 2005:
http://support.microsoft.com/default...;en-us;Q314546
John
"matty2112@.hotmail.com" wrote:
> I'm looking for help moving SQL Server to a new machine. The current
> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
> x64).
> I haven't seen any How To docs specifically for SQL 2005 on this topic,
> but the information for 2000 seemed relevant enough. Following advice
> I've read, my plan was to back up master, model, and msdb; then retore
> master and then model and msdb; and then do the same for my user dbs.
> To this point, I have not been able to restore master. This is how
> it's gone:
> Fresh install
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
> Realize that 32-bit version of SQL is installed causing path difference
> ("\Program Files (x86)\")
> Uninstall SQL and reinstall with SQL x64
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
> while old machine had just "\MSSQL\")
> SQL Server no longer starts (log shows "Could not open file" for
> aforementioned file)
> I'd be very grateful for some input. (btw, I've also attempted a
> detach/attach approach, but wasn't able to detach system dbs easily.)
> Thanks in advance.
>
|||Hi
I noticed in another post that there may be an issue with the resource DB if
you move the master database.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE7199E1-9C6D-412F-B83A-9D9F9756493B@.microsoft.com...[vbcol=seagreen]
> Hi
> This article was updated to also cover SQL 2005:
> http://support.microsoft.com/default...;en-us;Q314546
> John
> "matty2112@.hotmail.com" wrote:
SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
x64).
I haven't seen any How To docs specifically for SQL 2005 on this topic,
but the information for 2000 seemed relevant enough. Following advice
I've read, my plan was to back up master, model, and msdb; then retore
master and then model and msdb; and then do the same for my user dbs.
To this point, I have not been able to restore master. This is how
it's gone:
Fresh install
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf in wrong path
Realize that 32-bit version of SQL is installed causing path difference
("\Program Files (x86)\")
Uninstall SQL and reinstall with SQL x64
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
while old machine had just "\MSSQL\")
SQL Server no longer starts (log shows "Could not open file" for
aforementioned file)
I'd be very grateful for some input. (btw, I've also attempted a
detach/attach approach, but wasn't able to detach system dbs easily.)
Thanks in advance.
Hi
This article was updated to also cover SQL 2005:
http://support.microsoft.com/default...;en-us;Q314546
John
"matty2112@.hotmail.com" wrote:
> I'm looking for help moving SQL Server to a new machine. The current
> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
> x64).
> I haven't seen any How To docs specifically for SQL 2005 on this topic,
> but the information for 2000 seemed relevant enough. Following advice
> I've read, my plan was to back up master, model, and msdb; then retore
> master and then model and msdb; and then do the same for my user dbs.
> To this point, I have not been able to restore master. This is how
> it's gone:
> Fresh install
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
> Realize that 32-bit version of SQL is installed causing path difference
> ("\Program Files (x86)\")
> Uninstall SQL and reinstall with SQL x64
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
> while old machine had just "\MSSQL\")
> SQL Server no longer starts (log shows "Could not open file" for
> aforementioned file)
> I'd be very grateful for some input. (btw, I've also attempted a
> detach/attach approach, but wasn't able to detach system dbs easily.)
> Thanks in advance.
>
|||Hi
I noticed in another post that there may be an issue with the resource DB if
you move the master database.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE7199E1-9C6D-412F-B83A-9D9F9756493B@.microsoft.com...[vbcol=seagreen]
> Hi
> This article was updated to also cover SQL 2005:
> http://support.microsoft.com/default...;en-us;Q314546
> John
> "matty2112@.hotmail.com" wrote:
Moving SQL 2005 to new machine
I'm looking for help moving SQL Server to a new machine. The current
SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
x64).
I haven't seen any How To docs specifically for SQL 2005 on this topic,
but the information for 2000 seemed relevant enough. Following advice
I've read, my plan was to back up master, model, and msdb; then retore
master and then model and msdb; and then do the same for my user dbs.
To this point, I have not been able to restore master. This is how
it's gone:
Fresh install
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf in wrong path
Realize that 32-bit version of SQL is installed causing path difference
("\Program Files (x86)\")
Uninstall SQL and reinstall with SQL x64
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
while old machine had just "\MSSQL\")
SQL Server no longer starts (log shows "Could not open file" for
aforementioned file)
I'd be very grateful for some input. (btw, I've also attempted a
detach/attach approach, but wasn't able to detach system dbs easily.)
Thanks in advance.Hi
This article was updated to also cover SQL 2005:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"matty2112@.hotmail.com" wrote:
> I'm looking for help moving SQL Server to a new machine. The current
> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
> x64).
> I haven't seen any How To docs specifically for SQL 2005 on this topic,
> but the information for 2000 seemed relevant enough. Following advice
> I've read, my plan was to back up master, model, and msdb; then retore
> master and then model and msdb; and then do the same for my user dbs.
> To this point, I have not been able to restore master. This is how
> it's gone:
> Fresh install
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
> Realize that 32-bit version of SQL is installed causing path difference
> ("\Program Files (x86)\")
> Uninstall SQL and reinstall with SQL x64
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
> while old machine had just "\MSSQL\")
> SQL Server no longer starts (log shows "Could not open file" for
> aforementioned file)
> I'd be very grateful for some input. (btw, I've also attempted a
> detach/attach approach, but wasn't able to detach system dbs easily.)
> Thanks in advance.
>|||Hi
I noticed in another post that there may be an issue with the resource DB if
you move the master database.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE7199E1-9C6D-412F-B83A-9D9F9756493B@.microsoft.com...
> Hi
> This article was updated to also cover SQL 2005:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> John
> "matty2112@.hotmail.com" wrote:
>> I'm looking for help moving SQL Server to a new machine. The current
>> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
>> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
>> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
>> x64).
>> I haven't seen any How To docs specifically for SQL 2005 on this topic,
>> but the information for 2000 seemed relevant enough. Following advice
>> I've read, my plan was to back up master, model, and msdb; then retore
>> master and then model and msdb; and then do the same for my user dbs.
>> To this point, I have not been able to restore master. This is how
>> it's gone:
>> Fresh install
>> Run mssqlserver in single-user mode ... restore master ... goes to 100%
>> ... then produces error
>> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
>> Realize that 32-bit version of SQL is installed causing path difference
>> ("\Program Files (x86)\")
>> Uninstall SQL and reinstall with SQL x64
>> Run mssqlserver in single-user mode ... restore master ... goes to 100%
>> ... then produces error
>> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
>> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
>> while old machine had just "\MSSQL\")
>> SQL Server no longer starts (log shows "Could not open file" for
>> aforementioned file)
>> I'd be very grateful for some input. (btw, I've also attempted a
>> detach/attach approach, but wasn't able to detach system dbs easily.)
>> Thanks in advance.
>>
SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
x64).
I haven't seen any How To docs specifically for SQL 2005 on this topic,
but the information for 2000 seemed relevant enough. Following advice
I've read, my plan was to back up master, model, and msdb; then retore
master and then model and msdb; and then do the same for my user dbs.
To this point, I have not been able to restore master. This is how
it's gone:
Fresh install
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf in wrong path
Realize that 32-bit version of SQL is installed causing path difference
("\Program Files (x86)\")
Uninstall SQL and reinstall with SQL x64
Run mssqlserver in single-user mode ... restore master ... goes to 100%
... then produces error
Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
while old machine had just "\MSSQL\")
SQL Server no longer starts (log shows "Could not open file" for
aforementioned file)
I'd be very grateful for some input. (btw, I've also attempted a
detach/attach approach, but wasn't able to detach system dbs easily.)
Thanks in advance.Hi
This article was updated to also cover SQL 2005:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"matty2112@.hotmail.com" wrote:
> I'm looking for help moving SQL Server to a new machine. The current
> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
> x64).
> I haven't seen any How To docs specifically for SQL 2005 on this topic,
> but the information for 2000 seemed relevant enough. Following advice
> I've read, my plan was to back up master, model, and msdb; then retore
> master and then model and msdb; and then do the same for my user dbs.
> To this point, I have not been able to restore master. This is how
> it's gone:
> Fresh install
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
> Realize that 32-bit version of SQL is installed causing path difference
> ("\Program Files (x86)\")
> Uninstall SQL and reinstall with SQL x64
> Run mssqlserver in single-user mode ... restore master ... goes to 100%
> ... then produces error
> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
> while old machine had just "\MSSQL\")
> SQL Server no longer starts (log shows "Could not open file" for
> aforementioned file)
> I'd be very grateful for some input. (btw, I've also attempted a
> detach/attach approach, but wasn't able to detach system dbs easily.)
> Thanks in advance.
>|||Hi
I noticed in another post that there may be an issue with the resource DB if
you move the master database.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CE7199E1-9C6D-412F-B83A-9D9F9756493B@.microsoft.com...
> Hi
> This article was updated to also cover SQL 2005:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> John
> "matty2112@.hotmail.com" wrote:
>> I'm looking for help moving SQL Server to a new machine. The current
>> SQL machine runs Windows Server 2003 ... we recently upgraded SQL on
>> this machine from SQL 2000 to SQL 2005 (dev). The new machine runs
>> Windows Server 2003 (x64) and has a fesh install of SQL 2005 (dev,
>> x64).
>> I haven't seen any How To docs specifically for SQL 2005 on this topic,
>> but the information for 2000 seemed relevant enough. Following advice
>> I've read, my plan was to back up master, model, and msdb; then retore
>> master and then model and msdb; and then do the same for my user dbs.
>> To this point, I have not been able to restore master. This is how
>> it's gone:
>> Fresh install
>> Run mssqlserver in single-user mode ... restore master ... goes to 100%
>> ... then produces error
>> Log specifies it's looking for mssqlsystemresource.mdf in wrong path
>> Realize that 32-bit version of SQL is installed causing path difference
>> ("\Program Files (x86)\")
>> Uninstall SQL and reinstall with SQL x64
>> Run mssqlserver in single-user mode ... restore master ... goes to 100%
>> ... then produces error
>> Log specifies it's looking for mssqlsystemresource.mdf STILL in wrong
>> path (directory structure on new machine has "\MSSQL.1\MSSQL\" path
>> while old machine had just "\MSSQL\")
>> SQL Server no longer starts (log shows "Could not open file" for
>> aforementioned file)
>> I'd be very grateful for some input. (btw, I've also attempted a
>> detach/attach approach, but wasn't able to detach system dbs easily.)
>> Thanks in advance.
>>
Friday, March 9, 2012
Moving sql 2000 to sql 2005- update query run slower
Earlier I posted this query in SQL Server Database Engine forum. I am reposting it here.
We have upgraded our database server from SQL 2000 to SQL 2005. To migrate the DB I attached the mdf file in SQL 2005. After migration website loads quicker than earlier but records updation ( no of records are 20k) got slowdown. I have found that update query run very slow in SQL 2005 as compared to SQL 2000. Although other select query run very faster. Query is called from ASP (active server page) page.
Changing of fill factor option from 0 to 70 also did not work. I also have set the competible leve to 90.
OLD Sever Config: dual xeon 1GHz, 512MB RAM, window 2000, MS SQL 2000
New server Config: Dual Core Xeon with 2 CPU, 10 GB RAM, window 2003, MS SQL 2005, SQL 2005 SP1
size of mdf file is 16GB
Rebuilting of index did not improved the performance.
Here is the code
SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")
While NOT rsEmails.EOF
Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer
objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")
objMailer.Queue = True
objMailer.Send
strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")
Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn
Wend
Thanks in advance
Hi,
Have you update Statistics?
Is MDAC version are same on both the Servers and Client Machine?!
Have you check query running from QA ? What about Execution Plan of Both Servers? I would suggest you to refer relavent thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451 and some performance Tunning tips on SSP sote http://www.sql-server-performance.com/statistics_io_time.asp & http://www.sql-server-performance.com/transact_sql.asp
HTH
Hemantgiri S. Goswami
|||
Can you post the code for the creating the objConn, specifically are you using a client or server side cursor.
I also not your are not using the recordset returned. In which case you would be better of not storing the results in a recordset.
objConn.execute strSQL
Is this code running on a seperate machine.
What is the CPU usage on the 2 machines?
How many records are in the loop?
|||Thanks Simon for your help.
Yes this code is running on seperate machine. New sql server ( i.e sql 2005) has better machine as mentioned in my question posted above. It is running slow even for 100 records, in live environment records will be arround 20k or more
here is the code to open connection
Set objConn = Server.CreateObject("ADODB.Connection")
'20030930 JDR 01 - Set 10 minute timeout
objConn.CommandTimeout = 600
objConn.Open(strConnection)
Thanks
|||My problem got solved after using two connection objects one connection for reading and another for updating the records. Here is the new code...
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CommandTimeout = 600
objConn.Open(strConnection)
Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.CommandTimeout = 600
objConn2.Open(strConnection)
SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")
While NOT rsEmails.EOF
Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer
objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")
objMailer.Queue = True
objMailer.Send
strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")
Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn2
Wend
Moving sql 2000 to sql 2005- update query run slower
Earlier I posted this query in SQL Server Database Engine forum. I am reposting it here.
We have upgraded our database server from SQL 2000 to SQL 2005. To migrate the DB I attached the mdf file in SQL 2005. After migration website loads quicker than earlier but records updation ( no of records are 20k) got slowdown. I have found that update query run very slow in SQL 2005 as compared to SQL 2000. Although other select query run very faster. Query is called from ASP (active server page) page.
Changing of fill factor option from 0 to 70 also did not work. I also have set the competible leve to 90.
OLD Sever Config: dual xeon 1GHz, 512MB RAM, window 2000, MS SQL 2000
New server Config: Dual Core Xeon with 2 CPU, 10 GB RAM, window 2003, MS SQL 2005, SQL 2005 SP1
size of mdf file is 16GB
Rebuilting of index did not improved the performance.
Here is the code
SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")
While NOT rsEmails.EOF
Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer
objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")
objMailer.Queue = True
objMailer.Send
strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")
Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn
Wend
Thanks in advance
Hi,
Have you update Statistics?
Is MDAC version are same on both the Servers and Client Machine?!
Have you check query running from QA ? What about Execution Plan of Both Servers? I would suggest you to refer relavent thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451 and some performance Tunning tips on SSP sote http://www.sql-server-performance.com/statistics_io_time.asp & http://www.sql-server-performance.com/transact_sql.asp
HTH
Hemantgiri S. Goswami
|||
Can you post the code for the creating the objConn, specifically are you using a client or server side cursor.
I also not your are not using the recordset returned. In which case you would be better of not storing the results in a recordset.
objConn.execute strSQL
Is this code running on a seperate machine.
What is the CPU usage on the 2 machines?
How many records are in the loop?
|||Thanks Simon for your help.
Yes this code is running on seperate machine. New sql server ( i.e sql 2005) has better machine as mentioned in my question posted above. It is running slow even for 100 records, in live environment records will be arround 20k or more
here is the code to open connection
Set objConn = Server.CreateObject("ADODB.Connection")
'20030930 JDR 01 - Set 10 minute timeout
objConn.CommandTimeout = 600
objConn.Open(strConnection)
Thanks
|||My problem got solved after using two connection objects one connection for reading and another for updating the records. Here is the new code...
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CommandTimeout = 600
objConn.Open(strConnection)
Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.CommandTimeout = 600
objConn2.Open(strConnection)
SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")
While NOT rsEmails.EOF
Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer
objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")
objMailer.Queue = True
objMailer.Send
strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")
Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn2
Wend
Saturday, February 25, 2012
Moving Model and tempdb
Hi!
After i Upgraded my SQL 7 to SQL 2000 my model and tempdb
database is still in th old location(C:\MSSQL7\Data). How
can i Move it. I have traid all.
Best Regards!
/Jens Nilsson
Database Administrator
Malmo - SwedenHave you looked at this
http://support.microsoft.com/?kbid=224071
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jens Nilsson" <sqlis.mas@.skane.se> wrote in message
news:069401c35cb0$37db6330$a601280a@.phx.gbl...
> Hi!
> After i Upgraded my SQL 7 to SQL 2000 my model and tempdb
> database is still in th old location(C:\MSSQL7\Data). How
> can i Move it. I have traid all.
> Best Regards!
> /Jens Nilsson
> Database Administrator
> Malmo - Sweden
After i Upgraded my SQL 7 to SQL 2000 my model and tempdb
database is still in th old location(C:\MSSQL7\Data). How
can i Move it. I have traid all.
Best Regards!
/Jens Nilsson
Database Administrator
Malmo - SwedenHave you looked at this
http://support.microsoft.com/?kbid=224071
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jens Nilsson" <sqlis.mas@.skane.se> wrote in message
news:069401c35cb0$37db6330$a601280a@.phx.gbl...
> Hi!
> After i Upgraded my SQL 7 to SQL 2000 my model and tempdb
> database is still in th old location(C:\MSSQL7\Data). How
> can i Move it. I have traid all.
> Best Regards!
> /Jens Nilsson
> Database Administrator
> Malmo - Sweden
Subscribe to:
Posts (Atom)