We are in the process of determining a sql db replication strategy. We're
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
Jim
Load balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Sunday, March 25, 2012
Best replication strategy suggestion?
We are in the process of determining a sql db replication strategy. We're
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
JimLoad balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
JimLoad balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
Best replication strategy suggestion?
We are in the process of determining a sql db replication strategy. We're
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
JimLoad balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
JimLoad balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
Thursday, March 22, 2012
Best Practices of cube release process
Have you guys come across best way to move/release cube changes from test to production server. Any way to automate this process.
- Ashok
You're probably going to want to give the Deployment Wizard a try:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1485584&SiteID=1
|||The Deployment Wizard is probably a good option. It will handle things like not overwriting datasources, roles and partitions which can be handy if these vary between your environments (which they often do). This tool can also be run from the command line which allows for a degree of automation.Tuesday, March 20, 2012
Best practices for copying databases...
I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.
Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.
|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<data base_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
what the best process is for copying multiple databases from the production
server to the test server.
Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.
|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<data base_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
Best practices for copying databases...
I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the productio
n
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.b
ak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the productio
n
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.b
ak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
Best practices for copying databases...
I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.comsql
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.comsql
Best practices for concurrent development of DB
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph
Best practices for concurrent development of DB
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
Look into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
Look into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph
|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph
Best practices for concurrent development of DB
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
ChristophLook into what is called a "Unit Test Model." Also, consider modeling your
database base tables with a modeling tool like Computer Associates' ERWin
along with ModelMart. This will allow you to do version control on the
base, i.e., "dbo" schema.
As far as the interface objects: stored procedures, functions, and views,
saving each developer's copies of those objects under their own schema but
against the same base tables allows each of you to do concurrent,
independent work.
Sincerely,
Anthony Thomas
"Christoph Wienands" <christoph.wienands@.siemens.remove.com> wrote in
message news:uTu2bPmhFHA.2156@.TK2MSFTNGP14.phx.gbl...
Hey guys,
I'm currently setting improving our development process for an app with an
underlying SQL Server DB. We're gonna be two to three people who will be
developing different "regions" of the same database (with region I mean
different tables, queries, sps, etc.)
So far I was the only one working on the DB so I just stuck a backup file of
the DB into source control. But now we'll be two or three people and that
doesn't work anymore.
I think the ideal solution would allow a CVS-style merge of e.g. build and
fill scripts. I'm aware that we might run into conflicts if two people
change the same data but well, I guess there is no perfect solution. Does
anybody have experience with a similar situation?
I also would like to make the thing as automated as possible. In particular
I'm thinking of a mechanism that restores a database from a couple of
scripts and vice versa, something that scripts the whole database as a text
file with one button click (so that source control can do the text-based
merge).
Thanks in advance for your feedback and your ideas.
Christoph|||Hey Anthony,
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%234XqJIphFHA.708@.TK2MSFTNGP10.phx.gbl...
> Look into what is called a "Unit Test Model." Also, consider modeling
> your
> database base tables with a modeling tool like Computer Associates' ERWin
> along with ModelMart. This will allow you to do version control on the
> base, i.e., "dbo" schema.
> As far as the interface objects: stored procedures, functions, and views,
> saving each developer's copies of those objects under their own schema but
> against the same base tables allows each of you to do concurrent,
> independent work.
Thanks for your input. During my research I discovered there are a whole
bunch of tools that support you with that. Here is a link to a page with a
long list of them:
http://www.aspfaq.com/show.asp?id=2209
Some of them integrate with source control, do versioning, allow you to
create upgrade scripts for production databases, etc. Pretty cool stuff :-)
Just for completeness, here the textual list (who knows how long this link
will exist):
AdeptSQL Diff
AGS SQL Scribe
Apex SQLDiff
BMC SQL Programmer
Berryware SQL Matcher
CAST Release Builder
Cobb Systems Data Dictionary
DB Ghost
DBMaestro
Embarcadero DBArtisan
Enhanced ISQL/w
ERwin
Imceda Speed Change Manager
QALite
Quest SQL Impact
Red-Gate SQLCompare (with a companion article at MSSqlServer.com)
SQL Server Comparison Tool
SQL Total Compare
SynchroComp
Total SQL Analyzer
WinSQL
xSQL Object
Regards, Christoph
Saturday, February 25, 2012
Best Data Type for a Tracking ID?
Hello,
We're in the table design process and could really use outside thoughts on
our options.
The table in question will grow to several million records. This table will
update and insert depending on what the tracking ID does as it lives out it
day long life span.
Example Stored Proc:
UPDATE tbTracking
SET myCount = myCount +1
WHERE trackingID = @.trackingID AND productID = @.productID
IF (@.@.rowcount = 0)
BEGIN
INSERT INTO tbTracking (...) VALUES (...) ;
END
Everyday at midnight the table will be truncate just afer many other queries
generated reports and store cumulative data into other tables.
Our goal is extreme performance, with that in mind what is the best data
type for trackingID and productID? We have the luxury to make them whatever
is best, as this is a completely new project.
Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like,
but we're in need of advise from those with more experience with these
matters.
Thank you for your time.
MarkMark S. wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
This doesn't make much sense to me. As described it isn't clear whether
trackingid is a surrogate key or not. If it isn't then I don't
understand what its purpose is. What is the business meaning of
trackingid? Why isn't the datatype predetermined? What key or keys
exist in this table?
Maybe we are talking surrogate keys here, in which case see:
http://www.aspfaq.com/show.asp?id=2504
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
A guiding in principle in data warehouse applications is usually to
capture the data at the finest possible level of granularity. I expect
you've considered this, but I just thought it worth restating for the
benefit of all.
David Portas
SQL Server MVP
--|||Who or what determines the value of TrackingID?
If this key is to uniquely identify an event (such as a support call), then
perhaps (8 byte) datetime would be the logical choice. It has inherent
meaning, and unless you have hundreds of support calls coming in per minute,
it is unlikely that this key value would be duplicated, and retry logic on
the insert procedure or statement can handle the unlikely event that does.
There may even be a need to purge the tbTracking table based on < a specific
date/time rather than truncating the entire table at the end of the day.
As for ProductID, this should be an existing attribute in your database.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||In my experiences, int data type is the best performer especially when it
comes to indexing, joins, etc. (This comment relates to 32-bit O/S and SQL
Server 2000.)
I would make a meaningless key:
TrackingRowId int, identity, primary key, clustered
ProductRowId int, identity, primary key, clustered
SQL Server takes care of assigning the next number. The clustered index
will always insert data at the end of the data pages which avoids page split
s.
If you want to reset the tracking id each midnight, simply change the reseed
the tables as part of your processing.
Personally...
I would NOT use a character string for a key because its slower to compare
strings.
I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
butt when you have to write a manual query to pull data.
Finally, I have to reiterate David's point of capturing data at the lowest
level. Are you sure you want to summarize and truncate on a nightly basis.
What if a bug is discovered? You won't have the original data to recreate
the totals. What if a new way of summarizing is added? You won't have the
original data to drill into or summarize.
And, to reiterate JT's point of having a datetime column that can be very
handy. It is debateable whether to use it as the key or not. If you're
truncating the tables each night, it may be a waste.
Just my two cents,
Joe
"Mark S." wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
>
>|||Gentleman thank you for your feedback.
Joe, other than the UniqueIndentifier being unfriendly for humans, do you
find it faster than INTs?
As to th other questions and caveats, all that has been considered
previously, and wasn't mentioned in my question in the interests of brevity
and an attempt to focus my question on a single point. Regardless, thank you
for your full consideration.
Cheers,
Mark
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:50A0324B-1446-4044-A850-706B59A04169@.microsoft.com...
> In my experiences, int data type is the best performer especially when it
> comes to indexing, joins, etc. (This comment relates to 32-bit O/S and
> SQL
> Server 2000.)
> I would make a meaningless key:
> TrackingRowId int, identity, primary key, clustered
> ProductRowId int, identity, primary key, clustered
> SQL Server takes care of assigning the next number. The clustered index
> will always insert data at the end of the data pages which avoids page
> splits.
> If you want to reset the tracking id each midnight, simply change the
> reseed
> the tables as part of your processing.
> Personally...
> I would NOT use a character string for a key because its slower to compare
> strings.
> I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
> butt when you have to write a manual query to pull data.
> Finally, I have to reiterate David's point of capturing data at the lowest
> level. Are you sure you want to summarize and truncate on a nightly
> basis.
> What if a bug is discovered? You won't have the original data to recreate
> the totals. What if a new way of summarizing is added? You won't have
> the
> original data to drill into or summarize.
> And, to reiterate JT's point of having a datetime column that can be very
> handy. It is debateable whether to use it as the key or not. If you're
> truncating the tables each night, it may be a waste.
> Just my two cents,
> Joe
> "Mark S." wrote:
>|||The last line of the article David suggested
http://www.aspfaq.com/show.asp?id=2504
Says alot about GUI() not being optimized, if anyone disagrees, feel free to
speak up:
"the wider datatype leads to a drop in index performance (if clustered, each
insert almost guaranteed to 'dirty' a different page), and an increase in
storage requirements; " and five other cons.
Thank you.|||To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
WITH FILLFACTOR = 100
This causes every new row to be added at the end of the table--minimizing
index maintenance and eliminating page splits. Use caution, however: since
SQL Server doesn't automatically reorganize indexes, a high volume of DELETE
activity will cause the index to become sparse, which can reduce SELECT
performance, but it will not affect INSERT performance.
The INT datatype matches the word size of most Intel processors (32-bit), so
comparisons require fewer CPU cycles.
The stored procedure is an example of what not to do. It's a recipie for
primary key constraint violations. There's nothing that prevents two
transactions from trying to INSERT the same information at the same time.
Most of the time what will happen is that one connection will succeed with
the INSERT and the other will UPDATE the newly inserted row, but a collision
will occur if identical UPDATE statements occur simultaneously on two
unbound connections followed by (since the row doesn't yet exist)
simultaneous identical INSERT statements.
The correct method is to use something like this:
BEGIN TRAN
IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
UPDATE
ELSE
INSERT
COMMIT
Some people use the following instead, but I prefer the above method since
it is easier to read and understand
BEGIN TRAN
INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
IF @.@.ROWCOUNT = 0
UPDATE
COMMIT
Note that there isn't any marked reduction in performance or concurrency
between this and your sample, because UPDLOCK doesn't block SELECTs, and any
blocking that does occur is necessary to maintain integrity. Without the
EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
anyway which involves reading the index page into memory. With the EXISTS
clause, the SELECT reads the index page and applies an update lock, and the
INSERT or UPDATE simply transition from an update lock to an exclusive lock
in memory--no additional physical read is necessary.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||Thank you very much.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23oG2PTDDGHA.3980@.TK2MSFTNGP12.phx.gbl...
> To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY
> CLUSTERED WITH FILLFACTOR = 100
> This causes every new row to be added at the end of the table--minimizing
> index maintenance and eliminating page splits. Use caution, however:
> since SQL Server doesn't automatically reorganize indexes, a high volume
> of DELETE activity will cause the index to become sparse, which can reduce
> SELECT performance, but it will not affect INSERT performance.
> The INT datatype matches the word size of most Intel processors (32-bit),
> so comparisons require fewer CPU cycles.
> The stored procedure is an example of what not to do. It's a recipie for
> primary key constraint violations. There's nothing that prevents two
> transactions from trying to INSERT the same information at the same time.
> Most of the time what will happen is that one connection will succeed with
> the INSERT and the other will UPDATE the newly inserted row, but a
> collision will occur if identical UPDATE statements occur simultaneously
> on two unbound connections followed by (since the row doesn't yet exist)
> simultaneous identical INSERT statements.
> The correct method is to use something like this:
> BEGIN TRAN
> IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
> UPDATE
> ELSE
> INSERT
> COMMIT
> Some people use the following instead, but I prefer the above method since
> it is easier to read and understand
> BEGIN TRAN
> INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
> IF @.@.ROWCOUNT = 0
> UPDATE
> COMMIT
> Note that there isn't any marked reduction in performance or concurrency
> between this and your sample, because UPDLOCK doesn't block SELECTs, and
> any blocking that does occur is necessary to maintain integrity. Without
> the EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
> anyway which involves reading the index page into memory. With the EXISTS
> clause, the SELECT reads the index page and applies an update lock, and
> the INSERT or UPDATE simply transition from an update lock to an exclusive
> lock in memory--no additional physical read is necessary.
> "Mark S." <marks@.yahoo.com> wrote in message
> news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
>
We're in the table design process and could really use outside thoughts on
our options.
The table in question will grow to several million records. This table will
update and insert depending on what the tracking ID does as it lives out it
day long life span.
Example Stored Proc:
UPDATE tbTracking
SET myCount = myCount +1
WHERE trackingID = @.trackingID AND productID = @.productID
IF (@.@.rowcount = 0)
BEGIN
INSERT INTO tbTracking (...) VALUES (...) ;
END
Everyday at midnight the table will be truncate just afer many other queries
generated reports and store cumulative data into other tables.
Our goal is extreme performance, with that in mind what is the best data
type for trackingID and productID? We have the luxury to make them whatever
is best, as this is a completely new project.
Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like,
but we're in need of advise from those with more experience with these
matters.
Thank you for your time.
MarkMark S. wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
This doesn't make much sense to me. As described it isn't clear whether
trackingid is a surrogate key or not. If it isn't then I don't
understand what its purpose is. What is the business meaning of
trackingid? Why isn't the datatype predetermined? What key or keys
exist in this table?
Maybe we are talking surrogate keys here, in which case see:
http://www.aspfaq.com/show.asp?id=2504
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
A guiding in principle in data warehouse applications is usually to
capture the data at the finest possible level of granularity. I expect
you've considered this, but I just thought it worth restating for the
benefit of all.
David Portas
SQL Server MVP
--|||Who or what determines the value of TrackingID?
If this key is to uniquely identify an event (such as a support call), then
perhaps (8 byte) datetime would be the logical choice. It has inherent
meaning, and unless you have hundreds of support calls coming in per minute,
it is unlikely that this key value would be duplicated, and retry logic on
the insert procedure or statement can handle the unlikely event that does.
There may even be a need to purge the tbTracking table based on < a specific
date/time rather than truncating the entire table at the end of the day.
As for ProductID, this should be an existing attribute in your database.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||In my experiences, int data type is the best performer especially when it
comes to indexing, joins, etc. (This comment relates to 32-bit O/S and SQL
Server 2000.)
I would make a meaningless key:
TrackingRowId int, identity, primary key, clustered
ProductRowId int, identity, primary key, clustered
SQL Server takes care of assigning the next number. The clustered index
will always insert data at the end of the data pages which avoids page split
s.
If you want to reset the tracking id each midnight, simply change the reseed
the tables as part of your processing.
Personally...
I would NOT use a character string for a key because its slower to compare
strings.
I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
butt when you have to write a manual query to pull data.
Finally, I have to reiterate David's point of capturing data at the lowest
level. Are you sure you want to summarize and truncate on a nightly basis.
What if a bug is discovered? You won't have the original data to recreate
the totals. What if a new way of summarizing is added? You won't have the
original data to drill into or summarize.
And, to reiterate JT's point of having a datetime column that can be very
handy. It is debateable whether to use it as the key or not. If you're
truncating the tables each night, it may be a waste.
Just my two cents,
Joe
"Mark S." wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
>
>|||Gentleman thank you for your feedback.
Joe, other than the UniqueIndentifier being unfriendly for humans, do you
find it faster than INTs?
As to th other questions and caveats, all that has been considered
previously, and wasn't mentioned in my question in the interests of brevity
and an attempt to focus my question on a single point. Regardless, thank you
for your full consideration.
Cheers,
Mark
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:50A0324B-1446-4044-A850-706B59A04169@.microsoft.com...
> In my experiences, int data type is the best performer especially when it
> comes to indexing, joins, etc. (This comment relates to 32-bit O/S and
> SQL
> Server 2000.)
> I would make a meaningless key:
> TrackingRowId int, identity, primary key, clustered
> ProductRowId int, identity, primary key, clustered
> SQL Server takes care of assigning the next number. The clustered index
> will always insert data at the end of the data pages which avoids page
> splits.
> If you want to reset the tracking id each midnight, simply change the
> reseed
> the tables as part of your processing.
> Personally...
> I would NOT use a character string for a key because its slower to compare
> strings.
> I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
> butt when you have to write a manual query to pull data.
> Finally, I have to reiterate David's point of capturing data at the lowest
> level. Are you sure you want to summarize and truncate on a nightly
> basis.
> What if a bug is discovered? You won't have the original data to recreate
> the totals. What if a new way of summarizing is added? You won't have
> the
> original data to drill into or summarize.
> And, to reiterate JT's point of having a datetime column that can be very
> handy. It is debateable whether to use it as the key or not. If you're
> truncating the tables each night, it may be a waste.
> Just my two cents,
> Joe
> "Mark S." wrote:
>|||The last line of the article David suggested
http://www.aspfaq.com/show.asp?id=2504
Says alot about GUI() not being optimized, if anyone disagrees, feel free to
speak up:
"the wider datatype leads to a drop in index performance (if clustered, each
insert almost guaranteed to 'dirty' a different page), and an increase in
storage requirements; " and five other cons.
Thank you.|||To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
WITH FILLFACTOR = 100
This causes every new row to be added at the end of the table--minimizing
index maintenance and eliminating page splits. Use caution, however: since
SQL Server doesn't automatically reorganize indexes, a high volume of DELETE
activity will cause the index to become sparse, which can reduce SELECT
performance, but it will not affect INSERT performance.
The INT datatype matches the word size of most Intel processors (32-bit), so
comparisons require fewer CPU cycles.
The stored procedure is an example of what not to do. It's a recipie for
primary key constraint violations. There's nothing that prevents two
transactions from trying to INSERT the same information at the same time.
Most of the time what will happen is that one connection will succeed with
the INSERT and the other will UPDATE the newly inserted row, but a collision
will occur if identical UPDATE statements occur simultaneously on two
unbound connections followed by (since the row doesn't yet exist)
simultaneous identical INSERT statements.
The correct method is to use something like this:
BEGIN TRAN
IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
UPDATE
ELSE
INSERT
COMMIT
Some people use the following instead, but I prefer the above method since
it is easier to read and understand
BEGIN TRAN
INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
IF @.@.ROWCOUNT = 0
UPDATE
COMMIT
Note that there isn't any marked reduction in performance or concurrency
between this and your sample, because UPDLOCK doesn't block SELECTs, and any
blocking that does occur is necessary to maintain integrity. Without the
EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
anyway which involves reading the index page into memory. With the EXISTS
clause, the SELECT reads the index page and applies an update lock, and the
INSERT or UPDATE simply transition from an update lock to an exclusive lock
in memory--no additional physical read is necessary.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||Thank you very much.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23oG2PTDDGHA.3980@.TK2MSFTNGP12.phx.gbl...
> To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY
> CLUSTERED WITH FILLFACTOR = 100
> This causes every new row to be added at the end of the table--minimizing
> index maintenance and eliminating page splits. Use caution, however:
> since SQL Server doesn't automatically reorganize indexes, a high volume
> of DELETE activity will cause the index to become sparse, which can reduce
> SELECT performance, but it will not affect INSERT performance.
> The INT datatype matches the word size of most Intel processors (32-bit),
> so comparisons require fewer CPU cycles.
> The stored procedure is an example of what not to do. It's a recipie for
> primary key constraint violations. There's nothing that prevents two
> transactions from trying to INSERT the same information at the same time.
> Most of the time what will happen is that one connection will succeed with
> the INSERT and the other will UPDATE the newly inserted row, but a
> collision will occur if identical UPDATE statements occur simultaneously
> on two unbound connections followed by (since the row doesn't yet exist)
> simultaneous identical INSERT statements.
> The correct method is to use something like this:
> BEGIN TRAN
> IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
> UPDATE
> ELSE
> INSERT
> COMMIT
> Some people use the following instead, but I prefer the above method since
> it is easier to read and understand
> BEGIN TRAN
> INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
> IF @.@.ROWCOUNT = 0
> UPDATE
> COMMIT
> Note that there isn't any marked reduction in performance or concurrency
> between this and your sample, because UPDLOCK doesn't block SELECTs, and
> any blocking that does occur is necessary to maintain integrity. Without
> the EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
> anyway which involves reading the index page into memory. With the EXISTS
> clause, the SELECT reads the index page and applies an update lock, and
> the INSERT or UPDATE simply transition from an update lock to an exclusive
> lock in memory--no additional physical read is necessary.
> "Mark S." <marks@.yahoo.com> wrote in message
> news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
>
Subscribe to:
Comments (Atom)