f



Updating online SQL 2005 database from local database

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
0
jfm1 (26)
1/30/2007 10:32:40 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

3 Replies
1091 Views

Similar Articles

[PageSpeed] 11

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 <jfm@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

0
jfm1 (26)
1/30/2007 1:17:19 PM
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/View.aspx?title=Database%20Publishing%20Wizard
Scott Guthrie guides:
http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

Regards,

Plamen Ratchev
http://www.SQLStudio.com


0
Plamen (618)
1/30/2007 2:15:32 PM
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"
<Plamen@SQLStudio.com> wrote:

>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/View.aspx?title=Database%20Publishing%20Wizard
>Scott Guthrie guides:
>http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
>http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx
>
>Regards,
>
>Plamen Ratchev
>http://www.SQLStudio.com
>

0
jfm1 (26)
1/30/2007 7:04:48 PM
Reply: