Different ways of importing Data into SQL Server for Data Migration

There are many ways of importing data into SQL Server, depending upon the source from where data is to be imported. When working with legacy systems or non-relational systems like SAP, CRM applications and Mainframe systems, the source is generally text files or feeds which are in textual format. Professionals having knowledge of SSIS can import data with more fine tuning.

All the different ways of importing data, with the caveats of each of the methods are listed on an article that I found on lessthanadot.com. I had the opportunity to place the issue of date conversion with Import Export wizard to the database professionals, and this post is one of the best places where this issue can get it's deserved attention. This article would be of most use specifically in a SSIS Data migration project, where you need to put all the options on the table for importing data from source to SQL Server using SSIS as a part of migration strategy.

It's a useful post and can come handy while thinking of options in the way one would want to import data during the technical design planning of any solution. The article also provides the statistics of the test results for various volumes of data with all the methodologies followed for importing the data. If the author would had specified the hardware configuration used and with a little bit change in proforma, it would had been a nice tiny whitepaper in itself. Still nice article worth reading along with comments.

SQL Server 2008 Import Export Wizard Limitation for Date Conversion

Import Export Wizard in SQL Server 2008 has nice improvements over what was available in SQL Server 2005, but still there's more that can and should be done to cater the requirements of the real world.

I feel that while designing of Import Export wizard, equal importance has not been given to each data types. For example, there are properties to adjust the precision and scale when numeric data types are selected. But when datetime data type is selected, there should be option to specify the style to use while making the conversion during the import process. This feature is absent and while making text file imports for example, the dates would be imported and converted to mm/dd/yyyy format i.e. US Standard (provided the installation language is US English). But if the source data contains dates in dd/mm/yyyy format i.e. British Standard, there is no means to specify within the wizard, what format or standard should be used for conversion. This can be a great shortcoming at times.

The workaround can be use another tool, or import the same as Varchar column, and convert is using the Convert system function before using the field. I know that it's not a great solution, but because of the limitation that exists on the date format conversion using this wizard, it becomes a SISO show (Shit In Shit Out). Still it's not that bad and the solution is workable. I hope that Microsoft realizes this shortcoming and adds this feature in a service pack, instead of the next release.

BTW, if anyone is aware of a better solution or would like to correct me on this, I would be glad to learn the same. I might not be completely updated in the usage of SQL Server Tools and it can be my ignorance, but to the best of my knowledge, this shortcoming exists in SQL Server 2008 Import Export Wizard.

Free Ebooks , Study Guide and Interview Questions on SQL Server Security, Performance and Administration

Another Ebook bundle from Red-Gate.com containing some of the best technical material of SQL Server 2005 and SQL Server 2008 for free. It also includes one of the must have books for those preparing for a SQL Server developer profile interview. Mind it, it doesn't contain an major SSIS, SSAS or SSRS interview questions, except a few questions on SSIS, SSAS, and SSRS questions which may be less than 10 to 15 collectively in number out of the total 100 interview questions.

Ebook on SQL Server Execution Plans (Dissecting SQL Server Execution Plans.pdf)
Takeaway: In depth technical explanation on SQL Server Query plan execution

Ebook on SQL Server XML Schema Collections (The Art of XSD.pdf)
Takeaway: Technical content to master your skills on usage on XML and XSD with SQL Server

Ebook on SQL Server Interview Questions (SSC Stumper Vol 5.pdf)

Takeaway: Interview questions, answers and links to reference material put together by Red Gate and SQLServerCentral.com. It's one of the best SQL Server Interview Material, that I have came across on the web which is available in such an informative and structured manner for free.

Ebook on SQL Server Security and Encryption (Protecting SQL Server Data.pdf)

Takeaway: Best technical material available on the web for free on SQL Server Security and Encryption.

Download Courtsey: www.red-gate.com

Download Free SQL Server 2005 Ebooks , Download Free SQL Server 2008 Ebooks

RedGate has a very unique way of promoting it's product range of SQL Server tools and utilities, and I think that it's working quite well too. RedGate is giving out a free short ebook with a few of its tools, and also makes a few ebook bundles available for free on SQL Server.

This marketing strategy is keeping this company closer to the SQL Server community of professionals and also it shows how well this company has reach and insight into the product. Though I have not used many of SQL Server tools, as they are more concentrated on a DBA or Developer level of profile, and I work more onto the Business Intelligence side of SQL Server, but I am confident that they are one of the healthy market competitors.

Below is one of the EBook Bundle available for free from RedGate.

Free Ebook on DBA Best Practices (DBA_Best_Practices_ebook.pdf)

Takeaway: A kind of cramsheet of best practices for a SQL Server DBA

Free Ebook on SQL Server 2008 New Features (Brads Sure Guide to SQL Server 2008.pdf)

Takeaway: Higher level introduction to almost all the new features of SQL Server 2008 with examples

Free Ebook on Mastering SQL Server 2005 or SQL Server 2008 - Profiler Tool (Mastering Profiler eBook.pdf)

Takeaway: In depth technical content on the Profiler Tool

Free Ebook on How to be an Exceptional DBA by Brad McGehee (Exceptional DBA 2ndEd.pdf)

Takeaway: How to grow your image and career as a DBA, with complete focus on softskills

Free Ebook on SQL Server Tools and How to do with SQL Server (SQL_Server_Tacklebox_Ebook.pdf)

Takeaway: A SQL Server cookbook that shows how to use different SQL Server features and tools for different purpose

Download Courtsey: www.red-gate.com

Hardware configuration for SSIS and SSAS on SQL Server 2005 and SQL Server 2008

If you are a blogosphere bee, you would definitely be knowing the new news of MS BI town. If no is the answer, keep reading ahead. If you are working on estimation of implementation of any ETL or Data warehousing solution, one is often confronted with the issues and questions about hardware estimation and sizing. As I had mentioned in my previous posts, Project Real implementation and the whitepaper published my microsoft on the project configuration where they loaded 1 TB in 30 mins, configuration can be nice-to-have reference points. But they are more inclined from a very high performance point and not from the aim of a typical production system.

HP has come out with a new set of whitepapers and a tool which they claim can help in making recommendations for the hardware configuration required for Data warehousing and ETL. Based on the features that the tool claims to provide, it seems to be nice tool and can really provide informed recommendations about the hardware configuration, and the greatest point is that the tool comes from the experts of hardware. On the product page of this tool, they claim that the tool would model configurations for:

1. ETL (Extract, Transformation, and Load) layer using SQL Server Integration Services

2. OLAP cube build using SQL Server Analysis Services

3. Queries accessing data used in a data warehousing ROLAP (Relational Online Analytical Processing) environment

4. Queries executed against OLAP cubes using SQL Server Analysis Services

Following downloads are available from the HP site:

HP Business Intelligence Sizer for Microsoft SQL Server 2005/2008

http://h71019.www7.hp.com/ActiveAnswers/us/en/sizers/microsoft-sql-bi.html

HP Whitepapers on SQL Server 2008 Data Warehousing / Business Intelligence

http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-5263ENW.pdf

http://h20195.www2.hp.com/V2/getdocument.aspx?docname=4AA2-8173ENW.pdf

http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-7162ENW.pdf

Reference: Benjami Wright Jones Blog

How to store files in SQL Server 2008 using ASP.NET

Though I have left .NET behind in my career, there's still a level of affinity that I have towards it. I have been evident of situations in my career, whenever there has been a requirement of storing files at the database side, developers on either side i.e. .NET and SQL Server have been finding the easiest way to implement the same. The solution generally is to just store file path and location into database and storing files on a file server. This opens up many security holes and is also not a good practice. Another option has been to store files into a varbinary column which can make the database grow too huge.

With the induction of FILESTREAM data type in SQL Server 2008, things can be changed dramatically with the same being transparent to developers. Benefits of FILESTREAM data-type are pretty obvious, and those who are keen on discovering the way to implement the same in ASP.NET can read this article by Jacob Sebastian.

PCI Audit , C2 Compliance and Common Criteria Compliance in SQL Server 2008

Auditing and compliance like Sarbanes Oxley and others are becoming an regular requirement in projects especially in financial sector. SQL Server 2008 comes with some major security audit compliance standards, that covers major ground of compliance.

1) C2 Audit compliance: It's a US standard of compliance, and is quite strict in terms of its requirements. Everything that is executed on SQL Server is audited and written down to a trace file. And the not so pleasant part is, for whatsoever reason if SQL Server is not able to write to a trace, SQL Server service would be stopped. Once this auditing standard is enabled, the trace recording gets triggered and the file is saved in default directory. It can be enabled just by using sp_configure stored procedure.

2) Common Criteria compliance: This is an European standard of compliance and is considered to be a superset of C2 audit compliance. It is more flexible or I should use the word more free form in terms of the requirements it lays down for its compliance. A very nice article on this compliance can be found on SSQA.net.

3) PCI compliance: PCI is carried out on projects in almost any CMM level 5 organisation that practices Quality Assurance on projects genuinely. SQL Server has support for this too. A nice webcast and whitepaper on the same are available that guides how and what of PCI compliance with SQL Server 2008.

Those who need more information on compliance can download the SQL Server 2008 Compliance Guide for detailed information.