Satheesh Kannan Chandra's Blog

Database servers are the most important servers any enterprise company owns. They store client details, financial information, human resource details - all the data that keeps the company in business and, as such, they need to be secure. But probably we already knew that they are subject to wide variety of attacks. British Computer Society lists the most critical of these attacks. The to 10 attacks on Database servers are:

1. Excessive privileges
2. Privilege abuse
3. Unauthorized privilege elevation
4. Platform vulnerabilities
5. SQL injection
6. Weak audit
7. Denial of service
8. Database protocol vulnerabilities
9. Weak authentication
10. Exposure of backup data

You can visit this link for more info about the attacks.

One more nice reason to migrate to SQL Server 2005 is Scalable Shared Databases (SSD). Microsoft explained about this feature in the KB article 910378 (http://support.microsoft.com/?kbid=910378).

In SQL Server 2000 and older versions, any database is accessible to only one instance. The database can not be shared with the other instance. However, SSD allows us access the same database hosted on SAN from several server instances. That means that read-only workloads can be scaled up by adding additional servers accessing the same database.

This is the excerpt from the Microsoft site about Scalable Shared Databases (SSD).

As usual, I have blogged this because I was confused, and I hope that I am trying to get clear about the confusion. ;-)

Enterprise level database instances use databases in massive size and it requires larger RAM to process those data. Most of the SQL Servers are running under 32 bit Windows server operating systems, they can directly address up to 4GB of memory. However, to cater current enterprise level applications SQL Server requires memory more than 4GB.

Suppose if we have 4GB RAM, Operating system gets 2GB and the remaining 2GB allocated to user applications. Hence, (without any memory configuration settings), SQL Server can use upto 2GB RAM. However, by using /3GB option in Boot.ini file, SQL server can use upto 3GB of RAM. The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively.

SQL Server 2000 Enterprise Edition introduces support for the use of Microsoft Windows 2000 Address Windowing Extensions (AWE) to address approximately 8 GB of memory for instances that run on Microsoft Windows 2000 Advanced Server, and approximately 32 GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system.

The maximum amount of physical memory addressable by a 32-bit addressing mode is 4 GB. All processors based on the IA-32 architecture that begin with the Intel Pentium Pro, support a new 36-bit physical addressing mode known as Physical Address Extension (PAE). PAE allows up to 8 GB of physical memory on Windows 2000 Advanced Server and up to 32 GB of physical memory on Windows 2000 Datacenter Server.

Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition can use up to 2 GB of physical memory. With the use of the AWE enable option, SQL Server can use up to 4 GB of physical memory.

To configure Windows 2000 (Advanced or Datacenter), you must enter one of the following switches (depending on the size of the physical memory you have in your system) in the boot line of the boot.ini file, and reboot the server (mentioned below)

4 GB RAM --> /3GB
8GB RAM --> /3GB /PAE
16GB RAM --> /3GB /PAE
> 16GB RAM --> /PAE

Be aware, If your server has 16GB or less of RAM, then using the /3GB switch is important. But if your server has more than 16GB of RAM, then you must not use the /3GB switch. The reason for this is because the 1GB of additional RAM provided by adding the /3GB switch is needed by the operating system in order to take advantage of all of the extra AWE memory. In other words, the operating system needs 2GB of RAM itself to mange the AWE memory if your server has more than 16GB of RAM. If 16GB or less of RAM is in a server, then the operating system only needs 1GB of RAM, allowing the other 1GB of RAM for use by SQL Server.

Once we have changed the BOOT.INI option according to your RAM size, now it is time to enable in AWE option in your SQL Server. We can enable the AWE option by using T-SQL.

'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO


One caution about using the "awe enabled" setting is that after turning it on, SQL Server no longer dynamically manages memory. Instead, it takes all of the available RAM (except about 128MB which is left for the operating system). If you want to prevent SQL Server from taking all of the RAM, you must set the "max server memory" option to a figure that limits SQL Server to the amount or RAM you specify. Change the "max server memory" option by using the T-SQL,

sp_configure 'max server memory', 6144
RECONFIGURE
GO

Note:

1. To use Address Windowing Extensions (AWE) memory, you must run the SQL Server 2000 database engine under a Windows account that has been assigned the Windows lock pages in memory administrative credentials.

2. You cannot allocate more than 4 GB of physical memory to an application on Windows 2000 Server because Physical Address Extension (PAE) is not available on Microsoft Windows 2000 Server. Also, you cannot use the 3 GB switch in the Boot.ini file with Windows 2000 Server

3. When changing the "max server memory" option in SQL Server active-active cluster environment, consider the fail over situation. When an instance fails over to the available node where already an instance is running, the second instance also competes for memory with first instance. If we have 8GB of RAM in each node, then I configure the max memory option to 3GB for each instance. So in case of fail over, both the instances can work comfortably. (3GB for Ist SQL, 3GB for IIns SQL and 1 GB for OS)

4. When you apply SP4 against the AWE enables SQL Server instance, the SQL Server can recognize only half of the amount of memory available. So, if you have 16GB, SQL Server will only use 8GB. Fix is available in this link http://support.microsoft.com/kb/899761

An initiative by one of the leading educational names in technology studies, the Massachusetts Institute of Technology (MIT), has launched its OpenCourseWare project which effectively gives free access to course materials from a number of courses from MIT including Computer Science.

Visit http://ocw.mit.edu/OcwWeb/index.htm for more info.

Microsoft Windows finally surpassed Unix in the Operating System market share in terms of revenue with a very little margin gap. According to IDC server report, computer makers sold $17.7 billion worth of Windows servers worldwide in 2005 compared with $17.5 billion in Unix servers. For more interesting facts about server market read the article

http://news.com.com/Windows+bumps+Unix+as+top+server+OS/2100-1016_3-6041804.html

Unlike unix world, Microsoft world is not much finicky about alphabet’s case. It really doesn’t matter about in which we execute commands. Mostly, it accepts all phase of alphabets (lower, UPPER and mIxEd). As like other MS products, SQL Server do accepts commands in all cases. However few SQL commands are case sensitive. For example BCP command. This command-line program’s parameters are case sensitive.

In contrary to windows user name and password, SQL Login name and password are generally not case sensitive, but it depends on the SQL Server’s collation setting. If the SQL collation is case sensitive, then SQL login passwords also case-sensitive. In a case sensitive SQL Server or Database, all comparison operators, SQL identifiers are sensitive about case.

… WHERE name = ‘SQL’ is different than … WHERE name=’sql’
… WHERE NAME = ‘SQL’ is different than … WHERE name = ‘SQL’

So, if the business is not much concern about the alphabet case, then we need to chose a collation which named like CI (case Insensitive) rather than CS (case sensitive).

/* */ is the one among the two commenting commands available in T-SQL Server which is used to comment a paragraph (block comment). The other – (double dash) is used to comment a line (commonly known as in-line comment).


Including GO command inside the block comment ( /* ….. */ ) invalidates the comment and generates an error. We can’t embed GO command within the block comments. If we want to do so, then we need to put an inline (--) command before the GO signal.


/*
select @@version
go
*/


The above block comment throws errors as:

Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.


However, adding an inline comment before the GO signal makes SQL Server parser to treat the comments as comments.

/*
select @@version
--go
*/

It is something different than SQL Injection attack...

"SQL used for evil" blog entry shows how a malicious use of T-SQL which will cheat DBAs and make them to think something really went wrong at the SQL Server.



Fellow DBAs... beware of this kind of DoS attacks.

Due to change in my career track, there will be some break in blogging. Consider this as my downtime / maintenance window of my blogs.

WILL BE BACK SOON!!!

United States Computer Emergency Readiness Team (US_CERT) has announced the year 2005's security vulnerabilities index. You can find the vulnerabilities report form the link http://www.us-cert.gov/cas/bulletins/SB2005.html.

There were 5198 reported vulnerabilities : 812 Windows operating system vulnerabilities; 2328 Unix/Linux operating vulnerabilities; and 2058 Multiple operating system vulnerabilities were listed in the index.

Microsoft is gratifying student community with Microsoft Student. Here is the excerpt from microsoft site about the product.

"Sometimes the hardest part about doing homework is getting started. Learning Essentials for Students provides assignment-specific guidance in Microsoft Office applications such as Microsoft Word, PowerPoint®, and Excel. With curriculum-based templates, tools, and tutorials, Learning Essentials helps with a variety of subjects and assignments, from history reports and essays to physics projects and chemistry labs.

Microsoft Student is a comprehensive, subject-specific resource designed to help middle- and high-school students complete high-quality homework assignments and projects in less time. With curriculum-based templates and tutorials, a fully functional graphing calculator with 2-D and 3-D four-color graphing capabilities, trusted content, and tools for cutting through online clutter, Microsoft Student 2006 provides students with the tools and information they need to achieve academic success."

To find more info about MS Student, visit… http://www.microsoft.com/student

This is time to look back. We are at the end of the year 2005. Here is the top five innovations or technical advancements in the year 2005 that made researchers, software engineers, and entire communities to find innovative ways to take advantage of computing's growing power and falling costs.

Also go thru this interesting article "Life after Silicon" about Nanotechnology which may replace silicon or any semiconductor technology from the chip making industry.

“FOR” command in Windows (not a T-SQL FOR statement) is a useful statement. I am using this command in almost all of my windows and SQL Server administrative script requirements. Here is the one use of FOR command in SQL Server environment.
I had a requirement to automatically load all SQL scripts from a particular folder into a SQL Server. For this requirement I preferred batch script, which use FOR command. We have scheduled a job in SQL Server with the batch file that loads SQL Scripts into the SQL Server.

You can find the batch file from the following link.
http://www.skchandra.com/batchfiles/load_scripts.zip

The batch file uses the Windows authentication method and loads all SQL scripts with .SQL extension from the folder where the batch file resides.

To assign a value to a variable we use either SELECT or SET statement based on our preference. Is there any difference between the two assignment statements? I can see few differences when using these two statements. They are:

• We can use SELECT statement when we assign values to more than one variable. However SET can be used to assign value to one variable at a time.
• SELECT statement works well with record sets, hence it won’t throw any error when we assign a record set value to a variable. Whereas SET statement throws an error if we try to assign record set value to a variable if the record set returns more than one value.
  
  USE northwind
  GO
  DECLARE @selectVar varchar(40), @setVar varchar (40)
  SELECT @selectVar = LastName FROM Employees
  SELECT @selectVar
  SET @setVar = (SELECT lastname FROM Employees WHERE EmployeeID = 1)
  SELECT @setVar
  
  In the above code, SELECT assignment statement accepts the record set and assigns the last value to the variable selectVar. However, if we use SET statement without limiting the result of the SELECT statement to a single value (by using where clause), then the SET statement returns an error.
• SET statement is ANSI standard where SELECT is not.

# posted by Satheesh Chandra @ 7:06 PM 0 comments

Within a day “Upma” became a favorite dish for techies those are in Bangalore. Yes. This is the South Indian breakfast item which Bill loved to eat in his Indian tour. “Launch Tour 2005” was the most awaited event of this year for Microsoft technology practitioners. Nearly 5000 IT professionals gathered in Bangalore palace grounds to see their hero. Mr. Gates presented the event’s key note and then participated in QA section. The crowd cheered when Bill remarked about India’s first religion Cricket. He announced a contest program “http://code4bill.com”for student’s community, through which Microsoft providing an opportunity for the bright talents to work in Redmond. Totally it was a nice experience for me to attend the event.

Visit The Notorious Bill Gates for his view on Bangalored Jobs.

One of my friends wanted to know the way to find statistics in a table. There are many ways to find the statistics in a table; I mostly prefer to use the following query.


select
object_name(id) as [Table / Index],
name [Statistics Name]
from sysindexes
where indexproperty(id,name,N'IsStatistics') = 1

Out of which, the system generated statistics always begins with '_WA_Sys_'. So, we can extract the system generated statistics by using the following query.

select
object_name(id) as [Table / Index],
name [Statistics Name]
from sysindexes
where indexproperty(id,name,N'IsStatistics') = 1
and
name like '_WA_Sys_%'

SQL Server 2005, Visual Studio 2005 and Biztalk Server 2006 Indian Launch programs are starting form 9th Dec 2005.

Get Ready for Bill Gates!

Bill Gates is specially visiting Bangalore on December 9, 2005 to kick off the India Launch of SQL Server 2005, Visual Studio 2005 and BizTalk Server 2006.


If you are a Bangalorean register for the event at http://www.microsoft.com/india/ready2005/blrmain/Register.aspx

I came across a web page related to Bill Gates; my favorite entrepreneur and a great techie. This page is from MTV News about Bill Gates. The most attractive part for me in this page is, Bill Gates view on Bangalored jobs. Here is the excerpt from the page.


Yago: Before we reach that day, certainly I know a lot of people in high school and college are hearing a lot about how India and China will take over a lot of American jobs. What do you say to that generation of young people now that's in college, that's now in high school or approaching high school?

Gates: India and China advancing and getting rich is fantastic news. What that means is that people who have been living in poverty, had ill health and illiteracy, are now getting jobs that allow them to be educated and realize their potential. If we had a choice today where India and China would be as rich as the United States, we should all want that, because not only would it be great for them, but they'd be buying more of our products. ... Their advancing isn't taking away from a finite pool of jobs. What it does is it grows the global economy. It does mean that we have to renew our skills, renew our leadership, and that largely means investing in the education system. So it doesn't have to be a bad thing, it just highlights that we've underinvested in education and in fact other countries do a better job.

At last, I have installed SQL 2005 CTP in my system and trying to get comfortable with Microsoft's most powerful database platform for the enterprise available in the market today.

I am planning to concentrate on What's New in SQL Server 2005. One such whats new feature is SQLCLR. To find more information on this, I have googled into some of my favorite sites. The one of such I found is Kimberly's white paper on SQLCLR. We can view the document in its pre-live version. Lot of information in this page... Hope, I grasp the information in the document in the coming days.

.
A geek discovered that nobody can't create a folder anywhere named as "con" in windows.

This is something pretty cool...and unbelievable... At Microsoft the whole Team, including Bill Gates, couldn't answer why this happened!

Try it out yourself...

This is a forward email I recieved from my friend some days ago. As the word CON is very familiar word to me, I thought of not to overlook the mail. When I read through the mail contents, it is just an anti-Microsoft bigot’s mail.

In those DOS days, the devices are given with constants, like…
CON --> for CONsole output device (that is Video display)
PRN --> for Printer
LPT1, LPT2,… --> for printers (those days Line Printers use these LPT ports)

In the pre-windows age, we use redirectors along with these device constants in our batch scripts. Say for example,

COPY CON filename is to create a new file. What this command doing is, COPYing CONsole contents into a file. All the contents entered after the command will redirected to the file.

Like wise, COPY filename PRN is the command to take a printout of the file.
COPYing the file contents into PRiNter. Similarly, we can use COPY filename LPT1 for printing.

As like in UNIX, we can use these device constants with redirectors and filters.

TYPE filename > CON -->Display the contents of the file into screen.
TYPE filename > PRN --> to take a printout of the file.

Seeing that the device constants are reserved words, we can’t use these words as a file name and they are features of DOS; not a bug.

We often use a dot “.” or a string “(local)” to connect the default instance in the local (stand alone) SQL Server. However this shortcut doesn’t work with cluster virtual (SQL) server. We need to give the actual virtual (SQL)server name. When we use these shortcuts, SQL Server resolved this as a local system. That is, it resolves the shortcut as a host name of the server. In the standalone default SQL instance, the Windows command HOSTNAME and T-SQL command SELECT @@servername gives the same result.

In clustered SQL Server environment, the shortcuts points to the node name which owns the virtual SQL Server not to the virtual instance name. Consequently, the shortcuts do not connect. Here, the Windows command HOSTNAME and T-SQL commands SELECT @@servername doesn’t give the same result. Still, if we want to use the shortcuts then we need to create server alias with shortcut literals as the alias name using “Client Network Utility”.

In DTS, selecting (local) in the server list rather than the name of the SQL server will enable the package to connect to the local server regardless of where the package is executed. If, however, we can specify a particular server name in the package and execute the package on a remote server, the package will attempt to connect to the original server rather than the local server. Understanding the effect of the server name in a package will enable us to ensure the package executes the way it was intended.

Books on Line (BOL) taught us how to compare NULL in T-SQL statements. Here is the excerpt from BOL about NULL comparison.

“To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.”

But, Query Builder tool which comes with SQL Server is not smart enough when it deals with NULL. ;-) The Query Builder does not know to change =NULL to IS NULL when performing NULL comparisons. So, when we build query with query builder, change "=NULL" to "IS NULL" in the query manually.

There is more than one way to achieve a solution. Here is some of the “more than ones” from Production DBA’s point.

To find the SQL Server start date and time

Way1:

SELECT crdate from master.dbo.sysdatabases (NOLOCK) WHERE name = 'tempdb'

Way2:
SELECT login_time FROM master.dbo.sysprocesses (NOLOCK) WHERE spid = 1


To find the file type from sysfiles system table.

Way1:

SELECT filename,
CASE
WHEN (64 & status) = 64 THEN 'Log'
ELSE 'Data'
END AS FileType
FROM sysfiles (NOLOCK)


Way2:

SELECT filename,
CASE
WHEN groupid = 0 THEN 'Log'
ELSE 'Data'
END AS FileType
FROM sysfiles (NOLOCK)


------------------

Mathematics teaches average is sum of elements divided by number of the elements.

I.e.,
Average = SUM(elements) / NUMBER(elements).

Let us try to represent this into a T-SQL statement. SUM() function can be used to find the sum of elements and COUNT(*) function can be used for counting the elements. In the other hand we have AVG() used to find the average.

So, SUM(column) / COUNT(*) should be equal to AVG(column). But the reality is, they are not always equal. Let we try this T-SQL code,

-------- Begining of Code -------
CREATE TABLE #tbl_elements (element int)
GO

INSERT INTO #tbl_elements VALUES (5)
INSERT INTO #tbl_elements VALUES (10)
INSERT INTO #tbl_elements VALUES (NULL)
INSERT INTO #tbl_elements VALUES (15)
GO

SELECT
SUM(element)/COUNT(*) AS [Average through Formula],
AVG(element) AS [Average Through Function]
FROM #tbl_elements
GO

DROP TABLE #tbl_elements
GO
-------- End of Code -------

The result of the T-SQL statement is:

Average through Formula - 7
Average Through Function - 10


Why this difference???

The aggregate functions ignore NULL values when aggregating a column which contains both NULL and non-null values. COUNT(*) aggregate function is an exception. This function includes NULL values when it counts. If we need to exclude NULL values in the counting, specify the column name instead of ‘*’ in the function. That is COUNT(*) will count all elements including NULLs. However, COUNT(column) counts only the non-NULL values.

Now, let us re-write the T-SQL code as,

SELECT
SUM(element)/COUNT(element) AS [Average through Formula],
AVG(element) AS [Average Through Function]
FROM #tbl_elements
GO

We need to give extra care when we deal with NULL values to avoid this kind of mistakes.

Continuation of SQL Server - Application role - I...

Application role is one of the undervalued features available in SQL Server 2000.
I think most of the DBAs neglecting to utilize this feature in their SQL Server security setup. Some of the special characteristics of the application roles:

• Unlike other roles (fixed server roles, fixed database roles and user defined roles), application roles do not contain any members in it.

• To activate application role, it requires a password.

• When a user activates application role, his connection (only that session) get associated with the application role. Hence the session loses its all permissions and inherits permission from the application role.

• Application roles are database specific (the application roles are available only to the database they exist).

• Once it is associated with the standard SQL login (NT login or SQL Login), that SQL user session can not access the objects in other database.

• The associated session can access the other database objects only through GUEST granted permissions. Hence, if the guest user account does not exist in a database, the session can not gain access to the database.

• The associated session does not permit to execute “USE DATABASE” T-SQL command. Therefore, the session needs to address the other database object using three part naming convention.