Enable SQL Server Remote Connections

About this task

Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Its primary query language is Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) which is used by Microsoft. You need to allow distant users to connect to the SQL server so they can address it their queries.

SQL Server is used by:

• Content Manager End User components;
• Content Manager Author components;
• Content Manager Database.

Note: The first step of this procedure is sufficient in most cases. Go through the other steps if you encounter any issue.

Procedure

1. Enable remote connections to your SQL Server.
   1. Open SQL Server Management Studio.
   2. Right-click your server's name and select Properties.
   3. Tick the checkbox Allow remote connections to this server.
   4. Select OK.
   Microsoft SQL Server by default uses TCP 1433 but this can be changed using SQL Server Enterprise Manager or the database Management Studio.
2. Enable TCP/IP.
   1. Open the SQL Server Configuration Manager.
   2. In SQL Server Network Configuration select Protocols for [yourServerInstance].
   3. In the right-hand pane, make sure that TCP/IP is Enabled.
3. Open the 1433 port in your firewall.
   1. In the SQL Server Configuration Manager, right-click TCP/IP and select Properties.
   2. Select the IP Addresses tab and make sure the TCP Port for IP1 is 1433.
4. If you are using a named instance, create an extra rule in your firewall with the port 1434.
   Note: For a named SQL Server instance (e.g. [yourServerInstance]\SQL2012SP2), the firewall needs an extra rule on the UDP protocol with the specific port 1434. Without this rule the system will return the exception error: 26 - Error Locating Server/Instance Specified.
   1. Display the firewall advanced settings by navigating to Control Panel > System and Security > Windows Firewall > Advanced settings.
   2. Select Inbound Rules in the left-hand pane, then click New Rule in the right-hand pane.
   3. In the New Inbound Rule Wizard, Rule Type step, select Port.
   4. Protocols and Ports step, select UDP and set Specific local ports to 1434.
   5. Action step, select Allow the connection.
   6. Profile step, tick the Domain checkbox.
   7. Name step, enter a name for this rule, e.g. Named instance port 1434.
   8. Select Finish.

How to set SQL Server database Online/Offline

Database is made offline to move its physical files. There can be many ways to make a database offline. But there are three main methods which are used frequently to make the database offline. These methods are given below:-

1) With the help of Alter database Command

We can make the database offline or online with the help of the Alter database command. The Alter Database command to make the database offline is:

ALTER DATABASE database name SET Offline

If we want to make the database online we can use the following Alter Database command:

ALTER DATABASE database name SET Online

2) With the help of the Db_options

We can also use the db_options command to make a database offline or online.To make a database offline we can use the following command:

sp_dboption databasename ,'offline',true

To make the database online we can use the following command:

sp_dboption databasename ,'offline',false

3) With the help of Sql server management studio

We can also use the Sql server management studio to make a database offline / online.

SQL Server - Find table in multiple databases

To find a table in one specific database run the following query.

SELECT *
FROM sys.Tables
WHERE name LIKE '%Address%'

To search through multiple databases query can be enhanced as follows.

DECLARE @TableName VARCHAR(256)
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)

SET @TableName = 'Address'
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256))
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT '''+ @DBName + ''' AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables
WHERE name LIKE ''%' + @TableName + '%'''
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable

Sql query to search all tables for a specific text.

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END