New SQL BLOG

I am writting about my SQL and IT stuff on geeks these days...

Till I synch these two blogs please follow this link

MY OTHER SQL BLOG A BIGGER AND BETTER

Also I ahve my Space on MSN which has increased manifold in past few months

Hashims System Lock tricks

He should have been the Lockup Man in Tihar because he knows better ways to lock and unlock the system....

Here is a nice trick to lock your system as I found it in Hashims Blogs..Liked it alot...

If CTRL-ALT-DELETE seems like too much of a hassle, try this instead:
1. Right click an empty spot on the desktop, point to New and clickShortcut.
2. In the Create Shortcut dialog box, type the following into the Type thelocation of the item text box:"rundll32 user32.dll,LockWorkStation" // remove quotes while typing
3. Click Next.
4. In the Select a Title for the Program dialog box, type "Lock Desktop" inthe Type a name for this shortcut text box.
5. Click Finish.

Password Encryption in SQL 2000

/*
Written By :- Veer ji Wangoo (India) http://vsql.blogspot.com
Inputs from:- Dinesh Asanka(Sri Lanka) SqlServer Central
http://www.geocities.com/dineshasanka/index.htm
Dated :- 12 July 2004

A very simple and unique way to encrypt Password in your database application
using a function called pwdencrypt

Lets have look at it,we first create a table named [UserTab]
which will have two columns along with a UserTab .Note the data Type
varbinary(255) to include Hashed encrypted value in the Table
*/

CREATE TABLE [dbo].[UserTab] (
[ID] [int] IDENTITY (1, 1)
NOT NULL ,
[UserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varbinary] (255) NULL ,
) ON [PRIMARY]
GO


/*
Next insert the data with a usage pwdencrypt() --This is an Undocumented Function.
*/
INSERT INTO [UserTab](UserName,Password)
VALUES ( 'Vsql',pwdencrypt('Varsha'));

--When you select you will find the PWD in Encryted Form.

select * from [user]

--A Sp can be written with below logic and to retreive an d check the Password authentication
--We can ask the user to input teh USername and PWD into the Sp and check it with the matching Pwd
--If compare value comes as true then we give them access else we reject the access to th application

DECLARE @varPassword varbinary(255)
SELECT @varPassword = [Password] FROM [UserTab] where UserName = 'Vsql'
DECLARE @chkPassword varchar(255)
SELECT @chkPassword = 'Varsha'
if (pwdcompare(@chkPassword, @varPassword, 0) = 1)
Begin
print 'Get along'
End
else
print 'Permission Denied'

/*
Although this doesnt garuntee the Safegaurd of your passwords and
any hacking attacks but will certainly help in maintaining the integrity of the
applications.Like freaking with Usernames/passwords of any user by the people who has
access to tables in your Application Database

You can also use various Encryption algorithms availble in registry
and Devlopment Environments.
*/

The Monarch called Monad

Just last week this buzz word hit my head at South Ex Cofe Shop while talking to Sourabh..then all of a sudden things where getting carzy as Sudhakar mentioned in his comments on pooja BLog ("Your blog gonna be a one stop shop for Monad ")..Thats what Happened to me as well..

It was my final stop there..So To lok at originals as Pooja has put a Copyright sign on her Paper on Monad so I am redirecting you there
Click Here
Poojas MONAD Session

What UDP port does SQL Server listen on?

SQL Server's default TCP/IP port is 1433 and uses the UDP port of 1434. You cannot change the UDP port from 1434 to another, which is what helped the SQL Slammer virus spread so quickly. The UDP port is constantly listening on port 1434 and when ping responds with the TCP/IP port SQL Server listens on.

Default instances of SQL Server use TCP port 1433 by default. 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server. Named instances, however, dynamically assign an unused TCP port number the first time the instance is started. The named instance can also dynamically change its TCP port address on a subsequent startup if the original TCP port number is being used by another application. SQL Server only dynamically changes to an unused TCP port if the port it is currently listening on was dynamically selected. That is, if the port was manually selected, SQL Server will display an error and continue to listen on other ports.
The error messages are held in:
%systemroot%\program files\microsoft sql server\mssql\log
Any port errors are recorded in this file, which can be opened with notepad.
However, It is important to point out that dynamic port detection is ONLY available for named instances of SQL. The network libraries assume either 1433 or the global default port established with the Client Configuration Utility. If a default instance is listening on a port other than the standard 1433 port, you must provide an alias or alter the global default port. To change or allocate ports manually, you can either run server network utility, or edit the following Registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\instance_name\MSSQLServer\SuperSocketNetLib\Tcp
BUT you need to make sure that the now port is not in use by any other process. (Type
netstat -a -n
at the command prompt to get a list of the port numbers in use.)
The implications of changing the default ports are that the clients may not be able to see the server. SQL clients use DBNETLIB to detect the ports. DBNETLIB is always loaded by the ODBC or SQLOLEDB components. DBNETLIB is responsible for making either direct IP/SPX calls or forwarding requests directly to the Shared Memory, Named Pipes or other network libraries. The Client Configuration Utility has been extended in SQL 2000 to provide an option for dynamic port detection. When you enable the Client Configuration Utility, no port number is stored for the alias entry and DBNETLIB attempts to contact the server through a known UDP port to obtain the proper connection information.

Rendezvous with YUKON

Resources Covered: -

· Introduction
· Review of Features
· Enhancements
· DTS Redefined
· Deployment and Management
· Hand Shake of Brothers (.NET and YUKON) or Marriage of Romeo and Juliet.

Author: - Veer Ji Wangoo

Applies to : - SQL SERVER “YUKON” Beta 1

Target Audience :- SQL DBA,Solution Architects,IT analysts ,Developers etc

Introduction

The world of Programming had never moved so fast as it did in last couple of years. After Y2K the things got so fast that it became increasingly difficult for lesser mortals to keep the pace with changing technologies (particularly if one got stuck on some Legacy project).

But As I saw the only one way to update was to keep on reading the white papers (I wish I could start a daily newspaper for all IT professionals like Washington post). So alternative is the white paper like this.

Few Months back I heard about the YUKON and sooner I brought it under my Microscopic thought. So here it an attempt for a successful failure or failed success. However I would reiterate the fact that this paper is for educational and awareness purpose only.

The new version of Microsoft SQL server, named “YUKON” is a next generation robust, scalable, Reliable database for windows.

We can write a new bible on its marketing propositions and value editions, but we will concentrate more on technical stuff.

Review of Features

There is lot of activity going between the middle tier ADO.NET and the backend SQL 2000.With DOT NET CLR, it became more important for the DBAs to know what lies above their head and same for the Architects/Analysts/programmers etc to know what lies below their feet. As all of us know net Dot net environment is simply outstanding. But for this beautiful Building to stand the cross current of competitiveness it required a strong foundation (better than SQL2000), So here is what YUKON gave it.

The infrastructure of YUKON is divided into following components and
Constituents ,all working in tandem.

Some of then are being listed as

· SQL SERVICE BROKER
· REPORTIG SERVICE
· NOTIFICATION SERVICE
· SQL SERVER MOBILE EDITION
· WORK BENTCH
· DTS-WIZARD

Lets take a small trip around this YUKON park one by one. We will compare it with some other features currently available as well.

SQL Service Broker:-

For all those who have worked on MSMQ /PMQ and all other asychrnous mechanisms to make the applications more fault tolerant and ended up writing huge lines of code here is a good new for all of them.

Service broker gives a more scalable and fault tolerant architecture of message routing. It allows internal and external processes to send and receive messages using normal T-SQL.

Reporting Service: -

It is a complete server based platform for creating and managing reports from the data. It has lots of new API s that can be used to by developers .data providers to integrate reporting with the legacy systems as well as some third party tools. Major leap forward is the expansion of Microsoft Business Intelligence vision.

It is shipped with the Yukon along with the tools for creating, managing and viewing reports. It also has separate engine to host and process reports. During the set up it self it will ask for the reporting server and the virtual sites for the IIS to be created for reports. It is one of the best features that one can utilize .

Notification Service :-

For operation and administration of SQL we have been using notifications through alerts and operators. Sending E-mail / pagers /Netsends have been in SQL since long. But whats new is that it is a service of its own now. Added to that it can be subscribed for particular trigged action and reaction on data modification, Job out comes etc.we can send the notification to various devices as Mobiles/PDAs/Messenger services and off course Emails.

SQL Server mobile Edition: -

All of us are aware of SQL CE which shipped along with SQL 2000 .It is now renamed as SQL Server Mobile Edition and can be developed and managed using SQL Server “WORK BENCH’.
We can create the Mobile Edition database on the desktop or on the devices directly from the SQL workbench.

As such any database manipulations become independent of the device/site where the Database is residing, this includes the subscription and publication of database, making queries across to-and –fro to the devices. DTS into the mobile edition from non-SQL databases using Whidbey Applications has been made feasible.

SQL Server Workbench: -

One of the greatest and revolutionary features of this system is WORKBENCH. It is actually a DBA suite but has been made fine for developers as well.
There are two types of workbench BI-workbench and SQL server –workbench. Both are capable of doing similar tasks like developing projects and solutions. Point to clear here is that solution can contains more than one projects, while projects contain data sources, data source views,dts packages and other miscellaneous things like dml/ddl files.

The BI workbench doesn’t require direct connection to the SQL server RDBMS to design packages etc nor any connection to save your work.it works just like VS.NET project studio. one can implement VSS on to it and check for code modifications as well

SQL Server workbench unlike BI- workbench is aimed primarily at DBAs to manage SQL.Analysis and reporting servers. It also supports designing, executing and scheduling DTS packages, but does not include source control for packages saved on sql server (rather than file system)

To chose between two is entirely your choice. However key is if you want to execute the package etc directly put it on sql server workbench otherwise to develop, test and do cross platform development before the deployment into production then your choice is definitely BI-workbench.

It supports back up to SQL 7.0 and can be effectively used to connect and work with SQL Servers, Analysis services and Mobile Editions as well.

It is focused primarily on development, deployment, and Management. there are lots of constituents that are .We will just name and define some here.

1. Query Editor: - Replaces Query Analyser.
2. Xquery Designer: - Introduces support to write queries for xml.
3. Windows installer: - used by Yukon for installation but no more typical and minimal modes are there. But simple customization options are available.
4. Consistence Checker for set up - Set up configuration checker validates the target machine for deployment. In case of any issues it guides the installer for necessary action before deployment.
5. Failure reporting for set up: - creates log and directs user for corrective action.
6. Computer Manger: - Replaces Client network utility/Server network utility and SQL service manager; It is accessible through WMI (Windows management instrumentation). It helps to work with lots of things like Analysis Services, Previous versions of SQL Servers, Reporting services, MS cum Full text search etc
7. Object Explorer: - It is a combination of Enterprise manager and Analysis service manager.
8. Profiler and Tuning Advisor :- Much like profiler and index tuning wizards but lots of features like profiling Analysis services, saving traces/show plans as XML etc
9. SQL Server agent :- Much like earlier one but with few changes like creation of agent user roles ,creation of proxy roles and assignment of users/groups to these Proxy groups for monitoring/creating/editing of Jobs.

Enhancements

The enhancements in YUKON can be divided into two categories namely the Database Engine enhancements and Reporting service enhancements.However we will not go along these lines reason being there are other generic enhancements in yukon as well.
Some of the genric problems that we used to face like Server Reboot incase of change in SQL system parameters.the Database affinity for CPU and RAM memory may also no \w be altered without requiring the restatrt.

so we follow a conventional line to enhancements .

When we talk of enhancements the following categories have been mainly enhanced :-
· .net framework programing
· batches
· Data types

Lets take them one by one

1 .Net Framework :- we will take it up in another chapter But briefly describing it here.Just like any other Object we can now define and create a database object inside an instance of sql server that can be programmed in the microsoft .NET Framework common language runtime.As such all the properties of this object like Stored Procedures ,model functions,triggers,user defined types and aggregates can be done in rich CLR languages.We can create,alter and drop assemblies

2. Batches :- YUKON introduces a new feature called multiple active result sets (MARS).it allows client drivers to have more than one pending request per connection.It can interact with ODBC/OLEDB and batch execution environment


3. DataTypes :- There are new data types as well as enhancements on several existing datatypes.This is very important fom the common usage point of view.I will take some leverage to explain them here.
XML datatype:- helps you store and interact with the xml document or fragment.This data type can be in Instances of this data type can be ued in tables,functions,Sps,etc.We can put constraints on it by refercing the validating schema .As such we can specify Xquery against the xml data stored and apply inserts/updates/deletes to the data.
UTCDatetime :-is a datetime datatatype which has intellisence of time zone.As such for global operations of Ecom sites it becomes handy
Date:- datatype has been modified to the precision of 100 NS
Varchar(max)-Nvarchar(max)-varbinary(max) can hold data upto 2GB.so be free of 8000 limit of varchar.

4. Failover Clustering :- Yukon provides a high availibility support for server scope failures.With FailOver clustering the OS ad SQL server work in tandom to protect from failure by providing reduntant hardware and an automated mechanism to ove the database server to secondary hardware in the event the primary failes.Upto eight nodes we can map in ,depending on the OS version. Yukon has brought Analysis,notification,replication environment into fail over clustering as well.

5.Database Mirroring :- The basic work it does is shiping the trnsaction log to another server ie mirroring
the work don on primary.As such durng planned downtime DBA s can save al the downtime incurred in the past.BY usage with service broker applications can connect with secondary server within no time.Ulike failover clustering ,the mirrored server is fully cached and ready to accept workloads because of synchronised state.
One of the most imporant feature sof this mirroring is two way udation,which means synchronising the flow in both directions.It requires no standard server controls or SCSI disk of array s etc..

6. Database View:- Database view provides a read only ,stable view of a database that can be created with out any overhead,in case of any divergence / accidental changes View can be used to reapply the pages.

7.Replication :- Although muuch of SQL 2000 replication methodologies seems to be excellent.but adding to what we have already new features like enabling replication through http:// and https:// increases the availability of datat for mobile scenerios which enables synchronisation over the internet.New features like “Peer-to-Peer” model have been introduced to to make applications more scalable for SQL read workload.

8.Online Index Operations :- Gone are the days when any online reindexing wuld cause havocs with exclusive locks and bring the application to stand still.we can continue to make updates and perform queries against the data even during the rebuilding of clustered index.
Additionally the concept of parallel processing allows server to take the workload of online indexing
9.Online Restore :- Sql Server introduces the ability to perform a restore operations without getting the database offline.Only the data to be restored becomes offline not the whole database.However depending on the criticality of the suituation we can chose between the offline and online restore.
10.Fast Recovery :- Users can reconnect to a recovery database after the transaction log has been rolled forward.Earlier versions of SQL Server required users to wait until the incomplete transactiosn have been rolled back,even if the y didn’t need to access those parts of data .But key is that if you are access the shady data of uncommited transaction then normal blocking can be expected.

11. Mirrored backup:- In Yukjon backup media can now be mirrored ,upto four sets .which can be used in case of backup failing.

12. DBCC CheckSum :- Dbcc has been modified a lot to take into consideration the new feaures of yukon.But the exemplary to metion here is the dbcc checksum which verifies data at the page level by doing a checksum of each page,report the partitioning correctness and error free of registered assemblies etc.

13. ShowPlan and Deadlock Enhancements:- Now we can have graphical represntationm of deadlock occurances collected through trace events.The graphical shows dead lock cycle of chains,providing you means to analyse dead occurances.These results can be saved in XML formatAs such we can ship and view the plan without the underlying database.

14. SQLCMD :- A new command line utility replacing OSQL/ISQL.It is coming out with rich set of commaands, yet to be reviewed for writing in this paper.(So lets watch out on this)

15. Dedicated Administrator Connnection:- to access a running server even if the server is in no mood to talk to anybody (Hung or otherwise unavalable-like my Girl friend) ,It is hot wire connection and activated by varous memebers of the sysadmin role and is only available through the SQLCMD commmand locally as well as on remote machine.thus DBAs have a special access to server for diagnosis and treatment of SQL issues.

16. SQL management Objects (SMO): - To enhacne and extend the functioanlity of SQL server database and replication management through programing ,we have SMO object model in .NET environment.it replaces and extends the current DMOs.(they will stillbe there but without new features.).SMO is implemented as .net assembly by using WMI xobjects as before.So now all CLR features can be used to create applications that can help in the Online maintenance and adminsitration of SQl server.

17. Transact SQL :- previously all T-SQL was as per ANSI 92 stadards now YUKON implements ANSI 99 standards so we must expect lots of changes as new releases of yukon come along.many of the improvenments are based on the assumption that we need to be more expressive in querries and as such Microsft has taken lot of feed back from customer using T-SQL.there are lots of updates on this section beyong the scope of this article.For mention only Common table Exoression,recursibe queries,pivot and un pivot operators,realtional operators,outer apply ,error handling capability through try catch constructs..
18. Security :- a beautiful paradigm for security, both for developers and admistrators.However more details are likely to come in beta 2 .Currently lot of investment is being made in number of features covering a broad spectrum like enforcing policies for sql login passwords(authentication scope) with finer granularity in permissions (authorization scope ),to seperation of owners and schemas (in security management space)


DTS Redefined


DTS has been raw defined in the since that it is totally new and matured for enterprise development and deployment. The overall architecture of the product has changed a lot.
The designers have built several new wizards into workbench environment to help the DTS.

DTS Import/Export wizard

Much like as we have in sql 2000 but with some new features like status monitoring, control flow check, saving the result file as .dtsx or text file ,mailing it etc and new GUI as well.
However for the DTS packages created in 2k and below and you want them to run in YUKON. Then we are sorry to say you w\cant cum toll free. You will have to use migration wizard for it become YUKON complaint and run tax free…(everything comes at a cost)

DTS Package installer wizard

Now this wizard is something new. Here you can use BI-workbench to create a new deployment project where in you can set the deployment out put path where in your deployment files will be kept. You can have DTS packages, the DTSInstall.exe, the DTSdeploymentutility.Msi as well manifest file. Simple clicking of exe will call wizard and you are ready to deploy.

DTS Configuration wizard

As the word of this topic sound CONFIGURATION wizard guides you through the set up for creating configuration but what exactly is configuration? Well it is same like Dynamic Properties tasks in 2000. to configure the package variables and properties at run time. Only difference is that format is XML, they are external to package and values get set before execution rather than during run time. In any package assigned to a wizard, there can be more than one configuration however configuration lower in the list will get priority-i.e. The largest one.


Deployment and Management


If you ask any body how to deploy any Windows Application, Answer will be “click next”. That is true but that’s where most of us make the first mistake.

Although MS gives us easy deployment wizards but still there are lots of place where we need to think,analyse ,estimate and make decision. It is the foundation for getting good performance out of any application.

To get the best out of YUKON we must set configuration options properly, basic parameters and locations of files provides greater flexibility for application security and resource management.

We have Windows Installer like SQL2000 but minimum and typical modes are no longer implemented. Instead we have a feature tree with default options enabled and administrators can then customize by selection and clearance of items involved on feature tree. Remote set up and Multiple installations are also available in this wizard.

Coolest feature is the Setup consistency checker which checks the target computer for all necessary infrastructure before setup begins. In case of any discrepancy, It will give necessary instructions and actions to be taken. It uses WMI technology to do the same.

Even if the setup fails the error codes are well trapped ,with full description of error along with recommended corrective action


Hand Shake of Brothers

As I mentioned in the review about the tighter integration of CLR and SQL in Yukon. Some of it got its mention in enhancements section also.
I am putting a separate article on this issue as I this is a vast area to summarize here.

Basics Of RSS technology

Since last two mails I was wondering as to what is this RSS Stuff going:-
Very confusing word ???

I was wondering how cum our technical group went into political doldrums...
No wonder as they say lesser mortals think a lot...

So here we break the jinx.I have collected some info and links to
some sites that will make you understand the whole crux of the RSS technology.

What is RSS?

RSS (Rich Site Summary or Really Simple Syndication ) is a format for delivering summaries of regularly changing web content. Many news-related sites, weblogs and other online publishers syndicate their content as an RSS Feed to whoever wants it. usually it located by a simple XML sign board .

If you frequent Weblogs, you've seen the little XML icons inviting you to "syndicate this site", but what does that really mean?

A long time ago, newspaper managers realized that if they could use articles and stories from other newspapers in their paper, they could garner more readers because they could cover a wider area than they could with just their own reporters.

This is an example of how syndication can work in print.
Online, there are potentially millions of authors writing about millions of topics each day. It can be very difficult to keep track of without some type of automated system.

And that's where RSS comes in. Really Simple Syndication (RSS) is an easy way for Web sites to share headlines and stories from other sites. Web surfers can use sophisticated news readers to surf these headlines using RSS aggregators.

Why RSS? Benefits and Reasons for using RSS ?

RSS solves a problem for people who regularly use the web. It allows you to easily stay informed by retrieving summaries of the latest content from the sites you are interested in. You save time by not needing to visit each site individually. You ensure your privacy, by not needing to join each site's email newsletter.

The number of sites offering RSS feeds is growing rapidly and includes big names like Yahoo News and Amazon.com.
What do I need to do to read an RSS Feed? RSS Feed Readers and News Aggregators
Feed Reader or News Aggregator software allow you to grab the RSS feeds from various sites and display them for you to read and use.if you have DOT NET Framework 1.1 installed, you can download www.sharpreader.com and use it.

A variety of RSS Readers are available for different platforms. Some popular feed readers include Amphetadesk (Windows, Linux, Mac), FeedReader (Windows), and NewsGator (Windows - integrates with Outlook).
Once you have your Feed Reader, it is a matter of finding sites that syndicate content and adding their RSS feed to the list of feeds your Feed Reader checks. Many sites display a small icon with the acronyms RSS, XML, or RDF to let you know a feed is available.

Here is where you can configure RSS support for SQLCON

Thanks a lot Sudhakar and Ansari...for brining in some info