10 Technology Forecast for the year 2009
Posted by Ravi Khanal on Dec 31, 2008
There would be lots of ups and downs in the world of technology in year 2009. Ten things that might happen in the year 2009 would be:
1. Google’s advertizing model will start facing problems:
Google will have some problems with their advertizing model in the year 2009 and advertisers would be less interested in Google’s ads. Google’s ad should not be context based but rather be user based. Google should read the mind of the user and display the ad, exactly what he is looking for. There should be more localization in the Google’s ads. Now a day people are not so much interested in the Google’s ads, so either Google has to change its advertizing model or it will start facing problems.
2. Microsoft will inch ahead due to Google’s loss of reputation:
Year 2009 will be really bad for the Google. Google is acting as a spy and tracking all the data from cookies. Everybody will think twice before using Google. So the charm of Google is reducing day by day and it will slowly fall behind in the race against Microsoft.
3. Standards will evolve in the Clouds:
Till now everybody has defined cloud according to their way. But in the year 2009, some standards will evolve in the clouds and there will be SLAs .
Whats new with SQL Server 2008
Posted by Ravi Khanal on Dec 22, 2008
Improvement in Business Intelligence
- Enterprise Reporting Engine
There are two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.
- Analysis Services Time Series
This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.
In Data warehouses, spaces used to be always wasted due to the NULL values. Columns having lots of NULL value is actually allocating lots of space without storing any values. This problem has been taken care in SQL Server 2008 by the introduction of a sparse columns. This columns allows NULL values to be stored without taking any physical space on the disk.
T-SQL Improvements
Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.
How to Synchronize Logins and manage Orphaned Users
Posted by Ravi Khanal on Dec 19, 2008
Database movement between servers is one of the common task for the DBA. But when the database is moved from one server to another or from one instance to other, the logins do not get transferred. So as a DBA you have to manually transfer the logins to remove this problem.
So let’s discuss what is the cause behind this. SQL Server logins are stored in the master database and they are mapped against individual databases. The Login informations on SQL Server 2005 are visible through sys.server_principals system catalog view and the sys.logins view. The mapping information of the database user is stored in the sysusers system table. This table includes the name of the database user and the SID of the corresponding SQL Server Login. The SID is the security identifier and SID tracks the logins throughout the entire systems.
So when we move or copy our database from one server to another or from one instance to another then the windows login will have the same SID but the SQL logins may have the different SIDs. So when the SID of the new server instance and the older instance doesn’t matches we have a Orphaned User.
SSIS Package for merging data sources and applying transformations
Posted by Ravi Khanal on Dec 14, 2008
Scenario:
I have file in different heterogeneous sources like Text file and Excel File and I want to merge it into the SQL Server Table. But before loading the data into SQL Server table I have to change the First Name Column into Upper Case and also I need to give 20% raise in the salary. So I should have the proposed salary column added to the table.
Solution:
So First I need to have the data flow task for this.
Then in the data flow the package would be like this:
SQL Server 2008’s Installation Snapshot
Posted by Ravi Khanal on Dec 11, 2008
How to remove fragmentation in table?
Posted by Ravi Khanal on Dec 8, 2008
Hey, Our Application is down!
This is the common thing you hear while you are working as a DBA. The reason for this is not always evident. Perhaps the number of transactions issued has increased or maybe the volume of the data has increased. But sometimes this might not be the cause of the problem. The problem might be due to disorganization of the database. Database disorganization occurs when a database’s logical and physical storage allocation contains many scattered areas of storage that are too small, not physically contiguous, or too disorganized to be used.
Database disorganization or fragmentation can occur when modifying data with INSERT, UPDATE, or DELETE statements, which over time cause gaps in each page. If a query search is based on a table scan or partial table scan, then it will create overhead for the SQL Server process with additional page reads, leading to high CPU activity and unresponsiveness.
Sample SSIS Package
Posted by Ravi Khanal on Dec 6, 2008
Scenario:
In the inbound location, I have to create a directory which would be like “DealerSales and Current Date” and then download files from the remote location to that directory. Then I have to move the files from there to the working directory. Then I have to unzip the zip files and load the text files to the database and delete the text files from the working directory and move the zip from the working directory to the Archive folder. The directory’s date format should be in MMDDYYYY format.
Solution:
There could be a different approach to make the SSIS Package. One of the easier way to make the Package for this type of scenario could be like this:
- Let’s first have the File System Task which will create a directory like “DealerSales12052008″ inside download directory, if it doesn’t exist.
- Then let’s have the FTP Task which will download files from remote location to that folder.
- Then let’s have the File System Task which will create a directory like “DealerSales12052008″ inside the working directory, if it doesn’t exist.
- Then let’s have the For Each Loop Container and File System task which will move files from download directory to the working directory.
- Then let’s have the For Each Loop Container and Execute Process Task which will unzip the Zip files.
- Then let’s have the For Each Loop Container and Data Flow Task which will load all the text files into the SQL Server database.
- Then let’s have the File System Task which will create a directory like “DealerSales12052008″ inside the Archive Directory.
- Then let’s have the For Each Loop Container and File System Task which will move the zip files to the Archive Directory.
- Then let’s have the For Each Loop Container and File System Task which will delete the text files from the working directory.
- Then let’s have a File System tasks which will clean up our download directory and working directory.
So now let’s begin by making the skeleton of the package by dragging the task and which would look like this:
