we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
>> we are frequently running the maintenance tasks on production during that
>> databases are not accessible.
>> to avoid this we want to switch over to the standby server during
>> maintenace and after maintenance has been completed
>> switch back to primary server.During maintenance all data changes need to
>> be replicated back to production server.
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>> To accomplish this I am planning to setup database replication from
>> producation database server to Standby server.
>> Please advise best solutions for this like microsoft trnsaction
>> replication,Goldengate replication, wansync replication.
>> I think microsoft trnsaction replication,Goldengate replication doesn't
>> support complex shema changes to get replicated.
>> Requirement :
>> 1. 10 databases need to get replicated (with 30GB - 70GB each)
>> 2. these 10 databases will have schema changes monthly.
>> 3. data changes need to moved from standby to production server after
>> maintenance has been completed.
>> Please advise
>> Thanks
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Tuesday, March 27, 2012
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
I think bi-directional transactional replication is ideal for this. Point
the feed to the standby when you want to do maintenance on the primary. Then
stop the distribution agents, do your work, and restart the agents, and then
repoint to the primary.
When you do your schema changes you will have to tear down replication on
both sides and recreate it after you have done the schema changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ObcjXaJMHHA.5000@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
I think bi-directional transactional replication is ideal for this. Point
the feed to the standby when you want to do maintenance on the primary. Then
stop the distribution agents, do your work, and restart the agents, and then
repoint to the primary.
When you do your schema changes you will have to tear down replication on
both sides and recreate it after you have done the schema changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ObcjXaJMHHA.5000@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com
Thursday, March 22, 2012
Best Practices Question
Environment is SQL Server 2005 x64 Enterprise running under Windows Server 2003 x64 Enterprise with four processors and 16GB of ram.
I have 28 data copy routines I would like to add to a SSIS package. They use the Data Reader Source to an ODBC database (InterSystems Cache) and copy the table contents to a SQL2005 database for reporting needs. The data rows in these 28 routines range from only 100 rows to over 6 million rows depending on the table. I have tested these individually and they work fine. My question is, is it a good practice to have all of these routines in a single package or can I expect performance degragation?
A single package is fine, but multiple packages are more supportable.|||Thanks Phil. I should note that in this package, all 28 copy routines are in the same data flow object. Would it be better to have them in separate data flow objects? Also, when you say more supportable, are you referring to making it easier to troubleshoot?
|||
windthorstking wrote:
Thanks Phil. I should note that in this package, all 28 copy routines are in the same data flow object. Would it be better to have them in separate data flow objects? Also, when you say more supportable, are you referring to making it easier to troubleshoot?
I'd probably put the distinct "copy routines" into their own data flows, at the very least.
And yes, I mean it will be easier to troubleshoot.sql
Sunday, March 11, 2012
Best Practice for MSDE User permissions
Hi All
I am new to MSDE/SQL Server and need some guidance on best practices for
user permissions.
I have a VB6 program running in a bakery factory
The computer network is a peer to peer 3 computer network running WIndowsXP
MSDE runs on computer A and the data entry person runs my program on this
machine to enter daily orders for their customers
A manager needs to access the MSDE data from another computer for reporting
tasks and is not allowed to enter or modify data
I am sure I can't use Windows authentication as it is only a peer network.
Is this correct?
Should I create a new Login and set individual permissions on each table or
is it OK to use the sa account etc?
Any ideas appreciated
Regards
Steve> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
Windows authentication is problematic when you have multiple computers
without a domain. It is possible by mapping a drive on the client to the
SQL Server using a local server account but this is a kluge.
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
I suggest you use SQL authentication and assign permissions to roles. You
can prompt for the user's SQL login and password during application at
startup. Never use the 'sa' login for routine application access.
USE MyDatabase
--setup role-based security
EXEC sp_addrole 'Manager'
EXEC sp_addrole 'Clerk'
GRANT SELECT ON MyTable TO Manager
GRANT SELECT, INSERT, UPDATE, DELETE ON MyOtherTable TO Manager
GRANT SELECT ON MyOtherTable TO Clerk
--create login for managers
EXEC sp_addlogin 'SomeManager', 'SomeManagerPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeManager'
EXEC sp_addrolemember 'Manager', 'SomeManager'
--create login for clerks
EXEC sp_addlogin 'SomeClerk', 'SomeClerkPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeClerk'
EXEC sp_addrolemember 'Clerk', 'SomeClerk'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6DE28FBB-20B4-4637-BC82-7BDDD9FDA62B@.microsoft.com...
> Hi All
> I am new to MSDE/SQL Server and need some guidance on best practices for
> user permissions.
> I have a VB6 program running in a bakery factory
> The computer network is a peer to peer 3 computer network running
> WIndowsXP
> MSDE runs on computer A and the data entry person runs my program on this
> machine to enter daily orders for their customers
> A manager needs to access the MSDE data from another computer for
> reporting
> tasks and is not allowed to enter or modify data
> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
> Any ideas appreciated
> --
> Regards
> Steve
I am new to MSDE/SQL Server and need some guidance on best practices for
user permissions.
I have a VB6 program running in a bakery factory
The computer network is a peer to peer 3 computer network running WIndowsXP
MSDE runs on computer A and the data entry person runs my program on this
machine to enter daily orders for their customers
A manager needs to access the MSDE data from another computer for reporting
tasks and is not allowed to enter or modify data
I am sure I can't use Windows authentication as it is only a peer network.
Is this correct?
Should I create a new Login and set individual permissions on each table or
is it OK to use the sa account etc?
Any ideas appreciated
Regards
Steve> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
Windows authentication is problematic when you have multiple computers
without a domain. It is possible by mapping a drive on the client to the
SQL Server using a local server account but this is a kluge.
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
I suggest you use SQL authentication and assign permissions to roles. You
can prompt for the user's SQL login and password during application at
startup. Never use the 'sa' login for routine application access.
USE MyDatabase
--setup role-based security
EXEC sp_addrole 'Manager'
EXEC sp_addrole 'Clerk'
GRANT SELECT ON MyTable TO Manager
GRANT SELECT, INSERT, UPDATE, DELETE ON MyOtherTable TO Manager
GRANT SELECT ON MyOtherTable TO Clerk
--create login for managers
EXEC sp_addlogin 'SomeManager', 'SomeManagerPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeManager'
EXEC sp_addrolemember 'Manager', 'SomeManager'
--create login for clerks
EXEC sp_addlogin 'SomeClerk', 'SomeClerkPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeClerk'
EXEC sp_addrolemember 'Clerk', 'SomeClerk'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6DE28FBB-20B4-4637-BC82-7BDDD9FDA62B@.microsoft.com...
> Hi All
> I am new to MSDE/SQL Server and need some guidance on best practices for
> user permissions.
> I have a VB6 program running in a bakery factory
> The computer network is a peer to peer 3 computer network running
> WIndowsXP
> MSDE runs on computer A and the data entry person runs my program on this
> machine to enter daily orders for their customers
> A manager needs to access the MSDE data from another computer for
> reporting
> tasks and is not allowed to enter or modify data
> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
> Any ideas appreciated
> --
> Regards
> Steve
Thursday, March 8, 2012
Best Practice Analyzer rule doesnt make sense
I'm running the Best Practice Analyzer on a sql 2k server in an attempt to
prepare it for 2005, and one of many rules that dont make sence is:
Object contains an INSERT statement without explicit specification of target
column list.
This rule checks objects for use of INSERT statements without explicit
specification of target column list.
When inserting into a table or view, it is recommended that the target
column_list be explicitly specified. This results in more maintainable code.
Exactly how do you explicitly specify a column? Here's the code at fault:
CREATE TABLE #Tmp ( ID int, [Name] varchar(3))
INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (1, 'No')
INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (2, 'Yes')
SELECT * FROM #Tmp
Can anyone explain why it doesnt like this code?
moondaddy@.nospam.nospam
Try this:
INSERT INTO #Tmp ([ID], [Name]) VALUES (1, 'No')
Both ID and Name are reserved words and should be avoided where possible.
Andrew J. Kelly SQL MVP
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OzHTw2K7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> I'm running the Best Practice Analyzer on a sql 2k server in an attempt to
> prepare it for 2005, and one of many rules that dont make sence is:
> Object contains an INSERT statement without explicit specification of
> target column list.
> This rule checks objects for use of INSERT statements without explicit
> specification of target column list.
> When inserting into a table or view, it is recommended that the target
> column_list be explicitly specified. This results in more maintainable
> code.
> Exactly how do you explicitly specify a column? Here's the code at fault:
> CREATE TABLE #Tmp ( ID int, [Name] varchar(3))
> INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (1, 'No')
> INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (2, 'Yes')
> SELECT * FROM #Tmp
> Can anyone explain why it doesnt like this code?
> --
> moondaddy@.nospam.nospam
>
|||Thanks. I think that did it.
btw: Good to see microsoft.public.sqlserver.integrationsvcs up and running!
Looks like I will spend some time there.
moondaddy@.nospam.nospam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23TmGwTL7FHA.2816@.tk2msftngp13.phx.gbl...
> Try this:
> INSERT INTO #Tmp ([ID], [Name]) VALUES (1, 'No')
>
> Both ID and Name are reserved words and should be avoided where possible.
> --
> Andrew J. Kelly SQL MVP
>
> "moondaddy" <moondaddy@.nospam.nospam> wrote in message
> news:OzHTw2K7FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
prepare it for 2005, and one of many rules that dont make sence is:
Object contains an INSERT statement without explicit specification of target
column list.
This rule checks objects for use of INSERT statements without explicit
specification of target column list.
When inserting into a table or view, it is recommended that the target
column_list be explicitly specified. This results in more maintainable code.
Exactly how do you explicitly specify a column? Here's the code at fault:
CREATE TABLE #Tmp ( ID int, [Name] varchar(3))
INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (1, 'No')
INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (2, 'Yes')
SELECT * FROM #Tmp
Can anyone explain why it doesnt like this code?
moondaddy@.nospam.nospam
Try this:
INSERT INTO #Tmp ([ID], [Name]) VALUES (1, 'No')
Both ID and Name are reserved words and should be avoided where possible.
Andrew J. Kelly SQL MVP
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OzHTw2K7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> I'm running the Best Practice Analyzer on a sql 2k server in an attempt to
> prepare it for 2005, and one of many rules that dont make sence is:
> Object contains an INSERT statement without explicit specification of
> target column list.
> This rule checks objects for use of INSERT statements without explicit
> specification of target column list.
> When inserting into a table or view, it is recommended that the target
> column_list be explicitly specified. This results in more maintainable
> code.
> Exactly how do you explicitly specify a column? Here's the code at fault:
> CREATE TABLE #Tmp ( ID int, [Name] varchar(3))
> INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (1, 'No')
> INSERT INTO #Tmp (#Tmp.ID, #Tmp.Name) VALUES (2, 'Yes')
> SELECT * FROM #Tmp
> Can anyone explain why it doesnt like this code?
> --
> moondaddy@.nospam.nospam
>
|||Thanks. I think that did it.
btw: Good to see microsoft.public.sqlserver.integrationsvcs up and running!
Looks like I will spend some time there.
moondaddy@.nospam.nospam
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23TmGwTL7FHA.2816@.tk2msftngp13.phx.gbl...
> Try this:
> INSERT INTO #Tmp ([ID], [Name]) VALUES (1, 'No')
>
> Both ID and Name are reserved words and should be avoided where possible.
> --
> Andrew J. Kelly SQL MVP
>
> "moondaddy" <moondaddy@.nospam.nospam> wrote in message
> news:OzHTw2K7FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
best practice analyzer feedback
Hi I've been running the BPA for a few weeks now and have followed all the
recommendations to correct my databases, but I consistently get the following
2 Non Compliance errors.
1. One or more of the databases failed the scan. It is recommended that data
and log files be kept on separate drives.
I've tried to move the log files but it won't let me even when nobody is
using the databases.
2. One or more triggers is either setting NOCOUNT to OFF or missing NOCOUNT
setting! It is generally recommended to explicitly set NOCOUNT option to ON
at the beginning of a trigger.
Here is the trigger reffered to:-
CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
FOR INSERT
AS
IF @.@.ROWCOUNT=0
RETURN
SET NOCOUNT ON
INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
FROM Inserted
As can be seen the SET NOCOUNT ON is included and it won't work if it's at
the beginning of the trigger.
The rest of the tool seems to work OK.
1 - Try to detach database first then move log file, then attach database
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> Hi I've been running the BPA for a few weeks now and have followed all the
> recommendations to correct my databases, but I consistently get the
following
> 2 Non Compliance errors.
> 1. One or more of the databases failed the scan. It is recommended that
data
> and log files be kept on separate drives.
> I've tried to move the log files but it won't let me even when nobody is
> using the databases.
>
> 2. One or more triggers is either setting NOCOUNT to OFF or missing
NOCOUNT
> setting! It is generally recommended to explicitly set NOCOUNT option to
ON
> at the beginning of a trigger.
> Here is the trigger reffered to:-
> CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
> FOR INSERT
> AS
> IF @.@.ROWCOUNT=0
> RETURN
> SET NOCOUNT ON
> INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
> SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
> FROM Inserted
> As can be seen the SET NOCOUNT ON is included and it won't work if it's at
> the beginning of the trigger.
> The rest of the tool seems to work OK.
>
>
|||Thanks t, detach and attach worked great. Do you have any idea about the
NOCOUNT problem?
jez
"t" wrote:
> 1 - Try to detach database first then move log file, then attach database
> "jez123456" <jez123456@.discussions.microsoft.com> wrote in message
> news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> following
> data
> NOCOUNT
> ON
>
>
|||Hi
It is an interesting pattern... I'll open a tracking item to see if we can
relax the check for the next release.
Thanks for the feedback.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:D67E5CBA-C13E-4272-A100-B613BD7F4E82@.microsoft.com...[vbcol=seagreen]
> Thanks t, detach and attach worked great. Do you have any idea about the
> NOCOUNT problem?
> jez
> "t" wrote:
database[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
is[vbcol=seagreen]
to[vbcol=seagreen]
it's at[vbcol=seagreen]
recommendations to correct my databases, but I consistently get the following
2 Non Compliance errors.
1. One or more of the databases failed the scan. It is recommended that data
and log files be kept on separate drives.
I've tried to move the log files but it won't let me even when nobody is
using the databases.
2. One or more triggers is either setting NOCOUNT to OFF or missing NOCOUNT
setting! It is generally recommended to explicitly set NOCOUNT option to ON
at the beginning of a trigger.
Here is the trigger reffered to:-
CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
FOR INSERT
AS
IF @.@.ROWCOUNT=0
RETURN
SET NOCOUNT ON
INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
FROM Inserted
As can be seen the SET NOCOUNT ON is included and it won't work if it's at
the beginning of the trigger.
The rest of the tool seems to work OK.
1 - Try to detach database first then move log file, then attach database
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> Hi I've been running the BPA for a few weeks now and have followed all the
> recommendations to correct my databases, but I consistently get the
following
> 2 Non Compliance errors.
> 1. One or more of the databases failed the scan. It is recommended that
data
> and log files be kept on separate drives.
> I've tried to move the log files but it won't let me even when nobody is
> using the databases.
>
> 2. One or more triggers is either setting NOCOUNT to OFF or missing
NOCOUNT
> setting! It is generally recommended to explicitly set NOCOUNT option to
ON
> at the beginning of a trigger.
> Here is the trigger reffered to:-
> CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
> FOR INSERT
> AS
> IF @.@.ROWCOUNT=0
> RETURN
> SET NOCOUNT ON
> INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
> SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
> FROM Inserted
> As can be seen the SET NOCOUNT ON is included and it won't work if it's at
> the beginning of the trigger.
> The rest of the tool seems to work OK.
>
>
|||Thanks t, detach and attach worked great. Do you have any idea about the
NOCOUNT problem?
jez
"t" wrote:
> 1 - Try to detach database first then move log file, then attach database
> "jez123456" <jez123456@.discussions.microsoft.com> wrote in message
> news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> following
> data
> NOCOUNT
> ON
>
>
|||Hi
It is an interesting pattern... I'll open a tracking item to see if we can
relax the check for the next release.
Thanks for the feedback.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:D67E5CBA-C13E-4272-A100-B613BD7F4E82@.microsoft.com...[vbcol=seagreen]
> Thanks t, detach and attach worked great. Do you have any idea about the
> NOCOUNT problem?
> jez
> "t" wrote:
database[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
is[vbcol=seagreen]
to[vbcol=seagreen]
it's at[vbcol=seagreen]
Best Practice Analyzer doesnt like my cursor
I'm running the Best Practice Analyzer on my sql 2k server and one of many
issues is about a bunch of cursors. The issue is for this cursor in an SP.
Actually there are the same 2 issues listed for a lot of SPs they are:
This cursor is declared as updatable (either explicitly or implicitly),
however it doesn't seem to be updated.
and
This cursor does not specify explicit updatability information (FOR UPDATE
clause or READ_ONLY clause).
However, I declared the cursor as read only so I don't understand why I
still get this Non-Compliance issue. Here's the cursor:
DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
SELECT VAL_ID
FROM #Tmp
OPEN curs_VAPairedComp_Insert
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
@.VAL_ID,
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR 44444 'Could not update or insert.'
ROLLBACK TRANSACTION
RETURN
END
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
END
CLOSE curs_VAPairedComp_Insert
DEALLOCATE curs_VAPairedComp_Insert
Thanks!
moondaddy@.nospam.nospamI don't like your cursor, either - no need for it :)
You should lose the cursors altogether instead of fixing them to BPA
compliance.
What possible errors could happen? FK errors? Don't know where the
transaction is, but since you're rolling back all the inserts, this
should work just fine in a set-based, not row-based, fashion.
assuming that: @.VP_ID and @.UserID are already determined elsewhere in
the code, and that the transaction has already begun. if the transaction
only includes this insert, then the rollback is unnecessary, as the
whole insert will fail.
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
VAL_ID
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
from
#Tmp
IF @.@.ERROR<>0
BEGIN
RAISERROR...
ROLLBACK TRANSACTION
END
moondaddy wrote:
> I'm running the Best Practice Analyzer on my sql 2k server and one of many
> issues is about a bunch of cursors. The issue is for this cursor in an SP
.
> Actually there are the same 2 issues listed for a lot of SPs they are:
> This cursor is declared as updatable (either explicitly or implicitly),
> however it doesn't seem to be updated.
> and
> This cursor does not specify explicit updatability information (FOR UPDATE
> clause or READ_ONLY clause).
> However, I declared the cursor as read only so I don't understand why I
> still get this Non-Compliance issue. Here's the cursor:
>
> DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
> SELECT VAL_ID
> FROM #Tmp
> OPEN curs_VAPairedComp_Insert
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> @.VAL_ID,
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR 44444 'Could not update or insert.'
> ROLLBACK TRANSACTION
> RETURN
> END
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> END
> CLOSE curs_VAPairedComp_Insert
> DEALLOCATE curs_VAPairedComp_Insert
>
> Thanks!
>|||OK point made about being set-based, however, it only rolls back if there is
an error. Also, the real issue is WHY is this getting picked up in the
first place by the Analyzer? There are numerous other cursors that are
getting picked up also, but they are different from this one and are needed.
moondaddy@.nospam.nospam
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uxBN7WI7FHA.3276@.TK2MSFTNGP15.phx.gbl...
>I don't like your cursor, either - no need for it :)
> You should lose the cursors altogether instead of fixing them to BPA
> compliance.
> What possible errors could happen? FK errors? Don't know where the
> transaction is, but since you're rolling back all the inserts, this should
> work just fine in a set-based, not row-based, fashion.
> assuming that: @.VP_ID and @.UserID are already determined elsewhere in the
> code, and that the transaction has already begun. if the transaction only
> includes this insert, then the rollback is unnecessary, as the whole
> insert will fail.
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> VAL_ID
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> from
> #Tmp
> IF @.@.ERROR<>0
> BEGIN
> RAISERROR...
> ROLLBACK TRANSACTION
> END
>
> moondaddy wrote:|||it's probably just that there insert/update/delete statement(s) inside
the cursor(s) in question [BPA does say "based on apparent usage"]
moondaddy wrote:
> OK point made about being set-based, however, it only rolls back if there
is
> an error. Also, the real issue is WHY is this getting picked up in the
> first place by the Analyzer? There are numerous other cursors that are
> getting picked up also, but they are different from this one and are neede
d.
>
issues is about a bunch of cursors. The issue is for this cursor in an SP.
Actually there are the same 2 issues listed for a lot of SPs they are:
This cursor is declared as updatable (either explicitly or implicitly),
however it doesn't seem to be updated.
and
This cursor does not specify explicit updatability information (FOR UPDATE
clause or READ_ONLY clause).
However, I declared the cursor as read only so I don't understand why I
still get this Non-Compliance issue. Here's the cursor:
DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
SELECT VAL_ID
FROM #Tmp
OPEN curs_VAPairedComp_Insert
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
@.VAL_ID,
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR 44444 'Could not update or insert.'
ROLLBACK TRANSACTION
RETURN
END
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
END
CLOSE curs_VAPairedComp_Insert
DEALLOCATE curs_VAPairedComp_Insert
Thanks!
moondaddy@.nospam.nospamI don't like your cursor, either - no need for it :)
You should lose the cursors altogether instead of fixing them to BPA
compliance.
What possible errors could happen? FK errors? Don't know where the
transaction is, but since you're rolling back all the inserts, this
should work just fine in a set-based, not row-based, fashion.
assuming that: @.VP_ID and @.UserID are already determined elsewhere in
the code, and that the transaction has already begun. if the transaction
only includes this insert, then the rollback is unnecessary, as the
whole insert will fail.
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
VAL_ID
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
from
#Tmp
IF @.@.ERROR<>0
BEGIN
RAISERROR...
ROLLBACK TRANSACTION
END
moondaddy wrote:
> I'm running the Best Practice Analyzer on my sql 2k server and one of many
> issues is about a bunch of cursors. The issue is for this cursor in an SP
.
> Actually there are the same 2 issues listed for a lot of SPs they are:
> This cursor is declared as updatable (either explicitly or implicitly),
> however it doesn't seem to be updated.
> and
> This cursor does not specify explicit updatability information (FOR UPDATE
> clause or READ_ONLY clause).
> However, I declared the cursor as read only so I don't understand why I
> still get this Non-Compliance issue. Here's the cursor:
>
> DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
> SELECT VAL_ID
> FROM #Tmp
> OPEN curs_VAPairedComp_Insert
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> @.VAL_ID,
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR 44444 'Could not update or insert.'
> ROLLBACK TRANSACTION
> RETURN
> END
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> END
> CLOSE curs_VAPairedComp_Insert
> DEALLOCATE curs_VAPairedComp_Insert
>
> Thanks!
>|||OK point made about being set-based, however, it only rolls back if there is
an error. Also, the real issue is WHY is this getting picked up in the
first place by the Analyzer? There are numerous other cursors that are
getting picked up also, but they are different from this one and are needed.
moondaddy@.nospam.nospam
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uxBN7WI7FHA.3276@.TK2MSFTNGP15.phx.gbl...
>I don't like your cursor, either - no need for it :)
> You should lose the cursors altogether instead of fixing them to BPA
> compliance.
> What possible errors could happen? FK errors? Don't know where the
> transaction is, but since you're rolling back all the inserts, this should
> work just fine in a set-based, not row-based, fashion.
> assuming that: @.VP_ID and @.UserID are already determined elsewhere in the
> code, and that the transaction has already begun. if the transaction only
> includes this insert, then the rollback is unnecessary, as the whole
> insert will fail.
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> VAL_ID
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> from
> #Tmp
> IF @.@.ERROR<>0
> BEGIN
> RAISERROR...
> ROLLBACK TRANSACTION
> END
>
> moondaddy wrote:|||it's probably just that there insert/update/delete statement(s) inside
the cursor(s) in question [BPA does say "based on apparent usage"]
moondaddy wrote:
> OK point made about being set-based, however, it only rolls back if there
is
> an error. Also, the real issue is WHY is this getting picked up in the
> first place by the Analyzer? There are numerous other cursors that are
> getting picked up also, but they are different from this one and are neede
d.
>
Wednesday, March 7, 2012
Best Performance Strip setting RAID
We are running SQL6.5 and plan to upgrade to SQL2000. I'm wondering what's the best stripe size for the RAID 5 configuration. 8,16,32 or 64 kb.
The database is 90% used for read actions. Only during night complete refill of data and write actions only for statistics. Any advise is welcome on this subjectSince SQL server pages are 64K the raid stripe settings must also be set to 64K|||SQL 6.5 uses 2k pages and SQL 2000 uses 8k pages.
The stripe size of your RAID drives does NOT have to follow the page size, however I would use a RAID stripe size >= to my page size.
The database is 90% used for read actions. Only during night complete refill of data and write actions only for statistics. Any advise is welcome on this subjectSince SQL server pages are 64K the raid stripe settings must also be set to 64K|||SQL 6.5 uses 2k pages and SQL 2000 uses 8k pages.
The stripe size of your RAID drives does NOT have to follow the page size, however I would use a RAID stripe size >= to my page size.
Best Performance
We're looking to upgrade our SQL Server - We have two options - A dual
processor machine running Enterprise edition, or a quad processor machine
running standard edition.
The question I'd like to ask is which is likely to be the faster - Obviously
Enterprise edition will allow us to have more memory and should therefore
hopefully cut down the number of recompiles, but having four processors...
Help !!!
AndyFYI: There can be many reasons for a recompile. But you must be using
really many stored procedures and very little memory. SQL-Server will
make the tradeoff how to use the available memory and it knows a
recompilation is expensive. Because of that, there will be few
recompiles because of memory constraints.
Gert-Jan
Andy Reilly wrote:
I don't have enough info to say with certainty... no one does...
but generally speaking... I would be SHOCKED if the 4X Standard box didn't
perform better than the 2X Enterprise. Enterprise can support more mem,
but... the Standard limits are pretty darn good. IF you can get adequte
performance on a 2X box... that almost implies to me that your app and data
set isn't bing enough to worry about going beyond 2G anyway...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy Reilly" <ReillyA@.ThomasNoSpam-Sanderson.co.uk> wrote in message
news:%23F4GHBC5DHA.2188@.TK2MSFTNGP10.phx.gbl...
Obviously
processors...
processor machine running Enterprise edition, or a quad processor machine
running standard edition.
The question I'd like to ask is which is likely to be the faster - Obviously
Enterprise edition will allow us to have more memory and should therefore
hopefully cut down the number of recompiles, but having four processors...
Help !!!
AndyFYI: There can be many reasons for a recompile. But you must be using
really many stored procedures and very little memory. SQL-Server will
make the tradeoff how to use the available memory and it knows a
recompilation is expensive. Because of that, there will be few
recompiles because of memory constraints.
Gert-Jan
Andy Reilly wrote:
quote:|||the corrent answer to almost any performance queestion is 'it depends'. <g>
> We're looking to upgrade our SQL Server - We have two options - A dual
> processor machine running Enterprise edition, or a quad processor machine
> running standard edition.
> The question I'd like to ask is which is likely to be the faster - Obvious
ly
> Enterprise edition will allow us to have more memory and should therefore
> hopefully cut down the number of recompiles, but having four processors...
.
> Help !!!
> Andy
I don't have enough info to say with certainty... no one does...
but generally speaking... I would be SHOCKED if the 4X Standard box didn't
perform better than the 2X Enterprise. Enterprise can support more mem,
but... the Standard limits are pretty darn good. IF you can get adequte
performance on a 2X box... that almost implies to me that your app and data
set isn't bing enough to worry about going beyond 2G anyway...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy Reilly" <ReillyA@.ThomasNoSpam-Sanderson.co.uk> wrote in message
news:%23F4GHBC5DHA.2188@.TK2MSFTNGP10.phx.gbl...
quote:
> We're looking to upgrade our SQL Server - We have two options - A dual
> processor machine running Enterprise edition, or a quad processor machine
> running standard edition.
> The question I'd like to ask is which is likely to be the faster -
Obviously
quote:
> Enterprise edition will allow us to have more memory and should therefore
> hopefully cut down the number of recompiles, but having four
processors...
quote:
> Help !!!
> Andy
>
Best Performance
We're looking to upgrade our SQL Server - We have two options - A dual
processor machine running Enterprise edition, or a quad processor machine
running standard edition.
The question I'd like to ask is which is likely to be the faster - Obviously
Enterprise edition will allow us to have more memory and should therefore
hopefully cut down the number of recompiles, but having four processors...
Help !!!
AndyEnterprise is not necessarily faster than standard.
Check the following for further details :-
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/evaluate/choose.asp
If you think about it for a moment the speed of any
application is going to as fast as the slowest component,
which is normally (in order of slowest) Network, Hard
Disk, then CPU.
So before getting your CPU's sorted you should have a look
at the other two.
If you would like further details then feel free to email
me at little_flowery_me<REMOVETHIS>.@.hotmail.com
In the meanwhile have a quick look at this...
http://www.tpc.org/
J
>--Original Message--
>We're looking to upgrade our SQL Server - We have two
options - A dual
>processor machine running Enterprise edition, or a quad
processor machine
>running standard edition.
>The question I'd like to ask is which is likely to be the
faster - Obviously
>Enterprise edition will allow us to have more memory and
should therefore
>hopefully cut down the number of recompiles, but having
four processors...
>Help !!!
>Andy
>
>.
>|||FYI: There can be many reasons for a recompile. But you must be using
really many stored procedures and very little memory. SQL-Server will
make the tradeoff how to use the available memory and it knows a
recompilation is expensive. Because of that, there will be few
recompiles because of memory constraints.
Gert-Jan
Andy Reilly wrote:
> We're looking to upgrade our SQL Server - We have two options - A dual
> processor machine running Enterprise edition, or a quad processor machine
> running standard edition.
> The question I'd like to ask is which is likely to be the faster - Obviously
> Enterprise edition will allow us to have more memory and should therefore
> hopefully cut down the number of recompiles, but having four processors...
> Help !!!
> Andy|||the corrent answer to almost any performance queestion is 'it depends'. <g>
I don't have enough info to say with certainty... no one does...
but generally speaking... I would be SHOCKED if the 4X Standard box didn't
perform better than the 2X Enterprise. Enterprise can support more mem,
but... the Standard limits are pretty darn good. IF you can get adequte
performance on a 2X box... that almost implies to me that your app and data
set isn't bing enough to worry about going beyond 2G anyway...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy Reilly" <ReillyA@.ThomasNoSpam-Sanderson.co.uk> wrote in message
news:%23F4GHBC5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> We're looking to upgrade our SQL Server - We have two options - A dual
> processor machine running Enterprise edition, or a quad processor machine
> running standard edition.
> The question I'd like to ask is which is likely to be the faster -
Obviously
> Enterprise edition will allow us to have more memory and should therefore
> hopefully cut down the number of recompiles, but having four
processors...
> Help !!!
> Andy
>
processor machine running Enterprise edition, or a quad processor machine
running standard edition.
The question I'd like to ask is which is likely to be the faster - Obviously
Enterprise edition will allow us to have more memory and should therefore
hopefully cut down the number of recompiles, but having four processors...
Help !!!
AndyEnterprise is not necessarily faster than standard.
Check the following for further details :-
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/evaluate/choose.asp
If you think about it for a moment the speed of any
application is going to as fast as the slowest component,
which is normally (in order of slowest) Network, Hard
Disk, then CPU.
So before getting your CPU's sorted you should have a look
at the other two.
If you would like further details then feel free to email
me at little_flowery_me<REMOVETHIS>.@.hotmail.com
In the meanwhile have a quick look at this...
http://www.tpc.org/
J
>--Original Message--
>We're looking to upgrade our SQL Server - We have two
options - A dual
>processor machine running Enterprise edition, or a quad
processor machine
>running standard edition.
>The question I'd like to ask is which is likely to be the
faster - Obviously
>Enterprise edition will allow us to have more memory and
should therefore
>hopefully cut down the number of recompiles, but having
four processors...
>Help !!!
>Andy
>
>.
>|||FYI: There can be many reasons for a recompile. But you must be using
really many stored procedures and very little memory. SQL-Server will
make the tradeoff how to use the available memory and it knows a
recompilation is expensive. Because of that, there will be few
recompiles because of memory constraints.
Gert-Jan
Andy Reilly wrote:
> We're looking to upgrade our SQL Server - We have two options - A dual
> processor machine running Enterprise edition, or a quad processor machine
> running standard edition.
> The question I'd like to ask is which is likely to be the faster - Obviously
> Enterprise edition will allow us to have more memory and should therefore
> hopefully cut down the number of recompiles, but having four processors...
> Help !!!
> Andy|||the corrent answer to almost any performance queestion is 'it depends'. <g>
I don't have enough info to say with certainty... no one does...
but generally speaking... I would be SHOCKED if the 4X Standard box didn't
perform better than the 2X Enterprise. Enterprise can support more mem,
but... the Standard limits are pretty darn good. IF you can get adequte
performance on a 2X box... that almost implies to me that your app and data
set isn't bing enough to worry about going beyond 2G anyway...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy Reilly" <ReillyA@.ThomasNoSpam-Sanderson.co.uk> wrote in message
news:%23F4GHBC5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> We're looking to upgrade our SQL Server - We have two options - A dual
> processor machine running Enterprise edition, or a quad processor machine
> running standard edition.
> The question I'd like to ask is which is likely to be the faster -
Obviously
> Enterprise edition will allow us to have more memory and should therefore
> hopefully cut down the number of recompiles, but having four
processors...
> Help !!!
> Andy
>
Best method for running several queries?
I have an SQL file saved from QA. It has several queries used for testing.
These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
specific statement to run in that file. This keeps everything from running
at once.
Problem is that I access the server from several computers via QA. The SQL
file with all of the above queries is usually on one computer. Should I
just store the SQL file on the SQL Server machine as an SQL file or a stored
procedure? What is best for this? This file isn't something I would ever
want an app to have access to. It's strictly for manual testing purposes
via QA.
Thanks,
BrettScript files are considered source code. So, you would want to put it in a
souce control server somewhere and just grab it when you need it. Storing
the script inside sqlserver is probably not a good idea in this case.
-oj
"Brett" <no@.spam.net> wrote in message
news:ORw4XLLLFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I have an SQL file saved from QA. It has several queries used for testing.
>These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
>specific statement to run in that file. This keeps everything from running
>at once.
> Problem is that I access the server from several computers via QA. The
> SQL file with all of the above queries is usually on one computer. Should
> I just store the SQL file on the SQL Server machine as an SQL file or a
> stored procedure? What is best for this? This file isn't something I
> would ever want an app to have access to. It's strictly for manual
> testing purposes via QA.
> Thanks,
> Brett
>
These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
specific statement to run in that file. This keeps everything from running
at once.
Problem is that I access the server from several computers via QA. The SQL
file with all of the above queries is usually on one computer. Should I
just store the SQL file on the SQL Server machine as an SQL file or a stored
procedure? What is best for this? This file isn't something I would ever
want an app to have access to. It's strictly for manual testing purposes
via QA.
Thanks,
BrettScript files are considered source code. So, you would want to put it in a
souce control server somewhere and just grab it when you need it. Storing
the script inside sqlserver is probably not a good idea in this case.
-oj
"Brett" <no@.spam.net> wrote in message
news:ORw4XLLLFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I have an SQL file saved from QA. It has several queries used for testing.
>These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
>specific statement to run in that file. This keeps everything from running
>at once.
> Problem is that I access the server from several computers via QA. The
> SQL file with all of the above queries is usually on one computer. Should
> I just store the SQL file on the SQL Server machine as an SQL file or a
> stored procedure? What is best for this? This file isn't something I
> would ever want an app to have access to. It's strictly for manual
> testing purposes via QA.
> Thanks,
> Brett
>
Saturday, February 25, 2012
Best hw recommendation for a sql server - server.
At this time, i have a server running sql server 2000 sp3 on a windows2000
server sp4. this server is running slow, so I need to recommend a new server
,
buy I don′t know how to estimate the best hw tha will help us.
Do some body know a tool that can help me to estimate the best hw for our
system requierements'
Thanks a lot for your help.> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I don′t know how to estimate the best hw tha will help us.
Why do you think the slowness is due to hardware problems?
The most monumental increases we have realized in the past performance over
the past year, were from:
(a) installing SQL Server 2000 SP4 (huge gain!)
(b) optimizing indexes, statistics and procedure code|||Have you already eliminated, deadlocking, poor indexing etc before deciding
to purchase new ...? If not throwing hardware at a problem will only be a
short term solution.
Find out the budget and work back from there...
HTH. Ryan
"Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I dont know how to estimate the best hw tha will help us.
> Do some body know a tool that can help me to estimate the best hw for our
> system requierements'
> Thanks a lot for your help.|||Have you used Performance Monitor to confirm that the bottleneck is your
server? Replacing the hardware may seem like a quick fix compared to
analyzing and re-programming the application, but if you shell out the
$$,$$$ and the problem is still not solved, then you end up looking really
bad.
Performance Monitor:
http://www.sql-server-performance.c...&seqNum=28&rl=1
How to Perform a SQL Server Performance Audit
http://www.sql-server-performance.c...mance_audit.asp
Checklist: SQL Server Performance
http://msdn.microsoft.com/library/d...
etcheck08.asp
There may still be an easy fix related to resolving deadlocks, logical or
disk defragmenting, or simply adding a crucially needed index.
http://support.microsoft.com/defaul...kb;en-us;832524
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
How To: Optimize SQL Indexes
http://msdn.microsoft.com/library/d...
etHowTo03.asp
"Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I dont know how to estimate the best hw tha will help us.
> Do some body know a tool that can help me to estimate the best hw for our
> system requierements'
> Thanks a lot for your help.|||Thanks a lot for your recommendatio. I applyed some of them. The other thing
that I need help to estimate the best hw for a sql server is that here in th
e
office they want to change the server because they want to implement a
Cluster. So I have to recommend a new hw requirements.
Do you know any formula that can help to to estimate that' or can you tell
me what issue I need to consider to analyze that.
Thanks a lot for your help.
"JT" wrote:
> Have you used Performance Monitor to confirm that the bottleneck is your
> server? Replacing the hardware may seem like a quick fix compared to
> analyzing and re-programming the application, but if you shell out the
> $$,$$$ and the problem is still not solved, then you end up looking really
> bad.
> Performance Monitor:
> http://www.sql-server-performance.c...ver.as
p
> Monitoring - Performance Monitor
> http://www.informit.com/guides/cont...&seqNum=28&rl=1
> How to Perform a SQL Server Performance Audit
> http://www.sql-server-performance.c...mance_audit.asp
> Checklist: SQL Server Performance
> http://msdn.microsoft.com/library/d...enetcheck08.asp
> There may still be an easy fix related to resolving deadlocks, logical or
> disk defragmenting, or simply adding a crucially needed index.
> http://support.microsoft.com/defaul...kb;en-us;832524
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> http://www.microsoft.com/technet/pr...eNetHowTo03.asp
> "Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
> news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
>
>
server sp4. this server is running slow, so I need to recommend a new server
,
buy I don′t know how to estimate the best hw tha will help us.
Do some body know a tool that can help me to estimate the best hw for our
system requierements'
Thanks a lot for your help.> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I don′t know how to estimate the best hw tha will help us.
Why do you think the slowness is due to hardware problems?
The most monumental increases we have realized in the past performance over
the past year, were from:
(a) installing SQL Server 2000 SP4 (huge gain!)
(b) optimizing indexes, statistics and procedure code|||Have you already eliminated, deadlocking, poor indexing etc before deciding
to purchase new ...? If not throwing hardware at a problem will only be a
short term solution.
Find out the budget and work back from there...
HTH. Ryan
"Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I dont know how to estimate the best hw tha will help us.
> Do some body know a tool that can help me to estimate the best hw for our
> system requierements'
> Thanks a lot for your help.|||Have you used Performance Monitor to confirm that the bottleneck is your
server? Replacing the hardware may seem like a quick fix compared to
analyzing and re-programming the application, but if you shell out the
$$,$$$ and the problem is still not solved, then you end up looking really
bad.
Performance Monitor:
http://www.sql-server-performance.c...&seqNum=28&rl=1
How to Perform a SQL Server Performance Audit
http://www.sql-server-performance.c...mance_audit.asp
Checklist: SQL Server Performance
http://msdn.microsoft.com/library/d...
etcheck08.asp
There may still be an easy fix related to resolving deadlocks, logical or
disk defragmenting, or simply adding a crucially needed index.
http://support.microsoft.com/defaul...kb;en-us;832524
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
How To: Optimize SQL Indexes
http://msdn.microsoft.com/library/d...
etHowTo03.asp
"Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I dont know how to estimate the best hw tha will help us.
> Do some body know a tool that can help me to estimate the best hw for our
> system requierements'
> Thanks a lot for your help.|||Thanks a lot for your recommendatio. I applyed some of them. The other thing
that I need help to estimate the best hw for a sql server is that here in th
e
office they want to change the server because they want to implement a
Cluster. So I have to recommend a new hw requirements.
Do you know any formula that can help to to estimate that' or can you tell
me what issue I need to consider to analyze that.
Thanks a lot for your help.
"JT" wrote:
> Have you used Performance Monitor to confirm that the bottleneck is your
> server? Replacing the hardware may seem like a quick fix compared to
> analyzing and re-programming the application, but if you shell out the
> $$,$$$ and the problem is still not solved, then you end up looking really
> bad.
> Performance Monitor:
> http://www.sql-server-performance.c...ver.as
p
> Monitoring - Performance Monitor
> http://www.informit.com/guides/cont...&seqNum=28&rl=1
> How to Perform a SQL Server Performance Audit
> http://www.sql-server-performance.c...mance_audit.asp
> Checklist: SQL Server Performance
> http://msdn.microsoft.com/library/d...enetcheck08.asp
> There may still be an easy fix related to resolving deadlocks, logical or
> disk defragmenting, or simply adding a crucially needed index.
> http://support.microsoft.com/defaul...kb;en-us;832524
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> http://www.microsoft.com/technet/pr...eNetHowTo03.asp
> "Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
> news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
>
>
Best configuration for SQL Server 2005
Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx --Per
formance
2005
http://www.microsoft.com/technet/pr...ce/default.mspx -
--Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You ca
n
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx --Per
formance
2005
http://www.microsoft.com/technet/pr...ce/default.mspx -
--Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You ca
n
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
Best configuration for SQL Server 2005
Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
>> 2) Put tempdb database on different phsyical disk
>> 3) Add much more RAM as you can and as it allowed by OS
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
>> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
>> Practices 2005
>> <lorenzdomin...@.hotmail.com> wrote in message
>> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>>
>> > Hi
>> > I am having a little trouble finding what is the ideal configuration
>> > for running SQL Server 2005.
>> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
>> > Operating System will be Windows 2003 Standard Server R2
>> > I can have any number of hard drives for either a Raid 1 or Raid 5
>> > configuration or whatever, any comments appreciated?
>> > How should the Hard Disk Drives be set up for a 10Gb Database?
>> > Thanks
>> > Dominic- Hide quoted text -
>> - Show quoted text -
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
--
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
>> 2) Put tempdb database on different phsyical disk
>> 3) Add much more RAM as you can and as it allowed by OS
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
>> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
>> Practices 2005
>> <lorenzdomin...@.hotmail.com> wrote in message
>> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>>
>> > Hi
>> > I am having a little trouble finding what is the ideal configuration
>> > for running SQL Server 2005.
>> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
>> > Operating System will be Windows 2003 Standard Server R2
>> > I can have any number of hard drives for either a Raid 1 or Raid 5
>> > configuration or whatever, any comments appreciated?
>> > How should the Hard Disk Drives be set up for a 10Gb Database?
>> > Thanks
>> > Dominic- Hide quoted text -
>> - Show quoted text -
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
--
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
Best configuration for SQL Server 2005
Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
Dominic
Hi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic
|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
..MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegro ups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>
|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
Dominic
Hi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic
|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
..MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegro ups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>
|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
Friday, February 24, 2012
Best Configuration for a 3 Node SQL 2000 Cluster on Windows 2003?
Ok, I've got the cluster setup and running, but having never done this,
I'm not sure if I'm setting up SQL right... We're trying to migrate
our multitude of SQL Server running on older hardware to the new
cluster, but I want to make sure we don't shoot ourselves in the foot.
Here is what we've got:
Specs:
3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
Current Setup:
Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
is about 1.2TB left on the controller for additional space.
Followed all the instructions, Public IPs, Private IPs, etc...
Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
Virtual Servers (Same name, actual server name is longer and unique),
and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
SQLCL03 is the failover server which of course is identical to the
first two. We're never expecting to have 2 fail, but we may add a 4th
Server/Node in the future (we have 5 additional slots in our two Blade
Chassis).
>From what I've read you "can" install up to 16 instances into a single
cluster, but obviously I don't know if #1 I should try and install more
than 2 instances, or #2 if I even can. I've tried rerunning the SQL
Setup just to see, and all it lets me do it "modify" the current
install or remove it, it won't let me add another instance.
So... What am I looking at here? Is this the optimum configuration
for now, or can I do more? What about memory? Should I limit each SQL
Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
incase both the two main servers ever fail and everything gets forced
to the 3rd? These servers will pretty much only be used for SQL,
nothing else, so there isnt' too much worry about applications battling
for memory.
Thanks in advance. I know some of these questions may seem rather
newbish, but I've installed and administered SQL2k before, but never a
cluster... so this is new ground for me and the documentation out
there is not very helpful, most of it refers to SQL2k on Windows 2000,
not Windows Server 2003.
Jon Casimir
Lotsa comments inline.
<kazsmir@.gmail.com> wrote in message
news:1125515891.775160.31810@.z14g2000cwz.googlegro ups.com...
> Ok, I've got the cluster setup and running, but having never done this,
> I'm not sure if I'm setting up SQL right... We're trying to migrate
> our multitude of SQL Server running on older hardware to the new
> cluster, but I want to make sure we don't shoot ourselves in the foot.
> Here is what we've got:
> Specs:
> 3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
> 1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
> Current Setup:
> Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
> MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
> Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
> is about 1.2TB left on the controller for additional space.
>
I am not a big fan of blade servers as cluster nodes. Too many single
failure points for what is intended to be a highly available system.
A well-tuned, dedicated SQL server should have minimal need for a paging
file. If you are paging heavily, you have something tuned wrong.
Backups should never be stored on the same host computer or storage array as
the primary data store, even if they are on separate physical disks. Backup
across the net to a file share for immediate use and archive those files to
tape for longer retention periods.
> Followed all the instructions, Public IPs, Private IPs, etc...
> Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
> Virtual Servers (Same name, actual server name is longer and unique),
> and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
> SQLCL03 is the failover server which of course is identical to the
> first two. We're never expecting to have 2 fail, but we may add a 4th
> Server/Node in the future (we have 5 additional slots in our two Blade
> Chassis).
This doesn't sound right. You should have each Virtual Server\Instance
combination installed on all nodes on the cluster so you can fail over as
needed. During install time you can select which cluster nodes to install
SQL to. You can set the preferred node order of each Virtual Server later
independently so they start and fail where you choose.
On a multi-node, multi-instance cluster, I usually only worry about
first-order failures. If I have more than one instance go south on me, it
is usually the entire cluster that bombs. If I have one instance with a
problem, somebody competent better be standing in front of it fixing the
problem within 30 minutes. You can adjust memory settings and failover
order at that time.
> cluster, but obviously I don't know if #1 I should try and install more
> than 2 instances, or #2 if I even can. I've tried rerunning the SQL
> Setup just to see, and all it lets me do it "modify" the current
> install or remove it, it won't let me add another instance.
>
SQL won't let you add a new virtual server unless there is at least one
unassigned cluster disk resource to anchor the instance. Whether you
"should" install more instances is another matter. Each instance looks and
acts like a separate server on the network. Generally, multiple instances
in a cluster are used to manage security and performance. In my history, I
find that one instance per node + one spare is an optimal configuration, but
your needs with this server consolidation project may vary.
> So... What am I looking at here? Is this the optimum configuration
> for now, or can I do more? What about memory? Should I limit each SQL
> Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
> incase both the two main servers ever fail and everything gets forced
> to the 3rd? These servers will pretty much only be used for SQL,
> nothing else, so there isnt' too much worry about applications battling
> for memory.
Since you have paid for Enterprise Edition anyway you should max out the
memory, although your choice of blade servers as hosts may limit that
expansion capability. You will need to consider what happens during a
failover so that you can tolerate "stacking" multiple instances on the same
nost node.
> Thanks in advance. I know some of these questions may seem rather
> newbish, but I've installed and administered SQL2k before, but never a
> cluster... so this is new ground for me and the documentation out
> there is not very helpful, most of it refers to SQL2k on Windows 2000,
> not Windows Server 2003.
Most of the considerations for Windows 2000 clustering apply to Windows
2003, except for some installation gotchas. Unless you are sure something
from Windows 2000 doesn't apply, assume it does.
Now is the best time to ask "dumb" questions. Later, when your "highly
available" database solution that you bet your job on is down is the worst
time.
> Jon Casimir
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
I'm not sure if I'm setting up SQL right... We're trying to migrate
our multitude of SQL Server running on older hardware to the new
cluster, but I want to make sure we don't shoot ourselves in the foot.
Here is what we've got:
Specs:
3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
Current Setup:
Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
is about 1.2TB left on the controller for additional space.
Followed all the instructions, Public IPs, Private IPs, etc...
Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
Virtual Servers (Same name, actual server name is longer and unique),
and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
SQLCL03 is the failover server which of course is identical to the
first two. We're never expecting to have 2 fail, but we may add a 4th
Server/Node in the future (we have 5 additional slots in our two Blade
Chassis).
>From what I've read you "can" install up to 16 instances into a single
cluster, but obviously I don't know if #1 I should try and install more
than 2 instances, or #2 if I even can. I've tried rerunning the SQL
Setup just to see, and all it lets me do it "modify" the current
install or remove it, it won't let me add another instance.
So... What am I looking at here? Is this the optimum configuration
for now, or can I do more? What about memory? Should I limit each SQL
Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
incase both the two main servers ever fail and everything gets forced
to the 3rd? These servers will pretty much only be used for SQL,
nothing else, so there isnt' too much worry about applications battling
for memory.
Thanks in advance. I know some of these questions may seem rather
newbish, but I've installed and administered SQL2k before, but never a
cluster... so this is new ground for me and the documentation out
there is not very helpful, most of it refers to SQL2k on Windows 2000,
not Windows Server 2003.
Jon Casimir
Lotsa comments inline.
<kazsmir@.gmail.com> wrote in message
news:1125515891.775160.31810@.z14g2000cwz.googlegro ups.com...
> Ok, I've got the cluster setup and running, but having never done this,
> I'm not sure if I'm setting up SQL right... We're trying to migrate
> our multitude of SQL Server running on older hardware to the new
> cluster, but I want to make sure we don't shoot ourselves in the foot.
> Here is what we've got:
> Specs:
> 3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
> 1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
> Current Setup:
> Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
> MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
> Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
> is about 1.2TB left on the controller for additional space.
>
I am not a big fan of blade servers as cluster nodes. Too many single
failure points for what is intended to be a highly available system.
A well-tuned, dedicated SQL server should have minimal need for a paging
file. If you are paging heavily, you have something tuned wrong.
Backups should never be stored on the same host computer or storage array as
the primary data store, even if they are on separate physical disks. Backup
across the net to a file share for immediate use and archive those files to
tape for longer retention periods.
> Followed all the instructions, Public IPs, Private IPs, etc...
> Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
> Virtual Servers (Same name, actual server name is longer and unique),
> and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
> SQLCL03 is the failover server which of course is identical to the
> first two. We're never expecting to have 2 fail, but we may add a 4th
> Server/Node in the future (we have 5 additional slots in our two Blade
> Chassis).
This doesn't sound right. You should have each Virtual Server\Instance
combination installed on all nodes on the cluster so you can fail over as
needed. During install time you can select which cluster nodes to install
SQL to. You can set the preferred node order of each Virtual Server later
independently so they start and fail where you choose.
On a multi-node, multi-instance cluster, I usually only worry about
first-order failures. If I have more than one instance go south on me, it
is usually the entire cluster that bombs. If I have one instance with a
problem, somebody competent better be standing in front of it fixing the
problem within 30 minutes. You can adjust memory settings and failover
order at that time.
> cluster, but obviously I don't know if #1 I should try and install more
> than 2 instances, or #2 if I even can. I've tried rerunning the SQL
> Setup just to see, and all it lets me do it "modify" the current
> install or remove it, it won't let me add another instance.
>
SQL won't let you add a new virtual server unless there is at least one
unassigned cluster disk resource to anchor the instance. Whether you
"should" install more instances is another matter. Each instance looks and
acts like a separate server on the network. Generally, multiple instances
in a cluster are used to manage security and performance. In my history, I
find that one instance per node + one spare is an optimal configuration, but
your needs with this server consolidation project may vary.
> So... What am I looking at here? Is this the optimum configuration
> for now, or can I do more? What about memory? Should I limit each SQL
> Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
> incase both the two main servers ever fail and everything gets forced
> to the 3rd? These servers will pretty much only be used for SQL,
> nothing else, so there isnt' too much worry about applications battling
> for memory.
Since you have paid for Enterprise Edition anyway you should max out the
memory, although your choice of blade servers as hosts may limit that
expansion capability. You will need to consider what happens during a
failover so that you can tolerate "stacking" multiple instances on the same
nost node.
> Thanks in advance. I know some of these questions may seem rather
> newbish, but I've installed and administered SQL2k before, but never a
> cluster... so this is new ground for me and the documentation out
> there is not very helpful, most of it refers to SQL2k on Windows 2000,
> not Windows Server 2003.
Most of the considerations for Windows 2000 clustering apply to Windows
2003, except for some installation gotchas. Unless you are sure something
from Windows 2000 doesn't apply, assume it does.
Now is the best time to ask "dumb" questions. Later, when your "highly
available" database solution that you bet your job on is down is the worst
time.
> Jon Casimir
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
best book on complex DTS packages?
I already know how to use the basics for setting up and running DTS packages in SQL Server 2000. Does anyone have a good recommendation for book(s) that cover advanced DTS topics such as using command line scripts, ETL and so forth to work with complex DTS topics? ThanksThe only one I know about and own is Professional DTS by Wrox books, it was written by guys at Alltel and Encore development which is a BI company.
HTH
BTW: I do reccomend it!
HTH
BTW: I do reccomend it!
Best backup plan for 24*7 running production database
Hi:
ENVIRONMENT:WIN2000/SQL2000
We need best backup plan for one our 24*7 available
production database growing by 1.2 gb per day..
Any one can tell me the best plan or can recommend any
best site for this?
Thanks in advance
PuruPuru
For large databases, which yours soon will be, most people
would take advantage of differential backups to reduce
impact on the system.
Something like a full backup once a week, and
differentials the other days and regular transaction log
backups as required.
Early in the life of your database you may find towards
the end of the week your differentials are nearly a large
and take as much time as a full backup, due to volume of
changes, as the database gets bigger that will change.
Try to put your backups on seperate disks to your database
and transaction logs to reduce the impact of them running.
Try to schedule them for the quietest time of the day,
even with a 24 by 7 installation you will still have some
times that are quieter than others. (usually when peak
business hours are in the paciffic ocean).
If your database gets into the terra-bytes you may need to
come up with a different strategy.
Hope this helps
Regards
John
ENVIRONMENT:WIN2000/SQL2000
We need best backup plan for one our 24*7 available
production database growing by 1.2 gb per day..
Any one can tell me the best plan or can recommend any
best site for this?
Thanks in advance
PuruPuru
For large databases, which yours soon will be, most people
would take advantage of differential backups to reduce
impact on the system.
Something like a full backup once a week, and
differentials the other days and regular transaction log
backups as required.
Early in the life of your database you may find towards
the end of the week your differentials are nearly a large
and take as much time as a full backup, due to volume of
changes, as the database gets bigger that will change.
Try to put your backups on seperate disks to your database
and transaction logs to reduce the impact of them running.
Try to schedule them for the quietest time of the day,
even with a 24 by 7 installation you will still have some
times that are quieter than others. (usually when peak
business hours are in the paciffic ocean).
If your database gets into the terra-bytes you may need to
come up with a different strategy.
Hope this helps
Regards
John
Subscribe to:
Comments (Atom)