SQL11 (Denali): How to migrate SQL Server 2000 databases to SQL Server 2012?

Having found 'SQL Server 2000 (80)' inside the compatibility levels supported by SQL Server 2012, I need to say that the upcoming version of SQL Server is not going to support it. we do know that each new version only supports compatibility levels of two below versions, which means that it is an error as this is a CTP build.



What is going on if we try to restore a SQL Server 2000 database on SQL Server 2012? well, we will get the following error:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.1103. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Attaching the database neither works.

Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Northwind’. CREATE DATABASE is aborted.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Northwind’. CREATE DATABASE is aborted.
Msg 950, Level 20, State 1, Line 1
Database ‘Northwind’ cannot be upgraded because its non-release version (0) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

Therefore, SQL Server 2000 databases have to be migrated to SQL Server 2012 following the next steps: (1) restore SQL Server 2000 databases on SQL Server 2005 or SQL Server 2008; (2) take Full Backups of the new SQL Server 2005/2008 databases, and finally; (3) restore them on SQL Server 2012. In other words, there will not be direct migration from SQL Server 2000 to SQL Server 2012. That is all for now, let me know any remarks you may have. Thanks for reading again!

Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008

MSCOM Ops Team has done a tremendous and excellent work in testing the performance of SQL Server 2005 replication environments on Windows Server 2003 and SQL Server 2008 on Windows Server 2008, and good benefits were achieved as a result of the performance enhancements in the next generation TCP/IP stack. If you open the link, you will read that the team concludes the following:

“… the team discovered that SQL Server 2008 running on Windows Server 2008 yielded up to 100 times faster performance without requiring any expensive wide area network (WAN) acceleration hardware”

Furthermore, scalability and performance have been improved in SQL Server 2008 Native Client, exactly, in the way of invoking stored procedures with ODBC call syntax and OLE DB remote procedure call (RPC) syntax. To find out more details about it, check out Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008. It is also found best practices and baselines, here are some of the them:

"Testing showed that using transactional replication with SQL Server 2008 running on Windows Server 2008 dramatically outperformed SQL Server 2005 running on Windows Server 2003. As illustrated in Table 2, the most substantial performance gains occurred when the Publisher and Subscriber were both running SQL Server 2008 on Windows Server 2008…
…Testing also showed that the scope of the performance gains correlated with the type of replication and the type of data. Push subscription replication of character data with SQL Server 2008 running on Windows Server 2008 yielded a 104 percent increase over SQL Server 2005 running on Windows Server 2003, and pull subscription replication of the same data yielded a 1,298 percent gain.
Both lab and real-life testing by the MSCOM Ops team indicate that highly trafficked Web sites can gain the benefits of geo-replication most effectively when the site is built on SQL Server 2008 running on Windows Server 2008. Based on solid evidence of the feasibility of WAN–based geo-replication, MSCOM Ops plans to expand its implementation of this solution.
In addition, the MSCOM Ops team learned several valuable lessons because of its extensive performance testing of SQL Server 2008 running on Windows Server 2008, including:

• Windows Server 2008 and SQL Server 2008 with the TCP/IP stack improvements and partnering with application development teams can bolster global user experiences, produce higher availability, higher scalability, and better resiliency for sites, services, and applications through WAN-based geo-replication.
• Replication performance is significantly better for pull subscription scenarios than push subscriptions.
• The solution identified in this paper will not work for all applications, particularly applications that cannot handle the inherit latency involved with replicating data between geographically dispersed data centers."

To sum up, there are significant gains in terms of performance, scalability and disaster recovery implemented in SQL Server 2008 on Windows Server 2008. Not only will it be much faster than SQL Server 2005 on Windows Server 2003, but also much secure and cheaper. Therefore, SQL Server Replication technology will be considered a stronger solution inclusive for high availability purposes.
I hope you enjoy reading both document as they are a good read. That is all for now, let me know any remark you may have. Thanks for reading.

How to schedule a SQL Job to run every second in SQL Server 2005

It is well known that it is not possible to schedule a SQL Job to run every second or some seconds in SQL Server 2005 by using the wizard (I mean less than one minute). Nevertheless, when there is a need to do it we may have one way to achieve it. Let me expand on what I am saying.  Firstly, we have to create the SQL Job in SQL Server 2008 by using the Wizard and then we need to generate the script to create the SQL Job in SQL Server 2005.

Having successfully completing that will you be able to have a SQL Job in SQL Server 2005 to run every second with any hitches. I hope you find this tip very useful for you. That is all for now. Thanks for reading. Stay tuned.

SQL Server 2008: Row Value Constructor

Definitely, we were sometimes in a need of a new technique to insert data massively in SQL Server 2005 and previous versions. We used to do it by using traditional techniques like UNION ALL and individual INSERT statements. This story has ended up with the arrival of a new feature in SQL Server 2008 which allows to insert much data with only one INSERT statement, that is, Row Value Constructor.

To begin with, we are going to illustrate this new feature by creating a new table where four data rows will be inserted.

CREATE TABLE [Production].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileName] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileExtension] [nvarchar](8) COLLATE Latin1_General_CS_AS NOT NULL,
    [Revision] [nchar](5) COLLATE Latin1_General_CS_AS NOT NULL,
    [ChangeNumber] [int] NOT NULL CONSTRAINT [DF_Document_ChangeNumber]  DEFAULT ((0)),
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) COLLATE Latin1_General_CS_AS NULL,
    
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Document_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Document_DocumentID] PRIMARY KEY CLUSTERED 
(
    [DocumentID] ASC
)
) ON [PRIMARY]

At first, we will do it in SQL Server 2005. This is the traditional and old-fashioned way (one INSERT statement per row): 

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL)

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

This is the cumbersome way by using UNION ALL clause which has sometimes poor performance:

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
SELECT 'Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'
UNION ALL
SELECT 'Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL
UNION ALL 
SELECT 'Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'
UNION ALL
SELECT 'Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...'

Now in SQL Server 2008 we can use Row Value Constructor feature (only one INSERT statement to insert four data rows):

INSERT INTO [Production].[Document](  
      [Title]
      ,[FileName]
      ,[FileExtension]
      ,[Revision]
      ,[ChangeNumber]
      ,[Status]
      ,[DocumentSummary]
)
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'),
('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL),
('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'),
('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

As you have noticed, we do not need to use UNION ALL any longer, it allow to save a lot of time and work. It is truly useful when we need to insert much data and it also offers a remarkable performance. I hope you enjoy this new feature. Thanks for reading again!

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.

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.

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.

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.