Publishing Your Database in SQL Server

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.
  
1.gif

You will get the Publish database wizard

2.gif
  
On clicking the next, you will get the database to be pulished. select the database and click "next" button.

3.gif
 
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.

4.gif

In the previous window, I have selected tables. So the next window will list down all the tables available in the database.

5.gif
  
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.

6.gif
 
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.

7.gif
 
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.

8.gif

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. 1. Drop existing objects in script -> On selecting "True" drop statements will be included in the script.
  2. 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.
  3. 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"
  4. Script for target database - You will have the option to select the target database type. Is it SQL Server 2005 or SQL Server 2008.
  5. Type of objects to publish - Schema only or Data only or Schema and Data options.
9.gif
  
On clicking next button -> Summary of all the actions done by us in the previous windows will be displayed. 

10.gif

On clicking finish button. Fetching the scripts from the source -> connect to the remote server -> Execute in the remote server.

11.gif


Similar Articles