Does anyone have any techniques to be able to display what environment a
server might be part of (e.g. Production, UAT, Development etc).
Instance names already follow a standard naming convention (non descriptive)
so that is not an option.
My ideal solution would be to have a description appear for the server
registration in SSMS or Entman.
e.g.
server1 (SQL Server 8.0.878 - dom1\myuser)
would become...
server1 (SQL Server 8.0.878 PRODUCTION - dom1\myuser)
-
What metadata/ registry entries would I need to change to get this. Would it
cause issues (such as service pack upgrade problems). I would prefer not to
have to resort to putting this in a db/ table since I want the information
visible without having to issue a query.
Many thanks,
Mark.Hi
How does it know whether SQL Server uses PRODUCTION ir DEVELOPMENT?
http://dimantdatabasesolutions.blog...er.ht
ml
"news.microsoft.com" <nospam@.nospam.com> wrote in message
news:OFMENhYNIHA.5140@.TK2MSFTNGP05.phx.gbl...
> Does anyone have any techniques to be able to display what environment a
> server might be part of (e.g. Production, UAT, Development etc).
> Instance names already follow a standard naming convention (non
> descriptive) so that is not an option.
> My ideal solution would be to have a description appear for the server
> registration in SSMS or Entman.
> e.g.
> server1 (SQL Server 8.0.878 - dom1\myuser)
> would become...
> server1 (SQL Server 8.0.878 PRODUCTION - dom1\myuser)
> -
> What metadata/ registry entries would I need to change to get this. Would
> it cause issues (such as service pack upgrade problems). I would prefer
> not to have to resort to putting this in a db/ table since I want the
> information visible without having to issue a query.
> Many thanks,
> Mark.
>|||> My ideal solution would be to have a description appear for the server
> registration in SSMS or Entman.
Both SSMS and Enterprise Manager allow you to to logically group servers.
You might consider creating different groups for PROD, UAT, etc. to
categorize your servers by environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"news.microsoft.com" <nospam@.nospam.com> wrote in message
news:OFMENhYNIHA.5140@.TK2MSFTNGP05.phx.gbl...
> Does anyone have any techniques to be able to display what environment a
> server might be part of (e.g. Production, UAT, Development etc).
> Instance names already follow a standard naming convention (non
> descriptive) so that is not an option.
> My ideal solution would be to have a description appear for the server
> registration in SSMS or Entman.
> e.g.
> server1 (SQL Server 8.0.878 - dom1\myuser)
> would become...
> server1 (SQL Server 8.0.878 PRODUCTION - dom1\myuser)
> -
> What metadata/ registry entries would I need to change to get this. Would
> it cause issues (such as service pack upgrade problems). I would prefer
> not to have to resort to putting this in a db/ table since I want the
> information visible without having to issue a query.
> Many thanks,
> Mark.
>|||those properties just tell me about the installation and are readonly.
I'm looking to add a custom property description that will be displayed at
the server registration.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUguZuZNIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Hi
> How does it know whether SQL Server uses PRODUCTION ir DEVELOPMENT?
> http://dimantdatabasesolutions.blog...er.
html
> "news.microsoft.com" <nospam@.nospam.com> wrote in message
> news:OFMENhYNIHA.5140@.TK2MSFTNGP05.phx.gbl...
>|||that would only help me (and I dont want to distribute anything).
what I need is for the logical server usage name to be displayed whenever a
developer registers a server.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5F7B5747-FBF7-44BA-9D5A-5387DCA323CC@.microsoft.com...
> Both SSMS and Enterprise Manager allow you to to logically group servers.
> You might consider creating different groups for PROD, UAT, etc. to
> categorize your servers by environment.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "news.microsoft.com" <nospam@.nospam.com> wrote in message
> news:OFMENhYNIHA.5140@.TK2MSFTNGP05.phx.gbl...
>|||> what I need is for the logical server usage name to be displayed whenever
> a developer registers a server.
I don't have suggestion other than those already mentioned. The Best
Practice is to use mnemonic server/instance names that include the role of
the server but you mentioned that's not an option.
Hope this helps.
Dan Guzman
SQL Server MVP
"news.microsoft.com" <nospam@.nospam.com> wrote in message
news:O9shrmlNIHA.5988@.TK2MSFTNGP02.phx.gbl...
> that would only help me (and I dont want to distribute anything).
> what I need is for the logical server usage name to be displayed whenever
> a developer registers a server.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5F7B5747-FBF7-44BA-9D5A-5387DCA323CC@.microsoft.com...
>|||Thanks anyway Dan, always good to know opinions on best practice though.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C9D2AAAF-D3F1-4293-97FC-951A096736F2@.microsoft.com...
> I don't have suggestion other than those already mentioned. The Best
> Practice is to use mnemonic server/instance names that include the role of
> the server but you mentioned that's not an option.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "news.microsoft.com" <nospam@.nospam.com> wrote in message
> news:O9shrmlNIHA.5988@.TK2MSFTNGP02.phx.gbl...
>
2012年3月29日星期四
Disks not appearing in "My Computer"
I have a clustered environment which includes 2 nodes. The cluster
fails across fine but the only issue is the disks do not appear in My
Computer. When I connect to the disks through Windows disk management
all the disks are there.
Any ideas?
Many thanks
The clustered drives which are called resources are held by the node which is
the active one. At any point of time, only one node can hold the resources.
"Edwin vMierlo [MVP]" wrote:
> Clustered disks are only accessible on the node where they are online.
> Rgds,
> Edwin.
>
> <Joe.Mobley@.nationalexpress.com> wrote in message
> news:1178015037.826526.300420@.h2g2000hsg.googlegro ups.com...
>
>
fails across fine but the only issue is the disks do not appear in My
Computer. When I connect to the disks through Windows disk management
all the disks are there.
Any ideas?
Many thanks
The clustered drives which are called resources are held by the node which is
the active one. At any point of time, only one node can hold the resources.
"Edwin vMierlo [MVP]" wrote:
> Clustered disks are only accessible on the node where they are online.
> Rgds,
> Edwin.
>
> <Joe.Mobley@.nationalexpress.com> wrote in message
> news:1178015037.826526.300420@.h2g2000hsg.googlegro ups.com...
>
>
Diskkeeper users ..
Anyone uses Diskkeeper in their production environment to defrag their files
? Any pros and cons that you are aware of ? Can you defrag the files while
the databases are active or do we need to do it when SQL is offline ?
Thanks
Hi
Yes, but not with SQL Server running.
Once it has been defragmented, and you manually grow the DBs to a decent
size so that autogrow does not kick, there is very little need to do i again.
Regards
Mike
"Hassan" wrote:
> Anyone uses Diskkeeper in their production environment to defrag their files
> ? Any pros and cons that you are aware of ? Can you defrag the files while
> the databases are active or do we need to do it when SQL is offline ?
> Thanks
>
>
? Any pros and cons that you are aware of ? Can you defrag the files while
the databases are active or do we need to do it when SQL is offline ?
Thanks
Hi
Yes, but not with SQL Server running.
Once it has been defragmented, and you manually grow the DBs to a decent
size so that autogrow does not kick, there is very little need to do i again.
Regards
Mike
"Hassan" wrote:
> Anyone uses Diskkeeper in their production environment to defrag their files
> ? Any pros and cons that you are aware of ? Can you defrag the files while
> the databases are active or do we need to do it when SQL is offline ?
> Thanks
>
>
Diskkeeper users ..
Anyone uses Diskkeeper in their production environment to defrag their files
? Any pros and cons that you are aware of ? Can you defrag the files while
the databases are active or do we need to do it when SQL is offline ?
ThanksHi
Yes, but not with SQL Server running.
Once it has been defragmented, and you manually grow the DBs to a decent
size so that autogrow does not kick, there is very little need to do i again
.
Regards
Mike
"Hassan" wrote:
> Anyone uses Diskkeeper in their production environment to defrag their fil
es
> ? Any pros and cons that you are aware of ? Can you defrag the files while
> the databases are active or do we need to do it when SQL is offline ?
> Thanks
>
>
? Any pros and cons that you are aware of ? Can you defrag the files while
the databases are active or do we need to do it when SQL is offline ?
ThanksHi
Yes, but not with SQL Server running.
Once it has been defragmented, and you manually grow the DBs to a decent
size so that autogrow does not kick, there is very little need to do i again
.
Regards
Mike
"Hassan" wrote:
> Anyone uses Diskkeeper in their production environment to defrag their fil
es
> ? Any pros and cons that you are aware of ? Can you defrag the files while
> the databases are active or do we need to do it when SQL is offline ?
> Thanks
>
>
Diskkeeper users ..
Anyone uses Diskkeeper in their production environment to defrag their files
? Any pros and cons that you are aware of ? Can you defrag the files while
the databases are active or do we need to do it when SQL is offline ?
ThanksHi
Yes, but not with SQL Server running.
Once it has been defragmented, and you manually grow the DBs to a decent
size so that autogrow does not kick, there is very little need to do i again.
Regards
Mike
"Hassan" wrote:
> Anyone uses Diskkeeper in their production environment to defrag their files
> ? Any pros and cons that you are aware of ? Can you defrag the files while
> the databases are active or do we need to do it when SQL is offline ?
> Thanks
>
>
? Any pros and cons that you are aware of ? Can you defrag the files while
the databases are active or do we need to do it when SQL is offline ?
ThanksHi
Yes, but not with SQL Server running.
Once it has been defragmented, and you manually grow the DBs to a decent
size so that autogrow does not kick, there is very little need to do i again.
Regards
Mike
"Hassan" wrote:
> Anyone uses Diskkeeper in their production environment to defrag their files
> ? Any pros and cons that you are aware of ? Can you defrag the files while
> the databases are active or do we need to do it when SQL is offline ?
> Thanks
>
>
Disk Subsystem Problem during snapshot
We have set up a new SQL environment that has to replace our current SQL
environment. On our current environment we apply a snapshot every night from
our production database (the production database is hosted on another
environment). This means every night approximately 15GB is being copied to
the subscriber. It takes a long time to do this (3+ hours), but is not a
problem in our environment.
Exactly the same snapshot has to be applied on our new SQL environment. In
this environment we are having serious problems with our Disk Subsystem when
applying the snapshot. In the new environment we use a shared HP MSA1000 Disk
System. When applying the snapshot, the load is so high on the MSA1000 that
it can’t handle all the requests no more. This results in a crash of SQL
server (SQL can’t find the tempdb anymore) and other systems that use the
MSA1000 (two more servers, so three in total) are having problems with the
disks also (event id 51).
We’ve contacted HP and Microsoft and the conclusion will probably be that
the load generated by applying the snapshot is to high for the MSA1000.
Microsoft’s explanation about how this can be, knowing that the exact same
process runs on an old environment, is very reasonable (although it’s still
hard to believe that old hardware operates better under high load than new
advanced hardware).
I’d like to know what the opinion from you all is about applying a snapshot
every night to a subscriber, which involves copying approximately 15GB of
data. Should it be no problem to this, or is it very uncommon to do this
because of hardware limitations? Is it normal that the MSA1000 has problems
of this kind with this, or should it be able to handle the load (should it
result in disks that cannot be contacted anymore or should it just perform
very slow)?
Your opinion is very much appreciated!
Kind regards,
Jan Martijn Schuur
"Martijn" schrieb:
...
> I’d like to know what the opinion from you all is about applying a snapshot
> every night to a subscriber, which involves copying approximately 15GB of
> data. Should it be no problem to this, or is it very uncommon to do this
> because of hardware limitations?
15 GB is a large amount of data for a nightly snapshot. Why don't you use
the incremental transactional replication?
Your other problems with this are not familiar to me. Sorry.
environment. On our current environment we apply a snapshot every night from
our production database (the production database is hosted on another
environment). This means every night approximately 15GB is being copied to
the subscriber. It takes a long time to do this (3+ hours), but is not a
problem in our environment.
Exactly the same snapshot has to be applied on our new SQL environment. In
this environment we are having serious problems with our Disk Subsystem when
applying the snapshot. In the new environment we use a shared HP MSA1000 Disk
System. When applying the snapshot, the load is so high on the MSA1000 that
it can’t handle all the requests no more. This results in a crash of SQL
server (SQL can’t find the tempdb anymore) and other systems that use the
MSA1000 (two more servers, so three in total) are having problems with the
disks also (event id 51).
We’ve contacted HP and Microsoft and the conclusion will probably be that
the load generated by applying the snapshot is to high for the MSA1000.
Microsoft’s explanation about how this can be, knowing that the exact same
process runs on an old environment, is very reasonable (although it’s still
hard to believe that old hardware operates better under high load than new
advanced hardware).
I’d like to know what the opinion from you all is about applying a snapshot
every night to a subscriber, which involves copying approximately 15GB of
data. Should it be no problem to this, or is it very uncommon to do this
because of hardware limitations? Is it normal that the MSA1000 has problems
of this kind with this, or should it be able to handle the load (should it
result in disks that cannot be contacted anymore or should it just perform
very slow)?
Your opinion is very much appreciated!
Kind regards,
Jan Martijn Schuur
"Martijn" schrieb:
...
> I’d like to know what the opinion from you all is about applying a snapshot
> every night to a subscriber, which involves copying approximately 15GB of
> data. Should it be no problem to this, or is it very uncommon to do this
> because of hardware limitations?
15 GB is a large amount of data for a nightly snapshot. Why don't you use
the incremental transactional replication?
Your other problems with this are not familiar to me. Sorry.
2012年3月25日星期日
disk drive layout for best database performance
In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balance
d
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 G
B
logical slices. There are other applciations using the same physical drives
.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load o
f
> cache which makes the point mute, the only thing to check it to see if oth
er
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup yo
u
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanc
ed
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5' We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).
spam999free@.rrohio.com
remove 999 in order to email me|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.sql
OLTP environment. Our server has 2 fiber channel HBAs that are load balance
d
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 G
B
logical slices. There are other applciations using the same physical drives
.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load o
f
> cache which makes the point mute, the only thing to check it to see if oth
er
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup yo
u
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanc
ed
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5' We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).
spam999free@.rrohio.com
remove 999 in order to email me|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.sql
disk drive layout for best database performance
In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
Danne
Hi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne
|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>
|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>
|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
|||What's wrong with RAID 5? We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>
|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5? We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).
spam999free@.rrohio.com
remove 999 in order to email me
|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
Danne
Hi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne
|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>
|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>
>
|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...[vbcol=seagreen]
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
|||What's wrong with RAID 5? We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>
>
|||In article <e2GoRhnAGHA.3804@.TK2MSFTNGP14.phx.gbl>, kkogan@.haiint.com
says...
> What's wrong with RAID 5? We have been using it for a long time. It
> has been an industry standard for while, right?
The information he lists (in another post) for reasons to NOT use R5
have not been experienced on any of the hundreds of servers we maintain.
Not once, never, nada.
We do Mirrors for Log files and R5 (normally 5xdrives + hot spare) for
the database data files.
All RAID Arrays on their own controller, on a single channel, with RAM
on the card (and battery).
spam999free@.rrohio.com
remove 999 in order to email me
|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.
disk drive layout for best database performance
In creating a database which option will provide the best performance in a
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
--
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> In creating a database which option will provide the best performance in
>> a
>> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> balanced
>> attached to an EMC SAN. Should the database be created in a single file
>> on
>> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>> spread accross 6 disk drives each?
>> --
>> Danne
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > You can get more out of a 4 files, but you'll have to do some tables
> > movement between files so you'll find the optimal places for tables so
> > work can be equally distributed through all 4 files.
> >
> > --
> > Danijel Novak
> >
> >
> >
> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> >> In creating a database which option will provide the best performance in
> >> a
> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
> >> balanced
> >> attached to an EMC SAN. Should the database be created in a single file
> >> on
> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> >> spread accross 6 disk drives each?
> >> --
> >> Danne
> >
> >
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>> Not sure on your setup, but with our servers running of SAN it is hard to
>> tweak performance by spreading accross disk as the Shark has a crap load
>> of
>> cache which makes the point mute, the only thing to check it to see if
>> other
>> aps will contend for same disks on your storage. If disks a dedicated to
>> that server then i would bet that cache would even out almost any setup
>> you
>> make. but again i dont know your setup
>> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
>> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>> > Hi,
>> >
>> > You can get more out of a 4 files, but you'll have to do some tables
>> > movement between files so you'll find the optimal places for tables so
>> > work can be equally distributed through all 4 files.
>> >
>> > --
>> > Danijel Novak
>> >
>> >
>> >
>> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
>> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> >> In creating a database which option will provide the best performance
>> >> in
>> >> a
>> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> >> balanced
>> >> attached to an EMC SAN. Should the database be created in a single
>> >> file
>> >> on
>> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk
>> >> drives
>> >> spread accross 6 disk drives each?
>> >> --
>> >> Danne
>> >
>> >
>>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>> In creating a database which option will provide the best performance
>> in a OLTP environment. Our server has 2 fiber channel HBAs that are
>> load balanced attached to an EMC SAN. Should the database be created
>> in a single file on one 200 GB drive spread across 24 disk drives or
>> four 50 GB disk drives spread accross 6 disk drives each?
>>
>|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.
OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
attached to an EMC SAN. Should the database be created in a single file on
one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
spread accross 6 disk drives each?
--
DanneHi,
You can get more out of a 4 files, but you'll have to do some tables
movement between files so you'll find the optimal places for tables so work
can be equally distributed through all 4 files.
--
Danijel Novak
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> In creating a database which option will provide the best performance in a
> OLTP environment. Our server has 2 fiber channel HBAs that are load
> balanced
> attached to an EMC SAN. Should the database be created in a single file
> on
> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> spread accross 6 disk drives each?
> --
> Danne|||Not sure on your setup, but with our servers running of SAN it is hard to
tweak performance by spreading accross disk as the Shark has a crap load of
cache which makes the point mute, the only thing to check it to see if other
aps will contend for same disks on your storage. If disks a dedicated to
that server then i would bet that cache would even out almost any setup you
make. but again i dont know your setup
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You can get more out of a 4 files, but you'll have to do some tables
> movement between files so you'll find the optimal places for tables so
> work can be equally distributed through all 4 files.
> --
> Danijel Novak
>
> "Danne" <Danne@.discussions.microsoft.com> wrote in message
> news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> In creating a database which option will provide the best performance in
>> a
>> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> balanced
>> attached to an EMC SAN. Should the database be created in a single file
>> on
>> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>> spread accross 6 disk drives each?
>> --
>> Danne
>|||We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9 GB
logical slices. There are other applciations using the same physical drives.
We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
for system databases. We are allocating 4 9 GB devices for tempdb per a
recomendation from the software vendor. the 200 GB drive or 4 50 GB drives
are for the data file(s). I am not sure if we will see any difference
between the two choices, but I wanted to ask to see what best practices are.
Thanks for your comments.
--
Danne
"David J. Cartwright" wrote:
> Not sure on your setup, but with our servers running of SAN it is hard to
> tweak performance by spreading accross disk as the Shark has a crap load of
> cache which makes the point mute, the only thing to check it to see if other
> aps will contend for same disks on your storage. If disks a dedicated to
> that server then i would bet that cache would even out almost any setup you
> make. but again i dont know your setup
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > You can get more out of a 4 files, but you'll have to do some tables
> > movement between files so you'll find the optimal places for tables so
> > work can be equally distributed through all 4 files.
> >
> > --
> > Danijel Novak
> >
> >
> >
> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
> >> In creating a database which option will provide the best performance in
> >> a
> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
> >> balanced
> >> attached to an EMC SAN. Should the database be created in a single file
> >> on
> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
> >> spread accross 6 disk drives each?
> >> --
> >> Danne
> >
> >
>
>|||Danne,
The general rule of thumb for RAID is the more head/disks you have, the
better your performance. Of course it also has to do with the total
cache available on the SAN controllers. This is from the hardware/RAID
level. All the RAID drives created for SQL Server, or any database
should be RAID 0+1 or 10. DO NOT USE RAID 5!
Shahryar
Danne wrote:
>In creating a database which option will provide the best performance in a
>OLTP environment. Our server has 2 fiber channel HBAs that are load balanced
>attached to an EMC SAN. Should the database be created in a single file on
>one 200 GB drive spread across 24 disk drives or four 50 GB disk drives
>spread accross 6 disk drives each?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||*best practice would accomplish as many as possible of the following
avoid having to read and write to the same disk at same time
avoid contention with other applications
log and data files on separate drives
have tempdb on seperate than from log and data files
with that said, having 4 50GB drives for data would give you some flexiblity
with file groups and putting certain tables on certain groups (ie drives),
but a SAN blured the strategy as even though the drives may be seperated
from each other, there may be other app needing to use that same drive when
you need it. or the *different drives could actualy be the same drives
anyway. If you can get drives allocated from different physical drives on
the SAN, and have these drives exclusivly, that would be the best choice.
If you can do this, then having 4 50 GB drives spread accross the storage
unit, would allow you to put tables that are joined etc...on different
drives, then when a query is run that joines or updates or whatever would
read read/write from different location where the data would come from on
drive, tempdb proccessing would accour on another and transaction would be
logged to yet another. But i would guess that unless the storage unit is
heavily used that this would all happen in cache on the storage unit and
would appear to be light speed to your server...your server would continue
on its way and the actualy data would be read or written from cache at the
units leasure.
locating the data, log and tempdb on seperate drives gives you flexibility
in the future also.
but.....on one of our systems we found the best performance when all where
on the same drive.
location on the storage solution, cache, and contention with other apps is
what you need to tweak.
"Danne" <Danne@.discussions.microsoft.com> wrote in message
news:8A8E8377-198A-439A-9D4B-F32D74EC455C@.microsoft.com...
> We have 16 GB of cache on the Symm. The physical drives are 146 GB with 9
> GB
> logical slices. There are other applciations using the same physical
> drives.
> We have allocated 1 9 GB drive for transaction log and a second 9 GB drive
> for system databases. We are allocating 4 9 GB devices for tempdb per a
> recomendation from the software vendor. the 200 GB drive or 4 50 GB
> drives
> are for the data file(s). I am not sure if we will see any difference
> between the two choices, but I wanted to ask to see what best practices
> are.
> Thanks for your comments.
> --
> Danne
>
> "David J. Cartwright" wrote:
>> Not sure on your setup, but with our servers running of SAN it is hard to
>> tweak performance by spreading accross disk as the Shark has a crap load
>> of
>> cache which makes the point mute, the only thing to check it to see if
>> other
>> aps will contend for same disks on your storage. If disks a dedicated to
>> that server then i would bet that cache would even out almost any setup
>> you
>> make. but again i dont know your setup
>> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
>> news:Okdge5lAGHA.516@.TK2MSFTNGP15.phx.gbl...
>> > Hi,
>> >
>> > You can get more out of a 4 files, but you'll have to do some tables
>> > movement between files so you'll find the optimal places for tables so
>> > work can be equally distributed through all 4 files.
>> >
>> > --
>> > Danijel Novak
>> >
>> >
>> >
>> > "Danne" <Danne@.discussions.microsoft.com> wrote in message
>> > news:DF3F5578-1E5D-4DBC-B7B1-8FBDA952DCB3@.microsoft.com...
>> >> In creating a database which option will provide the best performance
>> >> in
>> >> a
>> >> OLTP environment. Our server has 2 fiber channel HBAs that are load
>> >> balanced
>> >> attached to an EMC SAN. Should the database be created in a single
>> >> file
>> >> on
>> >> one 200 GB drive spread across 24 disk drives or four 50 GB disk
>> >> drives
>> >> spread accross 6 disk drives each?
>> >> --
>> >> Danne
>> >
>> >
>>|||What's wrong with RAID 5' We have been using it for a long time. It
has been an industry standard for while, right?
Shahryar G. Hashemi wrote:
> Danne,
> The general rule of thumb for RAID is the more head/disks you have, the
> better your performance. Of course it also has to do with the total
> cache available on the SAN controllers. This is from the hardware/RAID
> level. All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
> Shahryar
> Danne wrote:
>> In creating a database which option will provide the best performance
>> in a OLTP environment. Our server has 2 fiber channel HBAs that are
>> load balanced attached to an EMC SAN. Should the database be created
>> in a single file on one 200 GB drive spread across 24 disk drives or
>> four 50 GB disk drives spread accross 6 disk drives each?
>>
>|||"Shahryar G. Hashemi" wrote:
> All the RAID drives created for SQL Server, or any database
> should be RAID 0+1 or 10. DO NOT USE RAID 5!
wow, all of my production servers run raid 5.
i'll go immediately change them to raid 10.
2012年3月19日星期一
Disconection of Database users in mirror configuration
On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.
On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.
Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.
After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.
Hi,
I quoted the topic "Managing server metadata and jobs after role switching" in BOL.
"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.
The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.
If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "
Peng
Disconection of Database users in mirror configuration
On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.
On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.
Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.
After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.
Hi,
I quoted the topic "Managing server metadata and jobs after role switching" in BOL.
"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.
The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.
If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "
Peng
2012年3月11日星期日
Disaster recovery in multiple publishers environment
Hi,
I configured multiple publishers and one subscriber architecture on sql 2005
by having one publisher control the schema and indexes on the subscriber, and
the other publishers only append their data to the subscriber. I'm trying to
mimic a disaster recovery issue where one of the publishers fails (one
scenario is for the main publisher to fail, and one scenario is for one of
the other publishers to fail), but I'm having issue with restoring any of the
publishers (either type) and re-initializing the replication with the one
subscriber in a way that doesn't generate data conflicts and allow the
replication to continue.
Every time I try to do this, the subscriber reports of data conflicts
against the data that the same publisher has replicated already (this is
because the records have a primary uniqueidentifier key), and now tries to
replicate again, and therefore replication doesn't seem to keep pushing new
data to the subscriber.
How can approach either scenario without having to delete the failed
publisher data from the subscriber, and without allowing for duplicate data
(if I remove the primary key from the subscriber) before I re-initialize the
replication?
Thanks,
Gil
Can you outline exactly how the disaster recovery test is being done - do you
restore the publisher's publication database to the same or another server?
Are you synchronising the distribution database backups? Are you using the
same distribution database or does each publisher have its own one.
Thanks,
Paul Ibison
|||Have a look at the continue on data consistency error profile. This will
allow you to skip the pk violations.
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
"Gil Shabat" <GilShabat@.discussions.microsoft.com> wrote in message
news:96ABD1A2-F355-4A77-B44C-2B60EA410CDD@.microsoft.com...
> Hi,
> I configured multiple publishers and one subscriber architecture on sql
> 2005
> by having one publisher control the schema and indexes on the subscriber,
> and
> the other publishers only append their data to the subscriber. I'm trying
> to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of
> the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing
> new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate
> data
> (if I remove the primary key from the subscriber) before I re-initialize
> the
> replication?
> Thanks,
> Gil
|||Thanks for your replies.
For now I'm using one server to backup, and then restore the database. I
haven't got to restore the distribution database, and this also exists on the
same server. After some experimenting, I found that if I delete the
subscription + publisher, then restore the database, run the following
command exec sp_removedbreplication @.dbname=’dbname’,@.type='both' to remove
records of the publication,
and re-create the publication + subscription, but configure the subscriber
not to initialize, I get to a point that the new data is replicated again
without too many issues on both scenarios. however, I'm not sure whether this
is the right approach to take. Can you please let me know your thoughts?
Also, on a related issue, I tested using the SQL 2005 Developer edition
changing the schema of the subscriber, by changing the main publisher, and
this seems to work just fine. Problem is that when I run same scripts against
a publisher that has the setting to 'Keep existing object unchanged', it
still tries to replicate the DDL (tried to add a column to an existing
replicated table), and therefore returns with error (something about the same
column name can't exist twice on the same table). In addition, if I update
one of the other publishers first I can clearly see that despite the 'Keep
existing object unchanged' setting it still replicates the changes.
I tried to follow the process above and this allowed me to get around this
issue (i.e. delete all publications other than main publication, make schema
changes to all including the main publication, so subscriber schema would
also be modified, then re-create the publications, and disable the
initialize), but this seems completely wrong to me. Any thoughts on that
Thanks,
Gil
"Gil Shabat" wrote:
> Hi,
> I configured multiple publishers and one subscriber architecture on sql 2005
> by having one publisher control the schema and indexes on the subscriber, and
> the other publishers only append their data to the subscriber. I'm trying to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate data
> (if I remove the primary key from the subscriber) before I re-initialize the
> replication?
> Thanks,
> Gil
|||What you initially describe is known as a nosync initialization and this is
quite standard.
Am not too sure why the replication of ddl changes would cause a duplicate
column - can you elaborate. EG is the schema of the table on the subscriber
already different to the publisher before the change?
As a workaround which avoids a complete reinitialization, you could drop the
article, drop the subscription to the article, make the change then readd,
(see http://www.replicationanswers.com/AddColumn.asp) but this might not be
necessary depending on more info from the question above.
HTH,
Paul Ibison
|||Thanks for the info.
I basically tested by having two schema identical databases (publishers),
and an identical schema database subscriber. Any change to the schema (ALTER
TABLE ADD...) on either publisher caused an immediate replication of the DDL
to the subscriber regardless whether or not the 'Keep existing object
unchanged' setting was used or not (the main publisher used the 'Drop
existing object and create a new one' and the secondary publisher used the
'Keep existing object unchanged' setting). In our production environment I
envision running the same DDL code against all publishers, and having only
the main publisher replicate those schema changes to the subscriber.
Unfortunately, when I tried to run a simple add column statement, the first
one to update its ddl statements have also replicated its changes to the
subscriber, while the 2nd one tried to do the same, but return an error that
the same column name cannot exist multiple times. The following is the exact
message: "Column name 'additional_Sync' in table 'dbo.Primary1' is specified
more than once"
To create the environment, I created two separate databases, and I ran
against them the same DDL to create one table. Then I created the main
publisher and subscription, which created the environment on the 3rd database
(subscriber), and finally, I created a new publication for the secondary
publisher, but used the 'Keep existing object unchanged' setting, so schema
wouldn't be re-created. This resulted in having data from the 2 publishers on
the subscriber. At this point I tried to test with adding a new column to the
table I created on the publishers.
I would appreciate any thought you might have.
Thanks,
Gil
|||Please can you test something for me with your setup. I believe that in SQL
2000 I set this up differently. One publisher/subscriber was set up as per
usual. The other one was set up with the "keep the existing table unchanged"
option. Data was sent from the second publisher but there was no DROP TABLE
statement there. If the data is partitioned this is fine. The thing is that
after setting it up this way, using sp_repladdcolumn, the column was only
propagated from the first publisher and not the second. This is where I
believe the difference might lie - you are using ALTER TABLE instead, and you
have the "keep existing..." setting on each publisher's articles. Please try
setting it up my way (not yet tried in SQL 2005) and see if this works for
you...
HTH,
Paul Ibison
|||I believe that in SQL 2005 MS changed the option from "keep the existing
table unchanged" to 'Keep existing object unchanged', but both do exactly the
same thing
I tried already yesterday to disable the replication schema updates by
executing: exec sp_changepublication @.publication ='Publication
Name',@.property ='replicate_ddl', @.value=0
and then executed the alter table command, which wasn't included in the
replication. then I added the column to replication by executing the
sp_repladdcolumn on both publishers, and the column has been added, and again
like before, both DDL commands were sent to subscriber, and I ended up with
the same error
Gil
I configured multiple publishers and one subscriber architecture on sql 2005
by having one publisher control the schema and indexes on the subscriber, and
the other publishers only append their data to the subscriber. I'm trying to
mimic a disaster recovery issue where one of the publishers fails (one
scenario is for the main publisher to fail, and one scenario is for one of
the other publishers to fail), but I'm having issue with restoring any of the
publishers (either type) and re-initializing the replication with the one
subscriber in a way that doesn't generate data conflicts and allow the
replication to continue.
Every time I try to do this, the subscriber reports of data conflicts
against the data that the same publisher has replicated already (this is
because the records have a primary uniqueidentifier key), and now tries to
replicate again, and therefore replication doesn't seem to keep pushing new
data to the subscriber.
How can approach either scenario without having to delete the failed
publisher data from the subscriber, and without allowing for duplicate data
(if I remove the primary key from the subscriber) before I re-initialize the
replication?
Thanks,
Gil
Can you outline exactly how the disaster recovery test is being done - do you
restore the publisher's publication database to the same or another server?
Are you synchronising the distribution database backups? Are you using the
same distribution database or does each publisher have its own one.
Thanks,
Paul Ibison
|||Have a look at the continue on data consistency error profile. This will
allow you to skip the pk violations.
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
"Gil Shabat" <GilShabat@.discussions.microsoft.com> wrote in message
news:96ABD1A2-F355-4A77-B44C-2B60EA410CDD@.microsoft.com...
> Hi,
> I configured multiple publishers and one subscriber architecture on sql
> 2005
> by having one publisher control the schema and indexes on the subscriber,
> and
> the other publishers only append their data to the subscriber. I'm trying
> to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of
> the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing
> new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate
> data
> (if I remove the primary key from the subscriber) before I re-initialize
> the
> replication?
> Thanks,
> Gil
|||Thanks for your replies.
For now I'm using one server to backup, and then restore the database. I
haven't got to restore the distribution database, and this also exists on the
same server. After some experimenting, I found that if I delete the
subscription + publisher, then restore the database, run the following
command exec sp_removedbreplication @.dbname=’dbname’,@.type='both' to remove
records of the publication,
and re-create the publication + subscription, but configure the subscriber
not to initialize, I get to a point that the new data is replicated again
without too many issues on both scenarios. however, I'm not sure whether this
is the right approach to take. Can you please let me know your thoughts?
Also, on a related issue, I tested using the SQL 2005 Developer edition
changing the schema of the subscriber, by changing the main publisher, and
this seems to work just fine. Problem is that when I run same scripts against
a publisher that has the setting to 'Keep existing object unchanged', it
still tries to replicate the DDL (tried to add a column to an existing
replicated table), and therefore returns with error (something about the same
column name can't exist twice on the same table). In addition, if I update
one of the other publishers first I can clearly see that despite the 'Keep
existing object unchanged' setting it still replicates the changes.
I tried to follow the process above and this allowed me to get around this
issue (i.e. delete all publications other than main publication, make schema
changes to all including the main publication, so subscriber schema would
also be modified, then re-create the publications, and disable the
initialize), but this seems completely wrong to me. Any thoughts on that
Thanks,
Gil
"Gil Shabat" wrote:
> Hi,
> I configured multiple publishers and one subscriber architecture on sql 2005
> by having one publisher control the schema and indexes on the subscriber, and
> the other publishers only append their data to the subscriber. I'm trying to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate data
> (if I remove the primary key from the subscriber) before I re-initialize the
> replication?
> Thanks,
> Gil
|||What you initially describe is known as a nosync initialization and this is
quite standard.
Am not too sure why the replication of ddl changes would cause a duplicate
column - can you elaborate. EG is the schema of the table on the subscriber
already different to the publisher before the change?
As a workaround which avoids a complete reinitialization, you could drop the
article, drop the subscription to the article, make the change then readd,
(see http://www.replicationanswers.com/AddColumn.asp) but this might not be
necessary depending on more info from the question above.
HTH,
Paul Ibison
|||Thanks for the info.
I basically tested by having two schema identical databases (publishers),
and an identical schema database subscriber. Any change to the schema (ALTER
TABLE ADD...) on either publisher caused an immediate replication of the DDL
to the subscriber regardless whether or not the 'Keep existing object
unchanged' setting was used or not (the main publisher used the 'Drop
existing object and create a new one' and the secondary publisher used the
'Keep existing object unchanged' setting). In our production environment I
envision running the same DDL code against all publishers, and having only
the main publisher replicate those schema changes to the subscriber.
Unfortunately, when I tried to run a simple add column statement, the first
one to update its ddl statements have also replicated its changes to the
subscriber, while the 2nd one tried to do the same, but return an error that
the same column name cannot exist multiple times. The following is the exact
message: "Column name 'additional_Sync' in table 'dbo.Primary1' is specified
more than once"
To create the environment, I created two separate databases, and I ran
against them the same DDL to create one table. Then I created the main
publisher and subscription, which created the environment on the 3rd database
(subscriber), and finally, I created a new publication for the secondary
publisher, but used the 'Keep existing object unchanged' setting, so schema
wouldn't be re-created. This resulted in having data from the 2 publishers on
the subscriber. At this point I tried to test with adding a new column to the
table I created on the publishers.
I would appreciate any thought you might have.
Thanks,
Gil
|||Please can you test something for me with your setup. I believe that in SQL
2000 I set this up differently. One publisher/subscriber was set up as per
usual. The other one was set up with the "keep the existing table unchanged"
option. Data was sent from the second publisher but there was no DROP TABLE
statement there. If the data is partitioned this is fine. The thing is that
after setting it up this way, using sp_repladdcolumn, the column was only
propagated from the first publisher and not the second. This is where I
believe the difference might lie - you are using ALTER TABLE instead, and you
have the "keep existing..." setting on each publisher's articles. Please try
setting it up my way (not yet tried in SQL 2005) and see if this works for
you...
HTH,
Paul Ibison
|||I believe that in SQL 2005 MS changed the option from "keep the existing
table unchanged" to 'Keep existing object unchanged', but both do exactly the
same thing
I tried already yesterday to disable the replication schema updates by
executing: exec sp_changepublication @.publication ='Publication
Name',@.property ='replicate_ddl', @.value=0
and then executed the alter table command, which wasn't included in the
replication. then I added the column to replication by executing the
sp_repladdcolumn on both publishers, and the column has been added, and again
like before, both DDL commands were sent to subscriber, and I ended up with
the same error
Gil
2012年2月14日星期二
Disable Event Logging to Windows Application Log?
Hi,
We are starting to work with SSIS in our production environment & due to support issues; we are trying to get rid of the "Package xxx started" log entries inside of the Windows Application Event Log...
So far, I have tried many different things, including setting the LoggingMode to "Disabled", as well as adding a new logging reference with a different destination... All of which still do not get rid of the extra log entries...
Is this possible? & does anyone know how to get this done?
Thanks!
I am having this same problem. Have you had any luck with this?
I have LoggingMode disabled in the properties of the package, I don't have anything checked on the Configure SSIS Logs screen in the Containers list nor on the Providers and Logs tab and the Details tab. The packages are being run from a SQL Server Agent job.
Thanks.
Bobette
|||I move the thread to SQL Server Tools general because this looks like and SQL Agent problem
Thanks,
Ovidiu
Hi,
Unfortunately I still haven't found a way to get this accomplished... At least I'm not the only one with the same problem.. . :) Thanks Bobette..
For now, we are simply living with the problem & have built a custom app that queries the event log for error's only... But this isn't a solution, just a band-aid...
~James
|||Hopefully someone that knows will find our posting here. Thanks for the quick reply!
bobette
|||After I read your poost twice I realized this is a SSIS thing.
The current implementation will always post the "Package xxx started" event and there is no way to disable this message
Thanks,
Ovidiu Burlacu
Thanks! At least I know to quit trying to figure it out.
Bobette
Disable Event Logging to Windows Application Log?
Hi,
We are starting to work with SSIS in our production environment & due to support issues; we are trying to get rid of the "Package xxx started" log entries inside of the Windows Application Event Log...
So far, I have tried many different things, including setting the LoggingMode to "Disabled", as well as adding a new logging reference with a different destination... All of which still do not get rid of the extra log entries...
Is this possible? & does anyone know how to get this done?
Thanks!
I am having this same problem. Have you had any luck with this?
I have LoggingMode disabled in the properties of the package, I don't have anything checked on the Configure SSIS Logs screen in the Containers list nor on the Providers and Logs tab and the Details tab. The packages are being run from a SQL Server Agent job.
Thanks.
Bobette
|||I move the thread to SQL Server Tools general because this looks like and SQL Agent problem
Thanks,
Ovidiu
Hi,
Unfortunately I still haven't found a way to get this accomplished... At least I'm not the only one with the same problem.. . :) Thanks Bobette..
For now, we are simply living with the problem & have built a custom app that queries the event log for error's only... But this isn't a solution, just a band-aid...
~James
|||Hopefully someone that knows will find our posting here. Thanks for the quick reply!
bobette
|||After I read your poost twice I realized this is a SSIS thing.
The current implementation will always post the "Package xxx started" event and there is no way to disable this message
Thanks,
Ovidiu Burlacu
Thanks! At least I know to quit trying to figure it out.
Bobette
Disable Event Logging to Windows Application Log?
Hi,
We are starting to work with SSIS in our production environment & due to support issues; we are trying to get rid of the "Package xxx started" log entries inside of the Windows Application Event Log...
So far, I have tried many different things, including setting the LoggingMode to "Disabled", as well as adding a new logging reference with a different destination... All of which still do not get rid of the extra log entries...
Is this possible? & does anyone know how to get this done?
Thanks!
I am having this same problem. Have you had any luck with this?
I have LoggingMode disabled in the properties of the package, I don't have anything checked on the Configure SSIS Logs screen in the Containers list nor on the Providers and Logs tab and the Details tab. The packages are being run from a SQL Server Agent job.
Thanks.
Bobette
|||I move the thread to SQL Server Tools general because this looks like and SQL Agent problem
Thanks,
Ovidiu
Hi,
Unfortunately I still haven't found a way to get this accomplished... At least I'm not the only one with the same problem.. . :) Thanks Bobette..
For now, we are simply living with the problem & have built a custom app that queries the event log for error's only... But this isn't a solution, just a band-aid...
~James
|||Hopefully someone that knows will find our posting here. Thanks for the quick reply!
bobette
|||After I read your poost twice I realized this is a SSIS thing.
The current implementation will always post the "Package xxx started" event and there is no way to disable this message
Thanks,
Ovidiu Burlacu
Thanks! At least I know to quit trying to figure it out.
Bobette
订阅：
博文 (Atom)