Updating online SQL 2005 database from local database

[John Morgan]

I have my first small SQl Server 2005 database developed on my local
server and I have also its equivalent as an online database.

I wish to update the local database (using and asp.net interface) and
then to upload the data (at least the amended data, but given the
small size all data should be no trouble) to the online database.

I think replication is the straight answer but I have no experience of
this and I am wondering what else I might use which might be less
complicated. One solution is DTS (using SQL 2000 terms) but i am not
sure if I can set this up (1) to overwrite existing tables and (2) not
to seemingly remove identity attributes from fields set as identities.

I know there are other possibilities but I would be glad of advice as
to the likely best method for a small database updated perhaps once
weekly or at less frequent intervals,

Best wishes, John Morgan

[John Morgan]

Since my first message I have investigated replication further and it
does not seem as complicated as first thought and I have successfully
carried out a snapshot replication on my local server.

the main disadvantage of replication now seems to be that my online
sql database provider charges $50 for providing a replication facility
(presumably for enabling a push subscription)

The payment is not significant providing replication is the way to to
go for the occasional updating of an online server from my local
server.

Best wishes, John Morgan
Tue, 30 Jan 2007 10:32:40 GMT, John Morgan <jf*@XXwoodlander.co.uk>
wrote:

>I have my first small SQl Server 2005 database developed on my local
server and I have also its equivalent as an online database.

I wish to update the local database (using and asp.net interface) and
then to upload the data (at least the amended data, but given the
small size all data should be no trouble) to the online database.

I think replication is the straight answer but I have no experience of
this and I am wondering what else I might use which might be less
complicated. One solution is DTS (using SQL 2000 terms) but i am not
sure if I can set this up (1) to overwrite existing tables and (2) not
to seemingly remove identity attributes from fields set as identities.

I know there are other possibilities but I would be glad of advice as
to the likely best method for a small database updated perhaps once
weekly or at less frequent intervals,

Best wishes, John Morgan

[Plamen Ratchev]

Hi John,

There are different ways and tools to accomplish what you need based on what
you feel comfortable with and what fits your update schedule:

1) As you mention replication will work. However, since you are updating the
data once a week or less frequently it might not be worth the effort to set
up and maintain replication.
2) The analog to DTS in SQL Server 2005 is SSIS (SQL Server Integration
Services). A simple way to create SSIS package to transfer your data is to
start the SQL Server Import and Export Wizard (just right click your
database and select Tasks, Export Data...) and follow the steps. On the
Select Source Tables and Views screen you can click "Edit Mappings". There
you have options to drop and re-create the destination tables and to enable
identity insert. At the end you can execute immediately or save it as SSIS
package to execute later.
3) Use the Database Publishing Wizard. It is designed for deployment of
local databases to remote hosting environments. The tool has both graphical
and command line interfaces, and there is a way that you can update your
database via a Web page. Here are more details:
Database Publishing Wizard:
http://www.codeplex.com/sqlhost/Wiki...shing%20Wizard
Scott Guthrie guides:
http://weblogs.asp.net/scottgu/archi...nt-part-1.aspx
http://weblogs.asp.net/scottgu/archi...-database.aspx

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[John Morgan]

Thank yuou Plamen,

I am really grateful for your comprehensive reply, just what I was
hoping for. That gives me plenty to work on,

Beest wishes, John

On Tue, 30 Jan 2007 14:15:32 GMT, "Plamen Ratchev"
<Pl****@SQLStudio.comwrote:

>Hi John,

There are different ways and tools to accomplish what you need based on what
you feel comfortable with and what fits your update schedule:

1) As you mention replication will work. However, since you are updating the
data once a week or less frequently it might not be worth the effort to set
up and maintain replication.
2) The analog to DTS in SQL Server 2005 is SSIS (SQL Server Integration
Services). A simple way to create SSIS package to transfer your data is to
start the SQL Server Import and Export Wizard (just right click your
database and select Tasks, Export Data...) and follow the steps. On the
Select Source Tables and Views screen you can click "Edit Mappings". There
you have options to drop and re-create the destination tables and to enable
identity insert. At the end you can execute immediately or save it as SSIS
package to execute later.
3) Use the Database Publishing Wizard. It is designed for deployment of
local databases to remote hosting environments. The tool has both graphical
and command line interfaces, and there is a way that you can update your
database via a Web page. Here are more details:
Database Publishing Wizard:
http://www.codeplex.com/sqlhost/Wiki...shing%20Wizard
Scott Guthrie guides:
http://weblogs.asp.net/scottgu/archi...nt-part-1.aspx
http://weblogs.asp.net/scottgu/archi...-database.aspx

Regards,

Plamen Ratchev
http://www.SQLStudio.com