replication strategy

Hi all,
First of all, I'm a newbi to databases.
Anyway, I am about to start writing a program that is handling
customers, invoices and so on. A decision has been taken to use Sql
Server 2000 (MSDE at customers)as RDBMS. It has also been decided that
the development should be devided into several iterations. Adding more
functionality at each iteration.
My problem is as follows: How do I distribute the design (tables,
stored proc, columns, keys etc) of the database when releasing new
versions of the program?
Let's assume that a customer table has the following columns "Id",
"Name" and "Phone". In the next version of my program I have added
funcionality to send e-mails to a customer so I have now added a
column called "Email" to the customer table.
This new column, some how, has to be inserted into the database before
the new version of the program can be used. I want to add this column
to the table without loosing any existing data in the table. Which is
the best strategy to do this? I have played around with SQLDMO,
looking at my own development database to create some sort of
definition file that could be used to alter table's. It feel's like
there is a better solution than to manualy go through all objects
(table, column, keys, Stored proc. etc) that I want to replicate.
Is there any good websites on this subject?
The program will be used at over 100 customers. Each customer should
run a installation program that will take care of updating the
database so that they can run the version of my program.
Br
JonasYou are really talking about distributing database
upgrade, not SQL Server replicaiton.
I'd just perform all (or most) database updates in some T-
SQL scripts, include the scripts in the install package,
have the install program control teir execution, and make
sure that the install program know how to check for
success/failure conditions and act accordingly.
I don't mean to sugges that you include all database
changes in a single T-SQL script. In many cases, that
wouldn't lead to robust error checking.
Linchi

quote:

---

>--Original Message--
>Hi all,
>First of all, I'm a newbi to databases.
>Anyway, I am about to start writing a program that is

---

handling

quote:

---

>customers, invoices and so on. A decision has been taken

---

to use Sql

quote:

---

>Server 2000 (MSDE at customers)as RDBMS. It has also been

---

decided that

quote:

---

>the development should be devided into several

---

iterations. Adding more

quote:

---

>functionality at each iteration.
>My problem is as follows: How do I distribute the design

---

(tables,

quote:

---

>stored proc, columns, keys etc) of the database when

---

releasing new

quote:

---

>versions of the program?
>Let's assume that a customer table has the following

---

columns "Id",

quote:

---

>"Name" and "Phone". In the next version of my program I

---

have added

quote:

---

>funcionality to send e-mails to a customer so I have now

---

added a

quote:

---

>column called "Email" to the customer table.
>This new column, some how, has to be inserted into the

---

database before

quote:

---

>the new version of the program can be used. I want to add

---

this column

quote:

---

>to the table without loosing any existing data in the

---

table. Which is

quote:

---

>the best strategy to do this? I have played around with

---

SQLDMO,

quote:

---

>looking at my own development database to create some

---

sort of

quote:

---

>definition file that could be used to alter table's. It

---

feel's like

quote:

---

>there is a better solution than to manualy go through all

---

objects

quote:

---

>(table, column, keys, Stored proc. etc) that I want to

---

replicate.

quote:

---

>Is there any good websites on this subject?
>The program will be used at over 100 customers. Each

---

customer should

quote:

---

>run a installation program that will take care of

---

updating the

quote:

---

>database so that they can run the version of my program.
>Br
>Jonas
>.
>

---

Replication strategy

Hello,
I have a SQL 2000 database which stores details of consumers input via
our website.
I am currently trying to configure some off-site disaster recovery,
which includes synchronising our database (including tables, data and
stored procedures) to another server.
We are limited on bandwith at the moment due to our office locations,
so ideally I need to keep the data transfers to a minimum. I am happy
for the transfer to take place overnight.
My question is, which replication model should I choose. I first
thought that Snapshot would be best, but the only problem is that the
configuration seems to indicate that it would drop and re-create each
table on the desitination server. This obvisously isn't very scalable,
as more data in the database will mean that the transfer wil take
progressively longer.
Is there a better way to do it? I guess the ideal way would be to only
copy the changes made, each night. Can I do this with transactional
replication?
Cheers,
Andrew
Yes - transactional replication will have the least impact on your limited
bandwidth, so toher things considered I'd choose this. I have an article on
replication times on www.replicationanswers.com that you might find helpful.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
Will the storing of a whole day's transactions have any performance
implications on my SQL server?
|||Typically no, but if you find by doing some monitoring that the production
server is struggling, you can use a remote distributor. Before going to this
step, you can use pull subscriptions which'll offload some of the work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the advice. I currently have the distribution on the
production server and am using push subscriptions, in order to make it
easier to administer the whole thing.
I will try your suggestions out if we run into problems.
Many thanks

Replication Strategy

I have an app that has this design: MasterDB contains all the templates used
by the app and a record of all DealDB's created, DealDb contains just the
data, imported from various sources. A new DealDB is created for every new
"deal". The app works fine in the office, now I want to extend it for out of
office(in-transit) use. My idea is to install MSDE on the users PC's and:
1. When the deal is marked in-transit the app will not allow anyone in the
office access to it. So, I want to either detach/attach the DealDB to the
local MSDE or use Bacup/Restore to local MSDE and the same coming back to the
office. Is one better/safer/less error prone then the other? Can I attach a
DB if it was never detached/ can I restore a DB to server that was not the
one it was backed up on?
2. The MasterDB will have to be replicated to local MSDE when a user puts a
deal in-transit. When the user is back in the office all changes to the
local(MSDE)MasterDB will need to be replicated back to the network version.
While the DealDB will simply be either attached or restored back to the
network.
I guess I would use merge replication for the MasterDb? Going to local would
I use Push or Pull subscription, and what about going back to network Push or
Pull?
Can someone help me out with the details, is there a script I can use?
Thanks!
Yes you can set transformations in transactional replication
"mikeb" wrote:

> I have an app that has this design: MasterDB contains all the templates used
> by the app and a record of all DealDB's created, DealDb contains just the
> data, imported from various sources. A new DealDB is created for every new
> "deal". The app works fine in the office, now I want to extend it for out of
> office(in-transit) use. My idea is to install MSDE on the users PC's and:
> 1. When the deal is marked in-transit the app will not allow anyone in the
> office access to it. So, I want to either detach/attach the DealDB to the
> local MSDE or use Bacup/Restore to local MSDE and the same coming back to the
> office. Is one better/safer/less error prone then the other? Can I attach a
> DB if it was never detached/ can I restore a DB to server that was not the
> one it was backed up on?
> 2. The MasterDB will have to be replicated to local MSDE when a user puts a
> deal in-transit. When the user is back in the office all changes to the
> local(MSDE)MasterDB will need to be replicated back to the network version.
> While the DealDB will simply be either attached or restored back to the
> network.
> I guess I would use merge replication for the MasterDb? Going to local would
> I use Push or Pull subscription, and what about going back to network Push or
> Pull?
> Can someone help me out with the details, is there a script I can use?
> Thanks!
sql

replication strategy

Hi all,
First of all, I'm a newbi to databases.
Anyway, I am about to start writing a program that is handling
customers, invoices and so on. A decision has been taken to use Sql
Server 2000 (MSDE at customers)as RDBMS. It has also been decided that
the development should be devided into several iterations. Adding more
functionality at each iteration.
My problem is as follows: How do I distribute the design (tables,
stored proc, columns, keys etc) of the database when releasing new
versions of the program?
Let's assume that a customer table has the following columns "Id",
"Name" and "Phone". In the next version of my program I have added
funcionality to send e-mails to a customer so I have now added a
column called "Email" to the customer table.
This new column, some how, has to be inserted into the database before
the new version of the program can be used. I want to add this column
to the table without loosing any existing data in the table. Which is
the best strategy to do this? I have played around with SQLDMO,
looking at my own development database to create some sort of
definition file that could be used to alter table's. It feel's like
there is a better solution than to manualy go through all objects
(table, column, keys, Stored proc. etc) that I want to replicate.
Is there any good websites on this subject?
The program will be used at over 100 customers. Each customer should
run a installation program that will take care of updating the
database so that they can run the version of my program.
Br
JonasYou are really talking about distributing database
upgrade, not SQL Server replicaiton.
I'd just perform all (or most) database updates in some T-
SQL scripts, include the scripts in the install package,
have the install program control teir execution, and make
sure that the install program know how to check for
success/failure conditions and act accordingly.
I don't mean to sugges that you include all database
changes in a single T-SQL script. In many cases, that
wouldn't lead to robust error checking.
Linchi
>--Original Message--
>Hi all,
>First of all, I'm a newbi to databases.
>Anyway, I am about to start writing a program that is
handling
>customers, invoices and so on. A decision has been taken
to use Sql
>Server 2000 (MSDE at customers)as RDBMS. It has also been
decided that
>the development should be devided into several
iterations. Adding more
>functionality at each iteration.
>My problem is as follows: How do I distribute the design
(tables,
>stored proc, columns, keys etc) of the database when
releasing new
>versions of the program?
>Let's assume that a customer table has the following
columns "Id",
>"Name" and "Phone". In the next version of my program I
have added
>funcionality to send e-mails to a customer so I have now
added a
>column called "Email" to the customer table.
>This new column, some how, has to be inserted into the
database before
>the new version of the program can be used. I want to add
this column
>to the table without loosing any existing data in the
table. Which is
>the best strategy to do this? I have played around with
SQLDMO,
>looking at my own development database to create some
sort of
>definition file that could be used to alter table's. It
feel's like
>there is a better solution than to manualy go through all
objects
>(table, column, keys, Stored proc. etc) that I want to
replicate.
>Is there any good websites on this subject?
>The program will be used at over 100 customers. Each
customer should
>run a installation program that will take care of
updating the
>database so that they can run the version of my program.
>Br
>Jonas
>.
>