Best schema for replicate a DB without replicate Data

Hi,
We had log shipping from LA to New York for our mission critical databases.
However, the log shipping breaks all the time because some of these databases
or tran logs are huge.
Afer re-evaluation we found that some DBs we don't really need the data
replicated but we do need to keep the schema and objects up to date:
everything except the huge volume of data. It does not need to be real time,
we could do this once a day.
Could someone suggests us what are the better options of doing this? Some
way that is simple without a lot of maintenance issues and potential breaking
points.
Thanks
HuiSeems you simply want to generate schema at regular intervals?
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>|||check out DB Ghost - http://www.dbghost.com
"Christine C" wrote:
> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>|||Sorry, I did not describe the full requirement:
1. We need to copy all changes in the databases (including sysusers, stored
procedures, views, udf, ...etc) except the data. Of course sysusers we do
need the data because we need all the users replicated.
2. One challenges we have is that there are objects (views, stored proc)
that are cross databases. Therefore whatever tools we used need to be able to
not error out because of the dependencies.
3. One of our developers are trying the SQL-DMO APIs to do the
copyalltables. Here are the uncertains for us:
a. He is not sure if SQL-DMO allows him to copy the users, views, and
stored procedures separately as well.
b. Tthe entire extract for replicating three databases (without data) took
50 minutes. Which means the entire time the destination databases are going
to be unusable for a long time.
Common question to all tools in your link:
c. what we need is for the tool to take care of the delta of the source and
destination. The objects that no longer exist in the source, how do we remove
them in the destination if the scripts are driving from the source?
d. The tools suggested in your link, are they mostly scripting tools for
DDLs only (we don't care the data) or some of them can transfer users as
well?
e. Do they require manually adding or changing the script defination
everytime a DB has changed (schema or stored proc)? Can those tools also know
to remove all objects before it proceeds?
I know the list of questions are long, but these are critical processes we
want to setup in production. The problems that we encountered might not be
standards and having huge databases need to replicate to New York for DR
complicates the issue too.
Thank you for your patience in replying.
"Tibor Karaszi" wrote:
> Seems you simply want to generate schema at regular intervals?
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
> news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> > Hi,
> >
> > We had log shipping from LA to New York for our mission critical databases.
> > However, the log shipping breaks all the time because some of these databases
> > or tran logs are huge.
> >
> > Afer re-evaluation we found that some DBs we don't really need the data
> > replicated but we do need to keep the schema and objects up to date:
> > everything except the huge volume of data. It does not need to be real time,
> > we could do this once a day.
> > Could someone suggests us what are the better options of doing this? Some
> > way that is simple without a lot of maintenance issues and potential breaking
> > points.
> >
> > Thanks
> >
> > Hui
> >
> >
> >
> >
>
>sql

Best schema for replicate a DB without replicate Data

Hi,
We had log shipping from LA to New York for our mission critical databases.
However, the log shipping breaks all the time because some of these databases
or tran logs are huge.
Afer re-evaluation we found that some DBs we don't really need the data
replicated but we do need to keep the schema and objects up to date:
everything except the huge volume of data. It does not need to be real time,
we could do this once a day.
Could someone suggests us what are the better options of doing this? Some
way that is simple without a lot of maintenance issues and potential breaking
points.
Thanks
Hui
Seems you simply want to generate schema at regular intervals?
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>
|||check out DB Ghost - http://www.dbghost.com
"Christine C" wrote:

> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>
|||Sorry, I did not describe the full requirement:
1. We need to copy all changes in the databases (including sysusers, stored
procedures, views, udf, ...etc) except the data. Of course sysusers we do
need the data because we need all the users replicated.
2. One challenges we have is that there are objects (views, stored proc)
that are cross databases. Therefore whatever tools we used need to be able to
not error out because of the dependencies.
3. One of our developers are trying the SQL-DMO APIs to do the
copyalltables. Here are the uncertains for us:
a. He is not sure if SQL-DMO allows him to copy the users, views, and
stored procedures separately as well.
b. Tthe entire extract for replicating three databases (without data) took
50 minutes. Which means the entire time the destination databases are going
to be unusable for a long time.
Common question to all tools in your link:
c. what we need is for the tool to take care of the delta of the source and
destination. The objects that no longer exist in the source, how do we remove
them in the destination if the scripts are driving from the source?
d. The tools suggested in your link, are they mostly scripting tools for
DDLs only (we don't care the data) or some of them can transfer users as
well?
e. Do they require manually adding or changing the script defination
everytime a DB has changed (schema or stored proc)? Can those tools also know
to remove all objects before it proceeds?
I know the list of questions are long, but these are critical processes we
want to setup in production. The problems that we encountered might not be
standards and having huge databases need to replicate to New York for DR
complicates the issue too.
Thank you for your patience in replying.
"Tibor Karaszi" wrote:

> Seems you simply want to generate schema at regular intervals?
> http://www.karaszi.com/SQLServer/inf...ate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
> news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
>
>

Best schema for replicate a DB without replicate Data

Hi,
We had log shipping from LA to New York for our mission critical databases.
However, the log shipping breaks all the time because some of these database
s
or tran logs are huge.
Afer re-evaluation we found that some DBs we don't really need the data
replicated but we do need to keep the schema and objects up to date:
everything except the huge volume of data. It does not need to be real time,
we could do this once a day.
Could someone suggests us what are the better options of doing this? Some
way that is simple without a lot of maintenance issues and potential breakin
g
points.
Thanks
HuiSeems you simply want to generate schema at regular intervals?
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> Hi,
> We had log shipping from LA to New York for our mission critical databases
.
> However, the log shipping breaks all the time because some of these databa
ses
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real tim
e,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential break
ing
> points.
> Thanks
> Hui
>
>|||check out DB Ghost - http://www.dbghost.com
"Christine C" wrote:

> Hi,
> We had log shipping from LA to New York for our mission critical databases
.
> However, the log shipping breaks all the time because some of these databa
ses
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real tim
e,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential break
ing
> points.
> Thanks
> Hui
>
>|||Sorry, I did not describe the full requirement:
1. We need to copy all changes in the databases (including sysusers, stored
procedures, views, udf, ...etc) except the data. Of course sysusers we do
need the data because we need all the users replicated.
2. One challenges we have is that there are objects (views, stored proc)
that are cross databases. Therefore whatever tools we used need to be able t
o
not error out because of the dependencies.
3. One of our developers are trying the SQL-DMO APIs to do the
copyalltables. Here are the uncertains for us:
a. He is not sure if SQL-DMO allows him to copy the users, views, and
stored procedures separately as well.
b. Tthe entire extract for replicating three databases (without data) took
50 minutes. Which means the entire time the destination databases are going
to be unusable for a long time.
Common question to all tools in your link:
c. what we need is for the tool to take care of the delta of the source and
destination. The objects that no longer exist in the source, how do we remov
e
them in the destination if the scripts are driving from the source?
d. The tools suggested in your link, are they mostly scripting tools for
DDLs only (we don't care the data) or some of them can transfer users as
well?
e. Do they require manually adding or changing the script defination
everytime a DB has changed (schema or stored proc)? Can those tools also kno
w
to remove all objects before it proceeds?
I know the list of questions are long, but these are critical processes we
want to setup in production. The problems that we encountered might not be
standards and having huge databases need to replicate to New York for DR
complicates the issue too.
Thank you for your patience in replying.
"Tibor Karaszi" wrote:

> Seems you simply want to generate schema at regular intervals?
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
> news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
>
>

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,
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
>

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
>

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
>

Best Practice for Ranking

Looking for advise or best practice for ranking of top 10 companies (out of 100s) for aggregates on a shipping comparitive value (tonnage). Would like to make each of the top 10 companies a column with values broken down by region (rows). I also need a column for a single chosen company (not necessarily top 10), and another column for that company's percentage of market share.

I'd like to utilize our data cube in a matrix, but can't solve how to add the last two columns above. We have the option of using the cube or the transactional db. Any advice is helpful, thanks.

One option would be to use the sort number 1-10 as your grouping key and have your report dataset based on a union of the 1-10 dataset, the 11 extra column dataset, and 12 extra column dataset. Then do a Matrix to line it up.|||Thanks, we'll give this a try.

Best practice for DR/Standby server

We use SQL2000 Standard Edition SP3a. We have a
Production Server and a DR Server that is off site.
Currently I have SPs coded that do log shipping to the DR
Server every hour from the log backups on the production
server. Is this the best way to keep the DR Server up to
date? I've explored Replication, but it seemed much more
complex and structure changes don't get replicated which
means a new shipment of the entire Production DBs anytime
a structure change is made. It seems like Replication
would be tough to support for this. There is also
Hardware Replication with the Production SAN to the SAN at
the DR site. That's not something I'm very familar with
or how it works exactly. Is Hardware Replication a better
way to do it? I've been told that they may need real time
updates to the DR Server in the future and I'm not sure if
that is realistic or not. I still think log shipping is
best. I could take it down to shipping every 15 min if
needed. Could someone post some opinions/suggestions for
me.
Thanks,
Van
Van,
If your DR server is only used for DR purposes this is what I would do - you should have less than 30 secs down time if you try what I propose.
1) Place your SQL Server data and log files into two separate LUNs on the SAN at your primary site. Start SQL Server and make sure it's running normally. You may even want to try putting the OS on the SAN too - up to you.
2) Image the server using an imaging tool and restore the image to your secondary server and attach it to your secondary SAN at your DR site.
3) Stop SQL Server service on the secondary server.
4) Set up replication at the block level using SAN replication software - replicate the LUNs that the log and data files reside on. You will need to do a full mirror to start with, and then stream replication from then on.
5) When the primary site goes down, make sure that replication gets stopped (it will stop anyway), perform a failover on your SAN to the secondary site, then start SQL Server on your secondary server. You will find that SQL Server will start normally and
there will be much, much, much less data loss than log shipping. Make sure that replication cannot go from source --> secondary should the source come back online, otherwise you will get data file corruption.
6) Step 5 can be scripted so that you can just "push a button" to fail over. The entire process to fail over shouldn't take more than 30 seconds - 1 minute.
The failback process once the primary is available again is a reverse of the above but in a more controlled manner.
I have implemented this solution across the atlantic and it works very, very well indeed, as long as the process is scripted with all manual steps removed. Also, make sure a DR test is performed regularly. Say goodbye to log shipping.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Best practice for DR/Standby server

We use SQL2000 Standard Edition SP3a. We have a
Production Server and a DR Server that is off site.
Currently I have SPs coded that do log shipping to the DR
Server every hour from the log backups on the production
server. Is this the best way to keep the DR Server up to
date? I've explored Replication, but it seemed much more
complex and structure changes don't get replicated which
means a new shipment of the entire Production DBs anytime
a structure change is made. It seems like Replication
would be tough to support for this. There is also
Hardware Replication with the Production SAN to the SAN at
the DR site. That's not something I'm very familar with
or how it works exactly. Is hardware Replication a better
way to do it? I've been told that they may need real time
updates to the DR Server in the future and I'm not sure if
that is realistic or not. I still think log shipping is
best. I could take it down to shipping every 15 min if
needed. Could someone post some opinions/suggestions for
me.
Thanks,
VanVan,
If your DR server is only used for DR purposes this is what I would do - you
should have less than 30 secs down time if you try what I propose.
1) Place your SQL Server data and log files into two separate LUNs on the SA
N at your primary site. Start SQL Server and make sure it's running normally
. You may even want to try putting the OS on the SAN too - up to you.
2) Image the server using an imaging tool and restore the image to your seco
ndary server and attach it to your secondary SAN at your DR site.
3) Stop SQL Server service on the secondary server.
4) Set up replication at the block level using SAN replication software - re
plicate the LUNs that the log and data files reside on. You will need to do
a full mirror to start with, and then stream replication from then on.
5) When the primary site goes down, make sure that replication gets stopped
(it will stop anyway), perform a failover on your SAN to the secondary site,
then start SQL Server on your secondary server. You will find that SQL Serv
er will start normally and
there will be much, much, much less data loss than log shipping. Make sure t
hat replication cannot go from source --> secondary should the source come b
ack online, otherwise you will get data file corruption.
6) Step 5 can be scripted so that you can just "push a button" to fail over.
The entire process to fail over shouldn't take more than 30 seconds - 1 min
ute.
The failback process once the primary is available again is a reverse of the
above but in a more controlled manner.
I have implemented this solution across the atlantic and it works very, very
well indeed, as long as the process is scripted with all manual steps remov
ed. Also, make sure a DR test is performed regularly. Say goodbye to log shi
pping.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk