Mustafa EL-Masry Script, SQl server Administration

Trigger to Prevent any user to Create Table on Database


Event Can prevent happend on your database
CREATE_TABLE
CREATE_INDEX
CREATE_FUNCTION

Example

USE
DBNAME;
GO
CREATE TRIGGER Prevent_Create

ON
DATABASE FOR CREATE_TABLE
AS
PRINT ‘CREATE TABLE Issued.’
SELECT EVENTDATA().value
(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
RAISERROR (‘New tables cannot be created in this database.’, 16, 1)
ROLLBACK;
GO
–Test the trigger.
CREATE TABLE T2 (C1 int);
GO
–Drop the trigger.
DROP TRIGGER Prevent_Create_Table ON DATABASE;
GO

Mustafa EL-Masry SQl server Administration

troubleshoot The Connectivity Issues


1. Check your SQL Server Error log to make sure that it enables TCP/IP protocol and establish the listening on a specific TCP port.Normally, if SQL Server is configured to enable TCP/IP protocol, when it starts it will log a message similar to the following error log: 2010-01-04 12:30:40.47 Server Server is listening on [ ‘any’ <ipv4> 1433].

This means that the SQL Server instance is using TCP/IP protocol and listening on the TCP port 1433.

2. Enable TCP/IP protocol if you could not find the message in the Error Log.

You can enable protocols via SQL Server Configuration Manager. Under SQL Server Network Configuration->Protocols for <instance name>, enable TCP/IP protocol.

3. Check Network issue after TCP/IP enabled.

To check if your network is healthy, you can use ping and telnet orders to check.

· Run “ping -t” for 1-2 minutes to check if there any timeout response

ping -t <the remote server IP address>

If there are timeout responses, contact your network administrator to check the network issue.

· Run “telnet” command directly to the TCP port on the server to check if you can access the TCP port.

Telnet <servername> <listening tcp port>

4. Check firewall if you cannot telnet to the specific TCP port.

Ensure that the TCP port on which SQL Server is listening has been put into the exceptions of your firewall. If you connect to a named instance, it is also recommended that you put the UDP port 1434 for SQL Server Browser service into your firewall exception.

The connection issues may be due to other causes like client drivers, application configuration, authentication and ect.

Reference

http://windows.microsoft.com/en-us/windows-vista/Telnet-frequently-asked-questions 

http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

 

Mustafa EL-Masry SQl server Administration

How do i rename my SQL Server instance?


 

 

SQL Server doesn’t support renaming the instance name directly. To work around the issue, you could use one of the following workarounds:

a) Uninstall the existed instance and install it with the new name.

 

b) Change the computer name and rename the part of the instance name that corresponds to the computer name.

For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance part of the name, Instance1, will remain unchanged. In this example, the \\ComputerName\InstanceName would be changed from \\MB1\Instance1 to \\MB2\Instance1.

For a renamed default instance, run the following procedures:
sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO For a renamed named instance, run the following procedures:
sp_dropserver <old_name\instancename>
GO
sp_addserver <new_name\instancename>, local

 GO

 

References

 

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

http://technet.microsoft.com/en-us/library/ms143799.aspx