I have created a database with objects. Now, I need to push this database to the external world. How can I achieve it?
SQL Server is providing a very nice option of publishing the database objects using scripting the entire database + publishing to the external database through internet.
Let's see how can we achieve it,
After creating your objects, right click on the database and click tasks ->Publish using webservice option.
You will get the Publish database wizard
On clicking the next, you will get the database to be pulished. select the database and click "next" button.
On clicking the check box "Publish all objects in the selected database" you wont get the below window. By default, it will select all the objects and bypass the below window.
In the previous window, I have selected tables. So the next window will list down all the tables available in the database.
Now, we need to select the target database. Database, where we need to push the schema and data. If we have already registered the database, we can select it or else click "Manage" button.
You will get the hosting providers window -> remove the default loaded one and click new button. You will get a window to register the destination server.
You should give the webserive address provided by the provider and the credentials to access this secure webservice.
To select the database click new -> provide the server + database + authentication information and click "Ok" button.
On clicking the "Ok" button. the next window will start creating the scripts in the source and try to connect and execute the same in the destination database.
- 1. Drop existing objects in script -> On selecting "True" drop statements will be included in the script.
- Publish using transaction -> Entire script will be loaded with transactions logic. Entire script will be executed and if there is any issue entire script will get rollbacked.
- Schema qualify - True will provide the entire objects with fully schema qualified one else only object name will be used. Advisable to have this option as "True"
- Script for target database - You will have the option to select the target database type. Is it SQL Server 2005 or SQL Server 2008.
- Type of objects to publish - Schema only or Data only or Schema and Data options.
On clicking next button -> Summary of all the actions done by us in the previous windows will be displayed.
On clicking finish button. Fetching the scripts from the source -> connect to the remote server -> Execute in the remote server.