|
69. Security in SQL Server 2005 - Part I
|
|
I have seen many articles about security and have seen customers get really paranoid when security breaches occur. Obviously, they have a valid reason to do so especially when the concern is about data security. In this digital aided world, everything boils down to just how can we keep our personal or company related data (which can be LOB data) in a secure manner and what are the tips and techniques we have in hand. This article will provides some of the guidelines and considerations for designing instance-level, database-level, and object-level security policies, and for defining security monitoring standards for instances and databases. Though not exhaustive, this will surely be able to form some starting base to work with in SQL Server.
|
|
Posted On : May 24, 2008
|
More...
|
Author : Vinod Kumar
|
|
|
|
68. Archiving Data with SQL Server 2005
|
|
Though HDD prices have fallen well beyond imagination, archiving of data and keeping the database to a manageable size is a concern for all organizations. Storing obsolete data online reduces the performance of a database server. A well-designed archival strategy minimizes the performance problems that are caused by maintaining excess data. When designing an archival strategy, you should first determine how much data can be archived, then choose an appropriate structure for the archival data, and finally create a plan for archiving the data.
|
|
Posted On : Jan 2, 2008
|
More...
|
Author : Vinod Kumar
|
|
|
|
67. Database Storage with SQL Server - Part II
|
|
With Part I as foundation let us take a step closer to other aspects to the storage system like what needs to be taken care when working with physical files, filegroups, transaction log design, SAN / RAID and more. We will cover as much as possible and let me highlight some of the best practices on the same lines.
|
|
Posted On : Sep 15, 2007
|
More...
|
Author : Vinod Kumar
|
|
|
|
66. Database Storage with SQL Server - Part I
|
|
Database storage is one of the primary failure point in the database environment. Hence, making the storage architecture consistent and at the same time giving it maximum redundancy must be embedded within the design itself. When I call redundancy, I dont specifically say implementing anything specific to RAID or SAN or other mechanisms. Redundancy must be achieved given your hardware and budget currently in hand if it even means making just multiple backup copies at the simplest level.
|
|
Posted On : Aug 10, 2007
|
More...
|
Author : Vinod Kumar
|
|
|
|
65. Understanding - DBCC SHOW_STATISTICS
|
|
Most SQL Server internal is undocumented, and even if they are documented there is hardly any information anyone can get out of these. So here is one such attempt where I take the DBCC SHOW_STATISTICS command and try to understand what it is trying to give us as useful information. I have at many occasion used these output to say if our indexing is effective or not.
|
|
Posted On : Jul 4, 2006
|
More...
|
Author : Vinod Kumar
|
|
|
|
64. SQL Server Management in SQL Server 2005
|
|
As more companies try to embrase the new version of SQL Server they will surely have their hands on SQL Server 2000 servers too. When I first saw this new tool I was amazed to see how this new tool tried to give the best of both the worlds as Query Analyzer and Enterprise manager. With due respect there is lots to learn from using this tool and interestingly I would recommend you using this for mnaging SQL Server 2000 machines too. So lets get started with some of the feature available.
|
|
Posted On : Apr 16, 2006
|
More...
|
Author : Vinod Kumar
|
|
|
|
63. SQL 2005 - TRY-CATCH - A boon to developers
|
|
This has been a topic worth taking a second look from a developer point of view. Even though it is such a small feature to dissect from a usage perspective we need to understand why something thing was brought on first place. In this article we will take a step back and look where SQL Server 2000 @@Error failed and why it is all the more reason for us to move towards this new model.
|
|
Posted On : Apr 04, 2006
|
More...
|
Author : Vinod Kumar
|
|
|
|
62. Reserved Resource Database in SQL Server 2005
|
|
Also called as RDB this is a new concept in SQL Server 2005 which I thought was worth taking a look at. In previous version of SQL Server 2000 whenever we had to upgrade a system defined object we had to drop and recreate. And rollback of the same was also something that we as DBAs had to break our heads. All the service packs have a disclaimer that states we backup our databases before we start the installation sequence. Even though I recommend it strongly, let us take a tour of how the new reserved Resource Database improves this current process and how we can captilalize this change.
|
|
Posted On : Jan 17, 2006
|
More...
|
Author : Vinod Kumar
|
|
|
|
61. Introduction to DMVs and DMFs in SQL Server 2005
|
|
Diagnosing problems in SQL Server 2000 has always been a point of concern from both developers and DBAs. More often than not we would have had a need to use undocumented and DBCC commands which are sometimes very difficult to understand too. SQL Server 2005 on the contrary is like a open book, no need to use bit based operations and undocumented column values. Welcome the introduction of Dynamic Management Views and Fuctions a.k.a DMVs and DMFs.
|
|
Posted On : Nov 6, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
60. New Sequence Number in SQL Server 2005
|
|
SQL Server 2005 is loaded with features and in this article we will look at yet another function introduced in SQL Server 2005 . This function is called as NEWSEQUENTIALID().
|
|
Posted On : Nov 5, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
59. Making the Most of the CLR
|
|
The integration of the common language runtime (CLR) in SQL Server 2005 opens up so many new possibilities that a developer might not know where to start. Here are three ways you can start taking advantage of SQL Server 2005's enhancements.
|
|
Posted On : May 02, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
58. sqlcmd Utility in SQL Server 2005 - Part II
|
|
Every new version of software will introduce new tools with its release. And in SQL Server 2005 there is absolutely no dearth of tools to talk on. Even though everyone would like to see and use the graphical user interface for most of the activity, there are sometimes these cool command prompt tools that we miss out. In continuation of that thought, I would take a quick walk through the sqlcmd tool that got introduced in SQL Server 2005.
|
|
Posted On : May 02, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
57. Using CLR features in SQL Server 2005 - Part 1
|
|
The next version of SQL Server, code-named Yukon, includes quite a few enhancements and expanded language support. If we were to list some of the key features introduced in this version then the list would run into lengthy pages. But the .NET integration is surely something that captures the imagination of developers. SQL Server 2005 can execute user-defined functions, stored procedures, and triggers written in common language runtime (CLR) targeted languages, including Visual Basic .NET and C#. In this article I will walk through the basic steps in creating our first CLR object.
|
|
Posted On : May 02, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
56. sqlcmd Utility in SQL Server 2005
|
|
Out of the many utilities that SQL Server 2005 has come up with I thought to take a sneak preview of the command line tools that got introduced with SQL Server 2005. Yes, sqlcmd Utility. Even though the intend is to use sqlcmd moving forward rather than the conventional osql command, we will take a quick tour of how we can use sqlcmd utility interactively, execute scripts and more.
|
|
Posted On : April 19, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
55. Security for CLR Assemblies in SQL Server 2005
|
|
With the support for managed code inside the SQL Server database, Microsoft has developed special security settings to protect the CLR's important new objects, assemblies. Assemblies are basically managed Dynamic Link Libraris (DLLs) that contain metadata and information about dependencies and are used as a deployment unit. Because assemblies are stored in the database, they also get backed up and restored with the database.
|
|
Posted On : April 15, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
54. Deadlock Detection in SQL Server 2005
|
|
The amount of features introduced in SQL Server 2005 are numerous. I think we have taken a tour of some T-SQL enhancements, DTS a.k.a. SSIS, XML and others before. But in this article we will take a look at another toolset that is available in SQL Server 2000 world but has been enhanced in this version, the Profiler. In this article we would look into an specific area of profiler enhancements, detecting dead locks in SQL Server 2005.
|
|
Posted On : Feb 8, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
53. Snapshot Isolation in SQL Server 2005
|
|
SQL Server uses isolation levels as a means to give us consistent data across multiple user accesses at the same time. There is generally a tradeoff with the fact that as we get higher up the isolation level the consistency of data is high and more is the reliability. But there is a caveat that we will be running low on the concurrency. SQL Server has had four levels prior to SQL Server 2005 namely Read Uncommitted, Read Committed, Repeatable Read and Serializable. I leave it to readers for the exact definition as these are found in SQL Server Books Online (SQL BOL). But in the next version of SQL Server 2005, we have a new Isolation Level called as "Snapshot Isolation level". In this article we will take a preview into the how and what's of this feature.
|
|
Posted On : Feb 8, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
52. EXECUTE AS Option in SQL Server 2005
|
|
SQL Server 2005 has introduced loads of features on the lines of security. Infact more implementations have been presented to users using SQL Server 2005. And one such feature is EXECUTE AS option. This option is an extension to what ownership chaining used to be in SQL Server 2000. You can check the BOL for specifics on the ownership chaining. But in this article I will walk through the new variations introduced with EXECUTE AS.
|
|
Posted On : Jan 6, 2005
|
More...
|
Author : Vinod Kumar
|
|
|
|
51. DDL Triggers in SQL Server 2005
|
|
There are loads of features we can explore in SQL Server 2005. Here is one such feature that I personally would call it as the DBA's requirement. Here in this article we will take a snapshot to what DDL triggers are and how it can be extended to our needs. DDL Triggers are new in SQL Server 2005. Fundamentally SQL Server 2005 allows us to existing set of available triggers to the next level. In these trigger we can execute a trigger for all available DDL statements in the system.
|
|
Posted On : Dec 14, 2004
|
More...
|
Author : Vinod Kumar
|
|
|
|
50. Indexes in SQL Server 2005 - Part II
|
|
We had taken a quick tour in our Part I about indexes. But that were some of the cool features that were introduced in SQL Server 2005. But there is more to this topic the more we explore the deeper concepts get. In this article let me take a quick tour to what ONLINE indexing is in SQL Server 2005.
|
|
Posted On : Nov 23, 2004
|
More...
|
Author : Vinod Kumar
|
|
|
|
49. Try Catch Block in SQL Server 2005
|
|
Error handling in T-SQL has been in question for quite sometime now in SQL Server 2000. It has not been user-friendly and more often than not most of the developers have had doubts in the way it used to work. Now the good news is in SQL Server 2005 this new feature of TRY-CATCH is more like the way it works in many programming language. I have to admit this is the new rich error handling capability for T-SQL world.
|
|
Posted On : September 28, 2004
|
More...
|
Author : Vinod Kumar
|
|
|
|
48. Introduction to DTS in SQL Server 2005 - Part II
|
|
One of the key features introduced in SQL Server 2005 is the new DTS platform. With the new release of SQL Server Beta 2, you must be eager to get your hands dirty on this new amazing platform. This product does bring in a new perspective and new thinking the way we used to work with DTS. Most of the DTS architecture has undergone dramatic changes. In this article I will walk through an step-by-step easy uploading of a given datafile into SQL Server. This new version of DTS does go beyond the ETL (Extract, Transform, Load) tools definition. You can orchestrate your code and create an workflow with the same. DTS in Yukon is more manageable, usable and more mature from its previous version.
|
|
Posted On : September 27, 2004
|
More...
|
Author : Vinod Kumar
|
|
|
|
47. Indexes in SQL Server 2005
|
|
The use of indexes in the relational world is magnitude of importance. And nevertheless in SQL Server the usage of Indexes is very important. Understanding the usage being one side, optimizing and using this is the other aspect. In this article my key focus will be on introducing some of the basic enhancements brought into SQL Server 2005 for indexes. In this article we will see two of the enhancements namely Disabling Indexes and Including non-key columns (my favorite).
|
|
Posted On : September 7, 2004
|
More...
|
Author : Vinod Kumar
|
|
|
|
46. ROW_NUMBER in SQL Server 2005
|
|
This is yet another cool feature in SQL Server Yukon.It has been in the SQL Server 2000 wish list for quite sometime. But I am excited to see this T-SQL enhancement make it into the SQL Server 2005 version for sure. What is this requirement? It is to generate a pseudo column as the query optimizer sends the output. This generated column can be used for various requirements and used in the UI layer for some identifier purposes. Lets take a sneak preview to this feature.
|
|
Posted On : September 5, 2004
|
More...
|
Author : Vinod Kumar
|
|
|
|
45. Introduction to SQL Server 2005 - DTS
|
|
DTS has come a long way in SQL Server 2005. The architecture of DTS has changed and transformed to the level where all users have to rethink the way DTS works from its previous versions. Nevertheless a more fresh perspective to understand this new version. I would surely consider that DTS has become more mature. And in this article we will take a quick tour to look at how a simple DTS Package can be created and used. Note: Updates made.
|
|
Posted On : September 2, 2004
|
More...
|
Author : Vinod Kumar
|
| |