MSSQL – Enabling “Service Broker protocol” for a DB

To determine whether or not Service Broker is enabled for a particular database, execute the following T-SQL:

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘Database name’;

— Enable Service Broker:
ALTER DATABASE [Database Name] SET ENABLE_BROKER; Read more...

[DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection Error when binding MSSQL database with plesk 10

Connection to a named instance of SQL server 2005/2008 should be made using the format
‘VirtualServer\Instancename’. if the IP address of a machine running a named SQL server instance
(ONE)is 192.141.128.140, you can connect to the named instance using  192.141.128.140\ONE for the servername.

To avoid a client app from using the above convention for a servername, a client  alias can be created using the client network utility. Client network utility  can be initiated by   Read more...

Backup all MSSQL databases

1. Log into your server through Remote Desktop Connection.
2. Open SQL Server Management Studio and select the server name
3. Click the New Query button and enter in the following data:

DECLARE @name VARCHAR(50) — database name

DECLARE @path VARCHAR(256) — path for backup files

DECLARE @fileName VARCHAR(256) — filename for backup

DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘C:\Backup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name     Read more...

SQL Server Port

Scenario 1:

SQL is not connecting via Management studio locally after a fresh Installation:

1. After a fresh Installation of SQL  and opening the port in the Firewall you may not be able to connect via SQL management studio.

Note: Installing SQL and opening the port in Firewall will not listen to the service/port. Its obvious that no service will listen even If the port is opened in the firewall. The application must listen to the service.

2. After Installing SQL, go to SQL Enterprise Manager in case of 2005, 2008, SQL server Network Utility If the version is 2000.

3. Go to TCP/IP, make sure the default localhost IP and the IP in which your server must listen to the SQL service should use the SQL port.  Restart the SQL services. Read more

Shrink 2008 MSSQL database

Shrinking SQL 2008 database is nowhere related to SQL 2000/2005 Method. Because SQL Server 2008 no longer allows you to truncate the transaction log using the no_log syntax

But here is the special code to shrink the database:

USE MASTER
GO
ALTER DATABASE database_name SET RECOVERY SIMPLE
GO
USE database_name
GO
DBCC SHRINKFILE (database_name_log, 1)
GO
ALTER DATABASE database_name SET RECOVERY FULL
GO     Read more

FTP Problems in Virtuozzo Servers

When connecting to VPS via FTP( Say File zilla ) which results in Could not connect to server. But the settings will be perfect. You check with server FTP settings, Permissions, ports, FTP modes etc and see everything is fine.

Cause

After uploading large files via FTP connection in virtual servers (happens in normal Windows Physical servers too) the Gateway service which resulting the transactions hangs and can be restored only after reconnection.

The problem is caused and can be fixed by Application Layer Gateway Service. Read more

Windows Client Experience in Server OS

To enable the windows vista/windows 7 experience (theme) in server three things should be considered:

1. Installing Terminal server Role
2. Installing Desktop Experience
3. Modifying Group policy to take effect the theme for the users

Installing Terminal server Role:

RDC (Remote Desktop Connection) no longer supports desktop ecpereince service for the server. So it is mandatoy that TS role must be installed

Steps:

Open server Manager -> Add role -> Terminal services -> add TS role (only)

Installing Desktop Experience

The desktop experience comes as service in the server . Read more

Enable Audio from remote machine through RDP

Here are the steps to follow to enable audio from remote machines in windows:

Steps:

1. First check the RDP application has audio enabled
Start -> mstsc -> options in RDC application -> Local resources -> Remote computer sound -> choose “Bring to this computer”

2. Edit group policy of the remote machine
start -> run -> gepdit.msc -> computer configuration -> administrative templates -> Read more