MS Access ODBC DSNless connection string
<font size="1">Driver={Microsoft Access Driver (*.mdb)};Dbq=e:\inetpub\username\data\yourdatabase.mdb;Uid=Admin;Pwd=pass; </font>

ex. daisysflowershop.com
<font size="1"><%
Dim connTest
connTest = "Driver={Microsoft Access Driver (*.mdb)};Dbq=e:\inetpub\daisysflowershop\data\flowerdb.mdb;"
%></font>

MS Access OLE DB DSNless connection string
<font size="1">Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\inetpub\username\data\yourdatabase.mdb;User Id=admin;Password=pass; </font>

ex. daisysflowershop.com
<font size="1">

Review the following Microsoft links for information on this.

http://www.microsoft.com/sql/prodinfo/previousversions/msde/prodinfo.mspx

http://www.microsoft.com/sql/editions/express/default.mspx

Note: SQL Server 2005 Express Edition is the next version of MSDE and is free.

The error reads below when submitting a form to my database:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

This error is due to your database not having 'write' permissions. You can resolve this in one of three ways:

1. Move your database to the 'data' folder. This folder is pre-defined with 'write' permissions.

2. Use the Enterhost Control Panel to give the folder, where your database currently resides, 'write' permissions.

3. Email support@enterhost.com and ask that your database be given 'write' permissions to the folder with the database.

dsn, datasource, data source, mdb, asp

We have created a Flash demo that will walk you through each step needed to define and connect to SQL using Enterprise Manager. Visit the following link and choose the Database Category and then select 'Enterprise Manager - Server Registration':

http://www.enterhost.com/support/dedicated/demos/index.cfm#

If you do not have a copy of Enterprise Manager, you can order an evaluation copy from Microsoft by clicking here.

SQL 2000

You can connect using any of the following Microsoft tools:

Enterprise Manager
Query Analyzer
Access XP/2002

You may run into the following error in .NET when connecting to a database:

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

This is most likely because you have exceeded the maximum pool size for your SQL connection. In ADO.NET, connection strings are pooled by default. To avoid this error, you can use one of the following solutions:

1. Raise the Max Pool Size. You can set the minimum and maximum pool sizes by adding the following to your connection string:

"min pool size=1; max pool size=50"

2. Disable connection pooling. Add the following to your connection
string to disable connection pooling:

"pooling=false"

However, Microsoft highly discourages you disable connection pooling for performance reasons. See the following URL for more information on connection pooling in
ADO.NET:

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/connectionpooling.aspx

connection pooling, sqlconnection

How to change your password using Enterprise Manager.

1. Log into your SQL Server using Enterprise Manager.
2. Expand the Security folder.
3. Click Logins.
4. Right-click your user name in the list of logins and select Properties.
5. In the password field type a new password and press OK.
6. A new window will open and prompt you for your old password and the new password again.
7. Congratulations! You have now changed your password using Enterprise Manager.

How to change your password using Query Analyzer.

1. Login to your SQL Server using Query Analyzer.
2. You will use the sp_password stored procedure to update your password.
3. You can use the following syntax:

EXEC sp_password 'OldPassword', 'NewPassword'

For example:
EXEC sp_password 'B3r12-36', 'Xj7-IpSca'

4. Congratulations! You have now changed your password using Query Analyzer.

db

You can use Enterprise Manager to copy your database from a local server to ours.

1. Open Enterprise Manager and login to the server you will be copying the database from (your local server).

2. Find your database and right-click on it.

3. Click All Tasks and Export Data

4. Click Next

5. Click Next

6. Enter the information for the SQL server that you will be copying to.

7. Select "copy objects and data...." and click next. Please note that if you select "tables and views" you will lose data during the transfer.

8. Uncheck "Use Default Options"

9. Click Options, and under the Security section, clear the checks from all the boxes and then click OK

10. Click Next

11. Click Next to complete the transfer.

db transfer

We can complete the database restore for you. Upload the .bak file to your server and send an email to support@enterhost.com with the following information:

Server name

Location of .bak file on your server

SQL Server

SQL Database name

SQL, restore, backup, database

We do not offer Microsoft Access. Our suggestion for getting it on the server would be to purchase the software and copy the contents of the CD to your server via ftp. You can install additional applications using this method.

third party, office

Yes, you have the ability to install this software on your server. It is a free download from Microsoft that will give you as a developer, basic functionality of SQL server. The software will run on both Windows 2003 Web Edition and Windows 2003 Standard Edition.

Please note that these editions of SQL are basic and do have hardware and software limitations. You can find more information from the following Link to Microsoft.

http://www.microsoft.com/sql/editions/express/features.mspx

This software is not installed by default, so we will not be able to support it for you. If you have questions on the installation, please refer to the documentation provided by Microsoft. Please note when installing these applications, to install them on the E: of your server to ensure you will not cause low disk space issues on the system partition (C:)

sql, 2005, msde, server express

SQL Server 2005 requires Microsoft SQL Server Management Studio, the updated version of Enterprise Manager.

The Express version is now available as a free download from Microsoft.

http://www.microsoft.com/sql/default.mspx

You can refer to the support demo section of our support center for specific instructions on how to connect using this software

SQL Server 2005 does not support Enterprise Manager, and requires Microsoft SQL Server Management Studio, the updated version of Enterprise Manager.

The Express version is now available as a free download from Microsoft.

http://www.microsoft.com/sql/default.mspx

sql 2000, enterprise manager, sql management studio

Click start - administrative tools - ODBC

Click the system tab at the top and click add

Choose the type of driver that you will be using and click finish

Type in the DSN name you have or will reference in your code and click select. 

Browse to the correct database and click ok

Click ok and ok to complete the wizard

access, data source

Before restoring a database, we strongly suggest making a backup of the current database. You can refer to our support center for more details.

Login to the SQL server via Remote Desktop or Terminal services

Restore Database

Open Enterprise Manager
Expand Microsoft SQL Servers <
Expand SQL Server Group
Expand (local) and databases
Right-click on the database you would like to restore Click 'All Tasks' and 'Restore Database'

Select the option 'From Device'
Click 'Select Devices'
Click 'add' and browse the backup file
Click 'ok'
Click the 'options' tab at the top
Check the option 'Force restore over the existing database'

Rename the location and name of the files to the following format. Replace databasename with the name of the database on the new SQL server.

E:\Program Files\Microsoft SQL Server\MSSQL\data\databasename_Data.MDF
E:\Program Files\Microsoft SQL Server\MSSQL\data\databasename_Log.LDF
Click 'ok'

Restore permissions

In Enterprise Manager, expand 'security' and click 'logins'
Find the user associated with the database you performed the restore on.
Right-click the user and click 'properties'
Click the 'Database Access' tab at the top
Check the database to grant permissions
Check 'db_owner'
Click 'ok'

sql 2000, restore, backup, .bak

The following guide requires Microsoft SQL Server 2005 to be installed on your dedicated server.

If you do not have SQL Server installed on your dedicated server, you can request SQL Server or additional databases to be added to your package. You can contact our sales team at sales@enterhost.com for more information on adding these services.

Open 'SQL Server Management Studio' on the desktop of your SQL server.

Click 'Connect' to connect with the default information

Create Database

Click the Maximize windows icon at the top of the window (next to the X)

Right-click on 'Databases' and click 'New Database'

Type the database name at the top

Under the column 'Autogrowth' and next to 'By 1MB, unrestricted growth' click the three periods (...) to bring up the Autogrowth settings

Under 'File Growth' select 'In Megabytes' and leave the number '1' in the box.

Under 'Maximum file size' select the option 'Restrict file growth (MB)'

In this option, select a maximum size for the database. This will prevent the database from using all the available space on the server. This is not critical for the data files, but it is for the log files. The log files will continue to grow and take up unnecessary space, and eventually cause disk space issues on the server. We would suggest using a number quite a bit larger then your database to allow for growth. If you are transferring from our shared environment a number from 100-200mb should be sufficient. If you are unsure of your database size you can enter 1000mb for a safe number.

Under the column 'Autogrowth' and next to 'By 10 percent, unrestricted growth' click the three periods (...) to bring up the Autogrowth settings

Under 'File Growth' select 'In Megabytes' and leave the number '1' in the box.

Under 'Maximum file size' select the option 'Restrict file growth (MB)'

Click 'ok'

Create User

Expand the server and then 'Security'
Right-click on 'Logins' and click 'New Logins'
Type the username at the to next to 'Login name'
In SQL Server Management Studio, expand 'Security' and click 'Logins'
Right-click 'logins' and click 'New Login'
Type the name of the database user at the top.
Under 'Authentication' select 'SQL Server Authentication'
Type in a password for the database user and confirm
Check 'Enforce password policy'
Uncheck 'Enforce password expiration'

Under the 'Defaults' section, change the Database to the database this user will be assigned to

Click 'User Mapping' on the left navigation pane Check the new database Check 'db_owner' and any other required permissions

Click 'ok'

For any additional databases you create, you can use this user or create new users. If you wish to use this user, you can right-click the user and click 'properties'. Under the 'user mapping' section, add the permissions for any additional databases.

sql 2005, server, new database

The following guide requires Microsoft SQL Server 2000 to be installed on your dedicated server.

If you do not have SQL Server installed on your dedicated server, you can request SQL Server or additional databases to be added to your package. You can contact our Sales Team at sales@enterhost.com for more information on adding these services.

Create Database

Open 'Enterprise Manager' on the desktop of your SQL server Expand 'Microsoft SQL Servers' and 'SQL Server Group'

Expand 'local'
Right-click on 'Databases' and click 'New Database'
Type the database name at the top
Click the 'data files' tab at the top
Check 'Automatically grow file'
Under 'File Growth' select 'In Megabytes' and leave the number '1' in the box.
Under 'Maximum file growth' select the option 'Restrict file growth (MB)'

In this option, select a maximum size for the database. This will prevent the database from using all the available space on the server. This is not critical for the data files, but it is for the log files. The log files will continue to grow and take up unnecessary space, and eventually cause disk space issues on the server. We would suggest using a number quite a bit larger then your database to allow for growth. If you are transferring from our shared environment a number from 100-200mb should be sufficient. If you are unsure of your database size you can enter 1000mb for a safe number.

Click the 'Transaction Log' tab at the top Check 'Automatically grow file'
Under 'File Growth' select 'In Megabytes' and leave the number '1' in the box.
Under 'Maximum file growth' select the option 'Restrict file growth (MB)' enter 100mb. This will limit your log file size and keep it from filling the hard drive.
Click 'ok'

Create User

In Enterprise Manager, expand 'Security' and click 'Logins'
Right-click 'logins' and click 'New Login'
Type the name of the database user at the top.
Under 'Authentication' select 'SQL Server Authentication'
Type in a password for the database user Under the 'Defaults' section, change the Database to the database this user will be assigned to.

Click the 'Database Access' tab at the top Find the database or databases this user will have access to Place a check mark next to the database under 'Permit'

In the 'Database roles', check 'db_owner' and click 'ok'
Confirm the password and click 'ok'

For any additional databases you create, you can use this user or create new users. If you wish to use this user, you can right-click the user and click 'properties'. Under the 'database access' tab, add the permissions for any additional databases.

sql 2000, new database

You can use any database that has an ODBC driver. This means that you can use any version of Access, because they all have ODBC drivers.

Step 1) Click Start> Settings> Control Panel.
Step 2) Click on the ODBC icon.
Step 3) Click on the System DSN Tab.
Step 4) Click on the ADD button, then select the type of database you are using, (Access or SQL).
Step 5) Type your DSN name in the Data Source Name box, then click the select button.
Step 6) Find your database and select it from the box on the left side once it appears.
Step 7) Once you have selected the database you will notice a path to the database will appear on the ODBC Microsoft Access Setup screen.
Step 8) Click the OK button
Step 9) This will bring you back to the first screen and you will see your newly created DSN, click OK and you're set.

In SQL Server 7.0, in SQL Server 2000, and in SQL Server 2005, the following messages can appear when the database log becomes full:

Error: 9002, Severity: 17, State: 2
The log file for database '%.*ls' is full

Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

To clear a transaction log, execute the command DUMP TRANSACTION databasename.
However, this does not work when your transaction log is full, as dumping the
transaction log is also a logged operation. In order to clear your transaction
log after it is full, include the WITH NO_LOG clause.

So, to correct this error, use Microsoft's Query Analyzer to execute the following SQL command against your database
while logged in as the DBO:

DUMP TRANSACTION databasename WITH NO_LOG

This will clear your transaction log and correct the error you were receiving.

Yes, as long as you have a copy you can connect to SQL Server using Enterprise Manager.

If you do not have a copy of Enterprise Manager, you can order an evaluation copy from Microsoft by clicking here

SQL2000

We do not support dividing your SQL database into multiple smaller databases.

It has been reported to us that Verizon ISP is now blocking port 1433. We suspect there are other ISPs blocking these ports as well.

We do not support DTS packages in a shared SQL environment.

Login to your SQL database with Query Analyzer and run the following command in copy the table 'table1' to a new table 'table2'(the table 'table2' does not have to be created before this proceed):

Select * Into table2 From table1

No, you do not have to purchase SQL Server to own a license of Enterprise Manager. You can purchase the lastest version of Microsoft Office Developer Edition. You will need to install the client tools.

http://www.microsoft.com/sql/evaluation/trial/default.asp

You will be able to 'see' the list of other databases on ther server, however you will not be able to view any any actual data structure or data within those databases since your account does not have rights to those databases. This is standard in a shared SQL environment.

Start MS Access and load your database.

Once you have it loaded, simply click Tools --> Database Utilities --> Upsizing wizard.

You will be prompted for the SQL authentication connection information in order to connect to the SQL server. Once connected, you may use all the SQL server resources.

import, db, db upgrade

As a normal part of ODBC operation, your database file is locked by the ODBC connection. There is a trick you can use to unlock the database temporarily. You can write a page to query a non-existent column in an existing table in your database. This will temporarily unlock the database so that you can overwrite the file. Since the file will be re-locked quickly, especially if other pages are accessing the database, it is best to rename or delete the old database before uploading the new one. As soon as you upload the new database and run a good query, normal ODBC operation will resume.

There are a few things you can do to maximize the success of the unlock page:

1. Make sure that no other pages are executing queries on the database. If a valid query is run right after your invalid query, the database will be locked again.

2. Since the database can be re-locked quickly, rename the .mdb file as soon as you run the invalid query. Then you can upload the new database and not worry about it locking again before you have a chance.

3. This process often requires some patience - if it doesn't work at first, try it again a few times. Also, you may want to wait a few minutes and then try. You may want to rename, disable, or move any pages that query the database to prevent them from re-locking it.

4. If all else fails, upload another copy of the database with a different name. Email support@enterhost.com and we will adjust the DSN.

You may be overloading your Access database. Microsoft Access is designed for desktop and small Internet applications. Test your query on your local machine. If it works fine, then your website traffic is overloading it. Try upgrading your database to SQL Server.

The easiest way to verify the version of MDAC is to use the following code:

If you are passing sensitive data, you will want to force the clients browser to
use SSL. If you have your own certificate installed this is not hard at all.

1. Save the following code in the root of your Website as an include file
named ForceSSL.inc:

2. For each page that requires SSL, insert the following code at the top of
the page to reference the include file from the previous step:

< !--#include virtual="/ForceSSL.inc"-->

3. When each page is browsed, the ASP code contained in the include file will
detect the port to determine if HTTP is being used. If HTTP is being used, the
browser will be redirected to the same page using HTTPS.

SQL Server 2005 does not support Enterprise Manager. SQL Server 2005 requires Microsoft SQL Server Management Studio, the updated version of Enterprise Manager.

The Express version is now available as a free download from Microsoft.

http://www.microsoft.com/sql/default.mspx

Creating a backup will provide you with an exact copy of your database.

If the SQL database is located on your own SQL server, you can use the steps below to create a backup.

If the SQL database you would like to backup is located on the Enterhost shared SQL server, then you will need to notify support@enterhost.com. Provide us with the name of your dedicated server and the database name(s) you would like to have backed up and we will place a copy of the backup file in the root of your e: drive upon your request.

Once you create the backup, you can restore the file to any SQL server of the same version or higher.

For SQL Server 2000
Login to the server through Enterprise Manager
Expand 'Microsoft SQL Servers' and 'SQL Server Group'
Click on your local SQL server and double-click databases. You will then see the list of databases on the right.
Right click on the database you would like to backup, click 'All Tasks and select 'Backup Database'
In the section titled 'Backup', select the type of backup you would like to make. For a full backup, select 'Database complete'

In the destination field, click on 'Add'.
Click to the right of the file name to open the file browser
Brose to the location you would like to store the backup and click the folder
Type the filename with the extension for the backup. A typical extension is .bak but you can use any filename you want. Example, daisysflowershop0301.bak
Click 'ok'
Click 'ok' to select the location

In the section called 'overwrite', select 'Overwrite Existing Media'. This will ensure a current copy of your database is the only copy in the backup.
Click 'ok'

For SQL Server 2005
Connect to the SQL server using SQL Server Management Studio
Expand 'Databases'
Right-click on the database you would like to backup and click 'Tasks' then 'Backup'
Select the backup type. Select Full for a complete backup

In the 'Destination Field' click 'Add'
Click to the right of the file name to open the file browser
Brose to the location you would like to store the backup and click the folder
Type the filename with the extension for the backup. A typical extension is .bak but you can use any filename you want. Example, daisysflowershop0301.bak
Click 'ok'
Click 'ok' to select the location
Click 'options' in the top left
Select the option for 'Overwrite all existing backup sets'
Click 'ok'

SQL, backup, back-up,restore, copy

To connect to the SQL database over port 2433, follow the steps below. 1. Click Start, and then click All Programs 2. Click SQL Server 2005, and then Configuration Tools 3. Click SQL Server Configuration Manager, and expand SQL Server 2005 Network Configuration 4. Under Protocol Name, click on TCP/IP, and then click the IP Address Tab 5. Expand IPAll, in the TCP Port field, change 1433 to 2433

Moving SQL Server from one computer to another is not a very difficult thing to do, but it often stumps newbie DBAs. Of course, it needs careful planning to ensure that the SQL Server is moved completely and properly to the new machine, and with a minimal downtime and no data loss. This article introduces you to a couple of methods you can employ to move/migrate SQL Server from one computer to another.

Before we get into the nitty-gritty, lets look at some reasons why one would want to move SQL Server from one computer to another? Here are a few:

Number of users has increased and the current database server simply doesn't have the required processing capacity.

You found out that the current database server is having hardware problems and is not reliable anymore.

You are enhancing your database applications and adding more features, and require more processing power, but the current server cannot be scaled up anymore. Instead of scaling out, you decided to scale up, by moving to a powerful new server.

Your infrastructure team came up with a new corporate spec, and you must upgrade your SQL Server to the new hardware specification.

Moving the entire SQL Server from one physical machine to another is different from just moving one or more databases. Moving an entire server means, moving more than just databases. You need to make sure the new server is configured with the same server level settings as the old server, and the new server must contain the same logins, role memberships etc. If you are interested in just moving databases, you could use backup/restore or detach/attach (using sp_detach_db and sp_attach_db) approach. You can find more information about moving databases, using the links provided at the end of this article. I also listed some useful books for DBAs.

For now we will concentrate on moving SQL Server completely from one machine to another.

Here are the steps involved in the process:

Purchase a new server, with the required hardware configuration.

Install the exact same version of Windows operating system on the new server.

Make sure the new server has the exact same drive letters with at least same or more disk space, as the old server. Configure your RAID subsystem as needed.

Take a note of the Windows service packs, Windows hotfixes and security patches that are currently installed on the old server, and apply them on the new server in the right order.

Take a note of any specific configuration changes that were made to the old server, and configure the new server accordingly. For example, if the old server is using AWE memory and has a /3GB switch or /PAE switch in the boot.ini file, then you need to add those switches on the new server's boot.ini as well. Another example would be, the pagefile. Page file can be spread across multiple drives, with specific amount of storage on each drive. You must mirror those settings on the new server as well.

Make sure the new server has the same number of (or more) network cards as the old server, and the network cards are configured optimally (for example, full duplex mode).

Once the new server is at the same (or better) level as the old server, from operating system's point of view, start with SQL Server installation. Install the same version of SQL Server (I tested these steps on SQL Server 7.0 and SQL Server 2000 only. Most of it is applicable to SQL Server 6.5 as well).

Make sure your new SQL Server is configured with the same authentication mode as the old server (Windows authentication (integrated security) or mixed mode).

Take a not of service packs, security patches, hotfixes that are installed on the old SQL Server, and install them on the new SQL Server as well.

Reboot the new server if necessary and make sure SQL Server, SQL Agent (and Microsoft Search) service are starting fine, without any errors.

You can perform the above steps much faster, if you have a corporate standard for installing and configuring SQL Server. Also, documentation of server settings is very important. Okay, now we have our new SQL Server ready. The next step would be to move the data, jobs, DTS packages, logins etc. to the new server. Moving all these objects individually would be very time consuming, as well as error-prone. If you can afford some downtime on your old SQL Server, follow these steps:

Make sure no users are trying to connect to your SQL Server. Inform them well in advance about the downtime. Shutdown any applications or websites that might be accessing this SQL Server.

Stop the SQL Server, SQL Agent and MS Search services on the OLD SQL Server.

Stop the SQL Server, SQL Agent and MS Search services on the NEW SQL Server.

Copy the data files and transaction log files from your old SQL Server's hard drive (for example D:\MSSQL\Data) to the new SQL Server's hard drive (to the same location, in this example, D:\MSSQL\Data). Since we are copying all database files, including the system databases (like master, model, msdb, distribution), your server level settings, jobs, DTS packages, alerts, operators, logins etc. will be copied across.

Copy all the other folders (like backup, ftdata, jobs, log, repldata etc.) under the old server's MSSQL directory, to the same location on the new server.

Copy any other folders or files from the old server to the new server, that will be accessed by your jobs, DTS packages, stored procedures etc.

If you have any specific net library configurations, aliases, protocol binding orders on the old server, make sure you apply those changes to the new SQL Server, using the tools, Client Network utility and Server Network Utility.

Take a note of any shares on the old server, and recreate them on the new server, with the same permissions and access rights to the relevant Windows accounts.

Start the SQL Server, SQL Agent and MS Search services on the new server. Connect to the new server, using Enterprise Manager and make sure all the databases are displayed and that there are no suspect databases. Scan through the SQL Server error logs, SQL Agent error logs, System and Application event logs, to make sure there are no errors. If you find any errors, fix them before you proceed any further.

Once you are happy with the new server's state, shutdown the SQL Server, SQL Agent and MS Search services.

Now its time to rename the new server as the old server, and remove the old server from the network. Consider that your old server is being called 'ServerA' and the new server we are building is currently named 'ServerB'. Follow these steps to swap the names of these servers:

Rename 'ServerA' to 'ServerA_OLD'. You can do this by right clicking on 'My Computer', select 'Properties', go to 'Network Identification' tab. Also, remove this server from the domain, and place it in it's own workgroup. Reboot the server 'ServerA'. As we renamed it, it will now come up as 'ServerA_OLD'.

On ServerA_OLD SQL Server, either disable all the jobs or disable the SQL Agent service. This is important, because, if the SQL Agent starts on this machine, it might kick off some jobs, that will interfere with other servers, or duplicate the work, that is already being done by our new server ServerA. If this situation is not taken care of, it will result in chaos and unpredictable results.

Rename the new server 'ServerB', to 'ServerA' and make sure it is in the production domain. Reboot 'ServerB'. As we renamed it, it will now come up as 'ServerA' (the name of the original server).

Connect to 'ServerA' using Query Analyzer and execute the command SELECT @@SERVERNAME. This will return the correct server name (if it didn't, then drop that server by running sp_dropserver and re-add the correct server name (ServerA), by running sp_addserver (Be sure to specify the 'local' parameter). For more information on how to drop and add the server names, read the article mentioned in the links section, at the end of this article.)

Now your new SQL Server is ready for use. Before you let the users start working with this server, perform testing and make sure the server is fully functional and all aspects of your production application are working fine. Once you are happy, you can let your users start accessing this new server. Since the name of the new server is now same as the old one, your applications will connect to the new server, without any problems. You don't have to reconfigure or change any DSN and connection string properties.

As you can see, it is not a very difficult task to move an SQL Server from one machine to another, but it requires careful planning. The only time consuming step in this process is the file copy. Copying databases that are several giga bytes (GB) or terra bytes (TB) in size from one machine to another over the network will be very time consuming, It can take a few hours or even a couple of days. The speed of file copy depends on the speed of your hard drives and your network bandwidth. Consider zipping the bigger files (using an application like WinZip or WinRAR) before copying. That could speed up the file copy over the network.

Though this method works well for mirroring SQL Server installation onto a new machine, it will require server downtime. If you are running a mission critical, 24/7 database and cannot afford any downtime at all, then you could follow the alternative approach described below:

Follow the same steps as explained above, for building a new SQL Server from scratch.

Copying database files requires you to shutdown the SQL Server. Since we don't want any downtime at all, forget about copying physical database files. Simply create backups of your static (read-only) databases and restore them onto the new server, using BACKUP and RESTORE commands.

You cannot apply the above step for your ever changing OLTP database, because, by the time you restore the backup onto the new server, the old server would have received more changes. To avoid this problem, you need to resort to log shipping. Log shipping is nothing but an automated process of backup and restore. The following steps will explain the process of log shipping (Note that SQL Server 2000 Enterprise Edition provides inbuilt logshipping. We don't need that for this purpose. We can simply create a job, that backs up and restores the databases).

Take a full database backup of your OLTP database from the old server, and restore it onto the new server. Make sure you restore using WITH STANDBY or WITH NORECOVERY option, as this allows us to restore further transaction log backups onto this database.

Create a job on the old server, with two steps. The first steps backs up the transaction log of the OLTP database. The second step restores this transaction log backup file onto the new server, using WITH STANDBY or WITH NORECOVERY option of the RESTORE command. Schedule this job to run every 10 minutes or 15 minutes, depending on the volume of transactions your OLTP database receives.

This job will take care of shipping all new transactions since the full database backup, to the database on the new server. Let this job run until you can afford some downtime on your production database (that is, mid-night, or early morning, or a planned maintenance window).

Once you are ready to swap the servers, ask your users to logoff (if there are any) and shutdown any applications or websites that are currently accessing this database on the old server. To be doubly sure, no new transactions are coming in, set the database in 'single user' or 'dbo use only' mode. Take one last transaction log backup of the production OLTP database, and restore it onto the database on new server. This time use the WITH RECOVERY option of the RESTORE command.

Now the OLTP database on the new server is completely in synch with the one on the old server. Its time to rename the servers now. Just follow the steps explained above (in the other approach), for renaming the servers.

As is evident, this method requires only a few minutes (5 to 10 minutes at the max) of downtime. The only time consuming processes in this approach are the "Amount of time it takes to create the last transaction log backup and apply it on the new server, the server renaming process (also depends on how quick you are on the keyboard:-), and the server reboot process".

Additional note about servers involved in replication topology: If the server you are moving is a distributor, then all replication agents (log reader, snapshot, distribution and merge) will be suspended, when you shutdown the server during the above process. The only side effect of this, would be the growing transaction log of the published database (as the transaction log cannot be truncated, until the replicated transactions are picked up by the log reader). If you have pull subscriptions, the pull distribution or merge agents will fail to connect to the distributor and you will see errors on your subscribers. These agents will most probably succeed (unless expired) when you restart them at the end of this process. If the server you are moving happens to be a publisher, just make sure you stop the replication agents related to this publisher, on the distributor and everything should be okay, once the new publisher comes back up. If the server you are moving happens to be a pull or push subscriber, make sure you stop the corresponding distribution agent, till the server swapping process completes.

Our Shared SQL servers do support full-text indexing and we will have to add the index for you. If you would like to create a full-text index, please send and email to support@enterhost.com with the following information and we will create the index for you.

SQL Server
Database Name

Table you want indexed
Unique Key
Columns to select
Schedule for index

full text, index, sql 2000

Some sources report that Vista's auto-tuning feature has caused network time-out problems with some applications and routers. If you are experincing VERY slow connections in applications such as Remote Desktop or SQL 2005 Management Studio, please try the following:

1. Open up an elevated command prompt. (Run as Administrator)
2. Enter the following command to disable auto-tuning:
netsh interface tcp set global autotuninglevel=disabled

If this does not resolve the problem you are having, you can turn Auto-tuning back on with the following steps:

1. Open up an elevated command prompt. (Run as Administrator)
2. Enter the following command to enable auto-tuning:
netsh interface tcp set global autotuninglevel=normal

Open the Windows Updates console. Click con Get updates for more products Internet Explorer will open. Accept terms of use Select User current settings. Click Install. It may take some time to search updates. Run Windows Update. Click on View available updates. Make sure to select any SQL updates. Click install.

1. Open the SQL Server Configuration Manager
2. Expand SQL Server 2005 Network Configuration
3. Select Protocols for SQLEXPRESS
4. Double click on TCP/IP
5. Click on the IP Addresses tab
6. Edit the TCP Port fields to remove 1433
7. Click OK
8. Restart the SQL Server service

SQL Injection is the act of a malicious user exploiting a weakness in a website's code. It is normally done to either insert code into a site or database, or gain access to sensitive information. Here is a simplistic example that demonstrates how it works:

There is a login field on a website. A malicious user stops by and enters admin for the username. Instead of trying to guess the password, they use a SQL snippet for the password: ' or 1=1--

The statement '1=1'; will always validate as true, so they're able to log in without knowing the password.

How can I protect my website?

Implement checks that validate and sanitize the user's input. Block common malicious characters like ' ; = *. Also disallow SQL keywords like SELECT, TABLE, DROP, etc.

Do not allow direct SQL queries from URLs. For example: http://yourwebsite.com/search.asp?category=users' or 1=1--

For more information, see:
http://www.governmentsecurity.org/articles/SQLinjectionBasicTutorial.php
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
http://msdn.microsoft.com/en-us/library/ms998271.aspx

SQL Server Management Studio Express is free from Microsoft and available as a part of SQL Express or stand-alone download. There are currently two versions available, 2005 and 2008.
2005
http://www.microsoft.com/sqlserver/2005/en/us/express.aspx

2008
http://www.microsoft.com/express/sql/download