MSBI (SSIS/SSRS/SSAS) Online Training

Tuesday, August 18, 2009

SQL SERVER – How to Find List of Primary Keys and Foreign Keys in a Database

Good Article from Pinal.

http://blog.sqlauthority.com/2009/07/17/sql-server-two-methods-to-retrieve-list-of-primary-keys-and-foreign-keys-of-database/

SQL SERVER – MSBI – Design Process Decision Flow

MicrosoftMicrosoft Business Intelligence offers a complete suite of programs that supports all facets of decision-making. Through tight integration with the Microsoft SQL Server 2005 platform, Microsoft provides comprehensive business intelligence (BI) capabilities that deliver the right information, at the right time, and in the right format. It has emerged in the Leaders Quadrant in
MS BI expertise which include MS BI Program Implementation Strategy, MS Reporting/OLAP implementation Roadmap, SSIS Implementation Roadmap, SQL Server 2005 Upgrade Roadmap, SSIS Migration Roadmap and MS Scorecard.
Leveraging the entire MS BI technology stack,.
We have expertise in MS BI products such as MS SQL Server, MS SQL Server Analysis Services, MS SQL Server Reporting Services, MS SQL Server Integration Services, Microsoft Office Business Scorecard Manager, Microsoft PerformancePoint Server and MS SharePoint Portal.
MS BI Services include:
· BI Readiness Assessment, Tool Evaluation (ETL & OLAP) and Tool Standardization.
· Design and Implementation of ETL strategy using MS SQL Server Integration Services with extensive custom programmability on Microsoft SQL Server Business Intelligence Development Studio and SQL Server Management Studio.
· Design and development of reports using MS SQL Server Reporting Services (Enterprise, ad-hoc, web-based and embedded reports) and reporting on Microsoft SharePoint Portal.
· Design and development of analytical solutions and design of multi-dimensional cubes for OLAP analysis applying dimensional modelling approach using MS SQL Server Analysis Services
· Product Upgradation for SQL Server with detailed platform change analysis, enterprise security framework and step-wise comprehensive approach
· Migration and consolidation of data from legacy VSAM, ERP and web portal with SQL Server encompassing ETL activities with MS SQL Server Integration Services and progress reporting with MS SQL Server Reporting Services with tight integration of .NET and SQL Server stored procedures.
· Web based reporting using advance visualization, dashboard and analytics through Microsoft PerformancePoint Server.



http://blog.sqlauthority.com/2009/07/28/sql-server-2008-design-process-decision-flow/

SQL Server 2005 – Use Of Import/Export wizard

As per the requirement we can easily transfer the data from one table to another table in same server as well in other server by using the import/export wizard.

Schema and as well as data we can transfer at a time using the SQL Server 2008 option.

See for more Details:

http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

SQL Server 2005 – How Much Space Does My Database Use?

Hi it’s very important to identify the following things as sql server developer. Like-
how large your database is, how full the files are, which tables and indexes use the most space, which tables might be dormant, etc…
Here is the link for excellent script from Glen Berry.

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!1619.entry

SQL Server 2005 – Job History

Hi
Sql Server jobs are writing log info into the system tables.
If we get struck at any Job or related steps,by using the Sys.job history we can easily identify the problem is.It will give the description at high level.(success,failure,stopped all the info)

By using the following query we can get complete job info from sys tables.

SELECT a.name
,count(a.name ) as CNT
,b.[step_id]
,b.[step_name]
,b.[message]
,b.[run_date]
,b.[run_status]
,b.run_time
,b.run_duration
FROM [sysjobhistory] b JOIN sysjobs a
ON a.job_id = b.job_id

WHERE b.[run_date] ='20090101'
and b.[run_date] <='20090818'



See the below link.
http://sqlservernation.com/blogs/tipweek/archive/2009/02/25/viewing-sql-server-jobs-and-history.aspx

SQL Server 2005 – Use “FileStream”

We are getting problem while we are inserting the data into database for especially datatype of varbinary and image .Instead of using those datatype we have one more better option like - Filestream storage type. As you are probably aware, varbinary and image are very difficult data types to handle and it is a pain to manage them. Filestream storage overcomes all the limitations and has emerged as a true winner in terms of storage.
With FILESTREAM, the SQL Server team not only added a feature to handle unstructured data, but also made sure that it smoothly integrates with many of the existing features of SQL Server.
· FILESTREAM feature is available with all versions of SQL Server 2008, including SQL Server Express.
· SQL Server Express database has a 4 GB limitation; however this limitation does not apply to the FILESTREAM data stored in a SQL Server Express database.
· FILESTREAM Columns can be replicated.
· FILESTREAM enabled databases can be used with LOG Shipping
· FILESTREAM columns can be used in Full Text Indexes
· FILESTREAM works with all recovery models
· FILESTREAM File Groups can be placed on compressed disk volumes
· The maximum size of the file that can be stored into the FILESTREAM data storage is limited by the size of the disk volume only.

I have been added some more useful links here.

http://blog.sqlauthority.com/2009/07/13/sql-server-blob-pointer-to-image-image-in-database-filestream-storage/
http://www.simple-talk.com/sql/sql-training/an-introduction-to-sql-server-filestream/