Saturday 5 August 2006

SQL Formatter Tool

Being asked sometimes about a good tool for formatting T-SQL code, I would like to share one of them known as 'SQL Formatter tool'. Not only will this tool format T-SQL code, but also MS ACCESS, ORACLE/PLSQL, DB2, and MySQL. The great thing of this tool is that we will be able to generate output results for HMTL, C#, VB, Cobol, PHP, Java, and others. Try using it to see if it works for you and then make the most out of it. Here is the link of the web version:

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Let me know any remarks or experience you may have using the tool. Thanks for reading. Stay tuned.

Sunday 21 May 2006

Installing SQL Server 2005 Service Pack 1 on all SQL instances via CMD

When it comes to installing SQL Server 2005 Service Packs on various instances rapidly, we can do it by executing the Service Pack setup with some parameters via CMD. First of all, we can use it to figure out which parameters we do have available to work with, so the following can be done:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /?

Having executed that via CMD, the complete list of parameters will be shown:

/? – Help
/quiet – Quiet mode
/reportonly -Instance report only
/allinstances – Apply patch all product instances by default
/instancename – Product instance names to patch
/SAPWD – SQL SA password
/use – Remote admin domain and account
/password – Remote admin password


Furthermore, if we are curious to know about how many SQL instances have the right service pack installed, we may use the '/reportonly' parameter:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /reportonly

Only after that will we be able to verify which client and server components are installed for each SQL Server 2005 instance like MSXML Parser version, Database Engine services, SSAS services, SSIS services, and so on. Now we are ready, for example, to install the SQL Server 2005 Service Pack 1 on SVPRDB:  

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /instancename SVPRDB

Finally, we may want to install it on all SQL Server 2005 instances in the same server:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /allinstances

As you have been, using this technique is not complex. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.

Friday 14 April 2006

How to remove 'sa' account from 'sysadmin' fixed server role in SQL Server 2000

When it comes to securing the databases, we need to have a small number of accounts with 'sysadmin' fixed server role, which means that only the DBA and someone else should be assigned to that role inside the database engine. So, I highly recommend disabling or removing 'sa' account from 'sysadmin' fixed server role in SQL Server 2000. Nevertheless, this task is not so easy to do because any change on 'sa' account is protected internally by SQL Server. Furthermore, Microsoft says that this account cannot be changed at all http://msdn.microsoft.com/en-us/library/aa905197(SQL.80).aspx.
System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords).
Because I also know that "In theory there is no difference between theory and practice. But, in practice, there is.” (Jan L.A. van de Snepscheut), I was digging deep into this issue and found out that, fortunately, what Microsoft says is true up to a point, but we can take control of it by making small changes internally. In other words, the famous 'sa' account can be changed in SQL Server 2000. Today's post is going to show how to achieve it with ease. (The demonstration is done on SQL Server 2000 Service Pack 3a.)

First of all, we do need to know that the logic to manage the 'sa' account is stored inside the 'master' system database of SQL Server. Consequently, I decided to look into the code of sp_dropsrvrolemember, sp_addsrvrolemember, and sp_droplogin system stored procedures. What I figured out is that all of them contain the following validation based on the name of the account to prevent from any change.


According to that code, only if we change that name will SQL Server allow us to make other changes on it. Likewise, I also noticed that SQL Server does not allow to delete this account if its SID is equal to '0x01', so, if I we also change that SID, we will be able to delete the account. This is what we are going to do now.

Naturally, we will get the following error when we try to remove the 'sa' account from 'sysadmin' fixed server role:

exec sp_dropsrvrolemember sa,‘sysadmin’ 

Server: Msg 15405, Level 11, State 1, Procedure sp_dropsrvrolemember, Line 40 
Cannot use the reserved user or role name ‘sa’.

Trying to drop the 'sa' account:

exec sp_droplogin sa

Server: Msg 15405, Level 11, State 1, Procedure sp_droplogin, Line 39
Cannot use the reserved user or role name ‘sa’.


In order to be able to make those changes, we firstly need to enable 'allow updates'.
exec sp_configure 'allow updates', 1  
go  
reconfigure with override 

Having done that, we can remove the 'sa2' account from 'sysadmin' fixed server role.

exec sp_dropsrvrolemember sa2, 'sysadmin'

Changing the SID to '0x02':

 update sysxlogins set sid=0x02 where name='sa2' 

Finally, only now are we able to delete 'sa2' account.

exec sp_droplogin 'sa2' 

Having successfully executed each step, we have succeeded in deleting the 'sa' account. As you have seen, it was not overly complicated to get this account deleted. On the other hand, how can we create that account again? if we try to create again the 'sa' account, we will get this error:

exec sp_addlogin 'sa'

Server: Msg 15405, Level 11, State 1, Procedure sp_addlogin, Line 49
Cannot use the reserved user or role name ‘sa’.


Do not worry about that, we can also create it again by executing this code as follow:

exec sp_addlogin 'sa2'  
go  
exec sp_addsrvrolemember sa2,'sysadmin'  
go 
update sysxlogins set name='sa' where name='sa2'
go
update sysxlogins set sid=0x01 where name='sa' 


That is all for now. I hope you find this tip helpful. Let me know any remarks you may have. Thanks for reading. Stay tuned.

Tuesday 14 March 2006

How to uninstall SQL Server 2005 instances successfully

With the passing of days, the number of people asking me how to uninstall SQL Server 2005 instances successfully is on increase. I would like to say that it is a simple question but quite difficult to answer as there are many related components installed which need to be uninstalled by following a correct order, otherwise, we are going to fail. Today's post is going to explain my own technique to do it with ease and success.

Here is the correct procedure to uninstall a SQL Server 2005 instance:

1. Firstly, we do have to find the right directory where 'ARPWrapper' tool is installed (usually it is inside of either 'C:\Program Files' or 'C:\Program Files (x86)' folders). After doing that, we can start the tool by executing one of the two following commands via CMD with '/remove' parameter included.

"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe" /Remove

"C:\Program Files (x86) \Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe" /Remove

Having done that, 'ARPWrapper.exe' will start and we will be able to choose the SQL Services we want to uninstall. The wizard shows two parts: the first one lists the server components and the second one the common components. Because we only want to uninstall all server components (Database Engine, Analysis Services, SSIS, and etc.), we will select the first one and then click on 'Next' and 'Finish'.


Naturally, if we wanted to uninstall the common components at the same time, we will have to check all of them in the same window:



Only after successfully completing the process will we be able to move on the next steps. (If we had Reporting Services installed, we would have to make sure that its virtual directories were deleted as well, otherwise, we do have to delete manually the directories ReportServer[$SQLInstanceName] and Reports[$SQLInstanceName] by using IIS Services Manager)

2. We are ready to move on. Going to 'Add or Remove Programs', we will uninstall some components following strictly this order: 
  • MSXML 4.0 Parser
  • SQLXML6
  • Microsoft SQL Server VSS Writer
  • Microsoft SQL Server 2005 Backward Compatibility Components
  • All components related to SQL Server 2005, except Microsoft SQL Server Native Client y Microsoft SQL Server Setup Support Files components
(It is time to move on the next step if this was the only SQL Instance that we wanted to remove, otherwise, we need to come back to the first step and do the same for each SQL Instance until removing all.)

3. Remove 'Microsoft SQL Server Native Client' component by using  'Add or Remove Programs'.
4. Remove 'Microsoft SQL Server Setup Support Files' component by using ''Add or Remove Programs'.
5. Finally, we have to restart the server.

To sum up, not only do we have SQL Server 2005 uninstalled successfully, but also very cleanly by following some simple and practical steps. Now this task is not going to be a pain in the neck any longer. 

I hope you find this post very useful and make the most out of it. That is all for now, let me know any remarks you may have uninstalling SQL Server 2005. Thanks for reading. Stay tuned.

Sunday 5 February 2006

Improving the performance by using 'Boost SQL Server Priority' option

Boost SQL Server Priority is one of the many options that we can configure at SQL Server instance level in order to improve the performance of the database engine by changing its priority running on Microsoft Windows Server.

By default, the value of this option is 0 (zero) which says to Windows Server to execute SQL Server under normal priority either running on one processor or symmetric multiprocessor environment. On the other hand, if we configure this value to 1, SQL Server will be executed with a major priority which might improve the performance of a database server. This option works very well on servers which are only dedicated to SQL Server. In contrast, it is not highly recommended using it when there are more applications running on the same server.

'Boost SQL Server Priority' option can be configured via either SQL Management Studio or T-SQL. In this post we are going to see how to configure it via the second way. Because this option is an Advanced Option, we firstly need to enable the possibility of accessing to it, which means that we have to enable 'show advanced options' option:
sys.sp_configure 'show advanced options', 1
Shortly after that, we must confirm the change without restating the database engine:
RECONFIGURE
GO
Having done that, we move on configuring 'Boost SQL Server Priority'.
EXEC sys.sp_configure N'priority boost', N'1'
GO
Now we need to commit and override the change.
RECONFIGURE WITH OVERRIDE
GO
That is all we need to do in order to enable this option. It couldn't have been simpler.
To sum up, at this point I need to ask you to work with a lot of cautiousness as this configuration might impact on the whole database engine performance. If there were many SQL Server database engines running on the same server and this option was enabled only for some of them, the rest of them might be impacted detrimentally. Nevertheless, I personally do not recommend enabling it on machines running on only one processor or many database engines (or other applications). Thanks for reading. Stay tuned.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE