NIC SQL Server Support for Web Hosting

Web Hosting is proud to offer Microsoft SQL Server 2005/2008 support for all of its hosting customers.  SQL Server 2005 and SQL Server 2008 are the industry standard high-performance, reliable, scalable database solution for all of your Internet and e-business needs.

 You have complete remote control of your database using familiar Microsoft management tools, and your SQL Server database is maintained and monitored 24 hours a day.  You have enough things to worry about.  The reliability and performance of your data shouldn't be one of them

This page answers three commonly asked questions:

1. How do I set up my Machine to connect to SQL Server database at NIC Premises?
2. What's the best way to transfer data that I already have to my Web Hosting SQL Server database?
3. What's the precautions one have to take while importing 
   data /tables.

How do I set up my PC to connect to SQL Server database at NIC?

Setting up your PC to connect to your SQL Server database at NIC Web Hosting is necessary before you can do anything else with your SQL Server database from your PC.  Please note that you must have already installed the Client Connectivity portion of SQL Server 2005 with SP2 and SQL Server 2008 on your local machine before continuing.

1. Make sure that you are connected to the Internet and have the TCP/IP protocol enabled. Port to use is 1433 with IP - SQL8.


2. To test connection port is open or not to your SQL Server from your PC, you should Type 'cmd' or 'command' in RUN Menu of Windows:



Click on 'OK' button then there will be appear window's Command Prompt. Now perform the following operations

1. In command prompt Type 'telnet <Server IP> 1433'.




2. Now Press Enter and then command prompt window will appear looks like below.



This means the port 1443 is open for your PC.

NOTE: If your action being failed in step (b) above, verify that your entered the <Server IP> correctly in step (a) above.  Also, verify that you are not trying to connect to the NIC SQL Server database from behind a firewall.  Many firewalls by default do not allow TCP/IP traffic on different port. You are certain you are not behind a firewall.

3. To test your connection to your SQL Server database Remotely from your PC, you should open the ' SQL Server Management Studio' and connect it with database engine as shown in figure below and then perform the following steps:



1. In the "Connect to Server" dialog box that appears, Select 'Database Instance Name' e.g. SQL8, as the SQL Server to connect to.  If Instance Name isn't listed in the drop-down box, Then Type <Database Server IP>
2. Select the "SQL Server authentication" option, anb) Select the "SQL Server authentication" option, and enter your SQL Server username and password supplied to you by NIC (see screenshot below).  Click "OK" to connect to the SQL Server database.





3. After your connected, enter the following query and click on <Excute> in the toolbar to execute it:



If you are able to successfully execute this query, then you have verified your client connection to your database, as well as your SQL Server username and password.

What's the best way to transfer data that I already have to my NIC SQL Server database?

If you have data you want to transfer to your new database, the best way to do this is by using the "Import and Export Data" program, found in the Microsoft SQL Server program group.  Follow these steps to transfer data using the SQL Server Import and Export Wizard:

1. Verify that you able to successfully connect to you SQL Server database by performing steps in the FAQ section "How do I set up my PC to connect to my SQL Server database at NIC?
2. Run the "Import and Export Data" program, located in the 'Microsoft SQL Server Management Studio'



3. Click on 'Export Data' Menu then there will be appear the introduction screen as shown below:




4. Click "Next" from the introduction screen, and on the "Choose a Data Source" screen, select the source where your data currently resides.  The screenshot below illustrates selecting the 'abc'



5. The options you will have to fill in will vary depending on the data source type you select in the "Source" drop-down list box.  For example, you'd select the default "SQL Native Client" for SQL Server. If you were transferring data from a local SQL Server.
6. Click "Next" to continue.


7. On the "Choose a Destination" screen, leave the default "SQL Native Client for SQL Server" as the Destination.  Select "Server Name" or TYPE <Server IP> for the Server to copy data to.  Select the "Use SQL Server authentication" connection option, and enter your SQL Server username and password supplied to you by NIC Web Hosting.  Then click the "Refresh" button to retrieve a list of databases from the NIC SQL Server, and select your database from the "Database" drop-down list box.  See the screenshot below for an example




8. Click "Next" to continue.from one or more tables or views" from the source database" and click "Next" to continue




9. If you see the "Select Source Tables" screen, you can specify which tables you would like to copy to your NIC SQL Server.  Unless you are an expert user, leave the defaults for the "Destination Table" and "Transform" columns.  Click "Next" to continue.



10. Click on 'Edit Mapping' button to set Data transfer settings. By Default Destination Schema Name will be 'dbo'. Here you can change the schema name as well as appropriate schema is created for the username. You can choose other options as described below options:
1. if there is a table already exiting with the same name that you want to export and if you want to drop that existing table and want to recreate it then check the option “ Drop and recreate new destination tables".
2. if you don't want to drop the existing table but want to delete the rows in existing table then you can check the option “ Delete rows in existing destination table.
3. if there is an Identity existing in the table that you want to export then you have to check the option “Enable identity insert"

Click ok to finish Transfer Settings.




11. On the "Save and Execute Package" screen, leave the default "Execute Immediately" selection checked, and click "Next" to continue.




12. Lastly, on the 'Complete the wizard' screen, review your selections and click "Finish" to execute the data transfer.

    
    
    

    The data transfer will perform some operations that will be visual as shown in following screen.

    
    
    

    Finally you will have a message ' The execution was successful' on the screen with summary.

    
    
    

    Now Click on 'Close' to close the wizard.

What's the precautions one have to take while importing data /tables :-

1. By default table contents get appended so always delete all records if you are uploading full contents.
2. Always create indexes after uploading the data. Query Window should be used for this purpose.

Some Useful SQL Queries:

SQL Query to Update Statistics of a Database:
USE <Database_Name>
GO
DBCC CHECKDB (Database_Name)
GO
EXEC sp_updatestats

SQL Query to Rebuilt Indexes in a Database:
USE <Database_Name>
GO
sp_MSforeachtable "DBCC DBREINDEX ('?')"
GO

^ TOP ^