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

 

Mustafa EL-Masry SQl server Administration

SQL Server memory configuration


On the Start menu, click Run. In the Open box, type gpedit.msc.

2. The Group Policy dialog box opens.

3. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

 4. Expand Security Settings, and then expand Local Policies.

 5. Select the User Rights Assignment folder.

6. The policies will be displayed in the details pane.

7. In the pane, double-click Lock pages in memory.

8. In the Local Security Policy Setting dialog box, click Add.

 9. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

 Finally to enable AWE:

 1. In SQL Server Management Studio, right-click a server and select Properties.

2. Click the Memory node. 3. Under Server memory options, select Use AWE to allocate memory.

Mustafa EL-Masry Errors

SQL Server 2008R2 Error After intall Then Make Update To Your PC


SQL Server 2008R2 Error After intall Then Make Update To Your PC

 

Introduction

 
Yesterday I install SQL  server 2008R2 in my Laptop  Then After install I make update to my pc in this update I download update for SQL Server 2008R2
This Update  Name  is

(Security Update for SQL Server 2008 R2 (KB2494088))
Then after I intall the update I make restart to my laptop then I try to connect to my instance SQL server 2008R2 I receive this Error massage

Login failed for user ‘Elmasry-PC\Elmasry’. Reason: Server is in script upgrade mode (Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)) .


 When I see this error I can’t believe my easy  Because before 5 minutes before the update my instance was worked very good so I make search on the internet  and I can fix this error

 
The Solution

 
1. Enable trace flag 902 on the instance of SQL Server 2008 R2. To do this, follow these steps:
a. Open SQL Server Configuration Manager.
b. In SQL Server Configuration Manager, click SQL Server Services.
c. Double-click the SQL Server service.
d. In the SQL Server Properties dialog box, click the Advanced tab.
e. On click the Advanced tab, locate the Startup Parameters item.
f. Add(( ;-T902 )) to the end of the existing string value, and then click OK.
2. Right-click the SQL Server service, and then click Start.
3. If the SQL Server Agent service is running, right-click the SQL Server Agent service, and then click Stop.
4. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2008 R2.
5. Run the following statements:

EXEC sp_configure ‘show advanced’, 1;
RECONFIGURE;
EXEC sp_configure ‘allow updates’, 0;
RECONFIGURE;
EXEC sp_configure ‘Agent XPs’, 1;
RECONFIGURE;
GO

Note  :

In steps No 5 I receive another Error massage this Massage is ((ad hoc update to system catalogs is not supported)) So if you Receive this error try to make this step (Fix Ad Hoc) If you don’t receive this error go to step no 6

 
Fix Ad Hoc
Run This Script then Try to make the step No 5 Again
sp_configure ‘show advanced options’,1
reconfigure
go
sp_configure ‘xp_cmdshell’,1
reconfigure
go
sp_configure ‘show advanced options’,1
reconfigure with override
go
sp_configure ‘xp_cmdshell’,1
reconfigure with override
go

—Reset the “allow updates” setting to the recommended 0
sp_configure ‘allow updates’,0
reconfigure with override
go
—Reset the environment back as the test is complete
sp_configure ‘xp_cmdshell’,0
reconfigure
go
sp_configure ‘show advanced options’,0
reconfigure
go

6. In SQL Server Configuration Manager, right-click the SQL Server service, and then click Stop.
7. Remove trace flag 902 on the instance of SQL Server 2008 R2. To do this, delete((  ;-T902 )) from the string value that you updated in step 1f.
8. Right-click the SQL Server service, and then click Start.
9. Right-click the SQL Server Agent service, and then click Start.
10. In SQL Server Management Studio, reconnect to the instance of SQL Server 2008 R2.
11. In Object Explorer, expand Management, right-click Data Collection, and then click Enable Data Collection.
Note :

 You may receive the following error message when you connect to the instance of SQL Server 2008 R2 in SQL Server Management Studio:
Error: 18401
Login failed for user ‘<login name>’. Reason: Server is in script upgrade mode. Only administrator can connect at this time.


This error message indicates that SQL Server 2008 R2 is completing the installation of cumulative update package 1. If you receive this error message, wait several minutes and then connect to the instance of SQL Server 2008 R2 again.
 Best Regards

Mostafa Elmasry