Difference between revisions of "MS SQL Server FAQs"
From Hostek.com Wiki
(→How to Enable TCP/IP Connections in MS SQL Server) |
(added section for how to unlock MS SQL sa user..~~~~) |
||
| (8 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | =MS SQL Server Frequently Asked Questions (FAQ)= | ||
==How to Enable TCP/IP Connections in MS SQL Server== | ==How to Enable TCP/IP Connections in MS SQL Server== | ||
| − | Some versions of SQL Server (including the Express edition) have TCP/IP services disabled by default, which effectively disables remote connections to SQL Server. To enable TCP/IP services, follow these steps: | + | Some versions of SQL Server (including the Express edition) have TCP/IP services disabled by default, which effectively disables remote connections to SQL Server. To enable TCP/IP services for SQL Server on your VPS or Virtual Dedicated server, follow these steps: |
#Open the SQL Server Configuration Manager<br />[[File:Remote-connections1.png]]<br /><br /> | #Open the SQL Server Configuration Manager<br />[[File:Remote-connections1.png]]<br /><br /> | ||
#Expand the ''SQL Server Network Configuration''-->''Protocols for SQLEXPRESS'' section (it may be named ''Protocols for MSSQLSERVER'' in some installations.)<br /><br /> | #Expand the ''SQL Server Network Configuration''-->''Protocols for SQLEXPRESS'' section (it may be named ''Protocols for MSSQLSERVER'' in some installations.)<br /><br /> | ||
| Line 6: | Line 7: | ||
#Toggle the ''Enabled'' property to '''Yes'''.<br />[[File:Remote-connections3.png]]<br /><br /> | #Toggle the ''Enabled'' property to '''Yes'''.<br />[[File:Remote-connections3.png]]<br /><br /> | ||
#Switch to the IP Addresses tab, and enable the ports you wish to use to access SQL Server<br />[[File:Remote-connections4.png]]<br /><br /> | #Switch to the IP Addresses tab, and enable the ports you wish to use to access SQL Server<br />[[File:Remote-connections4.png]]<br /><br /> | ||
| − | Restart the SQL Server Service, and you'll be able to connect remotely via IP or hostname. | + | Restart the SQL Server Service, and you'll be able to connect remotely via IP or hostname. |
| + | |||
| + | ''Note'': You will need to ensure your [[Firewall_Options_-_VPS/Dedicated#Windows_Firewall|Windows Firewall]] has the appropriate ports open to allow connections to MS SQL Server. | ||
| + | |||
| + | ==How to Clear Space in Your Transaction Log== | ||
| + | #Roll back any pending transactions and clear all connections first. To do that, run the following three statement query before attempting to backup the log again:<br><code>USE master;<br>ALTER DATABASE mydatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;<br>ALTER DATABASE mydatabasename SET MULTI_USER;</code> | ||
| + | #Back up your transaction log by opening SQL Server Management Studio, right-clicking the database and choosing: '''Tasks'''-->'''Back Up...'''. | ||
| + | #In the popup that appears be sure to set the '''Backup type''' to "Transaction Log". | ||
| + | #Set the backup destination, and give the file name a '''.trn''' extension. | ||
| + | #Once the backup is complete right-click the database, click '''Tasks'''-->'''Shrink'''-->'''Files'''. | ||
| + | #In the popup that appears, set the '''File Type''' to '''Log'''. | ||
| + | #Click '''OK'''. | ||
| + | |||
| + | When you check the size of the database's log file, it should be nearly empty (~ 1 MB). | ||
| + | |||
| + | ==How to add a Full Text Index== | ||
| + | #Log into the SQL Server with Management Studio and find your database | ||
| + | #Expand the Storage Folder, right click "Full text Catalogs" then click "Create New" | ||
| + | #Enter only Full-text Catalog Name and Owner fields. Select any options you require. | ||
| + | #Right-Click the new Full Text catalog then select "Properties" | ||
| + | #Select "Tables/Views" and assign table/views to catalog | ||
| + | #Click Ok, and you're done. | ||
| + | |||
| + | ==Do you support Bulk Import for CSV files?== | ||
| + | On our Shared Servers we do not support this feature, since the database server would be on a different server then your website. You would need to have a VPS hosting server to utilize this feature. | ||
| + | |||
| + | ==How to Unlock 'sa' User== | ||
| + | If the 'sa' user for your SQL Server installation is locked out, you can unlock it without having to reset its password. | ||
| + | |||
| + | *Open SQL Server Management Studio and log-in as the Windows administrator | ||
| + | *Open a new query window and run the following T-SQL: | ||
| + | <syntaxhighlight lang="tsql">ALTER LOGIN sa WITH CHECK_POLICY = OFF; | ||
| + | ALTER LOGIN sa WITH CHECK_POLICY = ON; | ||
| + | GO</syntaxhighlight> | ||
| + | |||
| + | Now you will be able to use the 'sa' account with its existing password. To reduce the likelihood of the account becoming locked out again, you may wish to either close the MS SQL Server port or restrict access to it by IP. You can do either via the [[WCP#Firewall_Management|Firewall Management section of your VPS control panel]]. | ||
| + | [[Category:Databases-MSSQL]] | ||
| + | [[Category:ColdFusion-VPS]] | ||
| + | [[Category:Linux-VPS]] | ||
| + | [[Category:Railo-VPS]] | ||
| + | [[Category:Windows-VPS]] | ||
Latest revision as of 19:29, 18 August 2014
Contents
MS SQL Server Frequently Asked Questions (FAQ)
How to Enable TCP/IP Connections in MS SQL Server
Some versions of SQL Server (including the Express edition) have TCP/IP services disabled by default, which effectively disables remote connections to SQL Server. To enable TCP/IP services for SQL Server on your VPS or Virtual Dedicated server, follow these steps:
- Open the SQL Server Configuration Manager
- Expand the SQL Server Network Configuration-->Protocols for SQLEXPRESS section (it may be named Protocols for MSSQLSERVER in some installations.)
- Right-Click on the TCP/IP item, and click Properties
- Toggle the Enabled property to Yes.
- Switch to the IP Addresses tab, and enable the ports you wish to use to access SQL Server
Restart the SQL Server Service, and you'll be able to connect remotely via IP or hostname.
Note: You will need to ensure your Windows Firewall has the appropriate ports open to allow connections to MS SQL Server.
How to Clear Space in Your Transaction Log
- Roll back any pending transactions and clear all connections first. To do that, run the following three statement query before attempting to backup the log again:
USE master;
ALTER DATABASE mydatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mydatabasename SET MULTI_USER; - Back up your transaction log by opening SQL Server Management Studio, right-clicking the database and choosing: Tasks-->Back Up....
- In the popup that appears be sure to set the Backup type to "Transaction Log".
- Set the backup destination, and give the file name a .trn extension.
- Once the backup is complete right-click the database, click Tasks-->Shrink-->Files.
- In the popup that appears, set the File Type to Log.
- Click OK.
When you check the size of the database's log file, it should be nearly empty (~ 1 MB).
How to add a Full Text Index
- Log into the SQL Server with Management Studio and find your database
- Expand the Storage Folder, right click "Full text Catalogs" then click "Create New"
- Enter only Full-text Catalog Name and Owner fields. Select any options you require.
- Right-Click the new Full Text catalog then select "Properties"
- Select "Tables/Views" and assign table/views to catalog
- Click Ok, and you're done.
Do you support Bulk Import for CSV files?
On our Shared Servers we do not support this feature, since the database server would be on a different server then your website. You would need to have a VPS hosting server to utilize this feature.
How to Unlock 'sa' User
If the 'sa' user for your SQL Server installation is locked out, you can unlock it without having to reset its password.
- Open SQL Server Management Studio and log-in as the Windows administrator
- Open a new query window and run the following T-SQL:
ALTER LOGIN sa WITH CHECK_POLICY = OFF; ALTER LOGIN sa WITH CHECK_POLICY = ON; GO
Now you will be able to use the 'sa' account with its existing password. To reduce the likelihood of the account becoming locked out again, you may wish to either close the MS SQL Server port or restrict access to it by IP. You can do either via the Firewall Management section of your VPS control panel.