I Need More Memory for Runing ETL Testing

I have been working on a data warehouse project for almost five months now.  We didn’t use any existing ETL process tool on the market, instead we develop our own.

After I studied the source code of Rhino ETL, I believe that it is possible to write our own ETL process. I didn’t use Rhino ETL, instead, my team develop our own framework that suit our need. The key of what I learn about Rhino ETL is that I can yield result from a database connection. This allow me to save memory usage. Instead of loading a list of data, I yield only the data I need to be process. Of course, I still need to load reference data during the transformation process.

Anyways, our application has over 150 tables, and the data warehouse has about 100 tables so far. There will probably be more data warehouse tables need to be added in the future.  We are at the final phrase of the first data warehouse project release. My team has been doing performance and manual testing while I am developing a integration test framework. We have unit test that test the data transformation and other parts of the ETL process, but we don’t have an integration which I believe it is necessary if we want to have a robust product.

Long story short, I finally finish coding the basic integration testing framework. I am using NDBUnit part my my framework.

Here is the basic attributes of ETL Process:
1. It uses ParallelLinq to process the transformation
2. It chunk the process by date range to reduce memory usage
3. It clean up old data from the live system once ETL is completed

Here is the basic attributes of the Integration Test
1. It loads the entire live database to a Dataset before the ETL process
2. It loads the entire live database to a Dataset after the ETL process
3. It loads the entire data warehouse database to a Dataset after ETL process

The data can be either an XML file, or it can be an existing database.

Other than the fact that my Visual Studio Dataset design almost die because of the amount of table that I have, the testing was fine until I use an customer database for testing, and this is what happened:

Good thing I was running Windows 7 64 bit which allow my applications allocate more memory than my other developers’ 32 OS. However, the test in the end took 1 hour to run and fail to perform clean up due to SQL connection was disposed error.

The problem was that I was too optimistic about loading data into memory for comparing data. To resolve this issue, I now have to explicitly load and unload data when doing comparison.

In conclusion, memory and performance issues don’t just exist in your application, but even in your tests.

Why can’t MS make SQL2008 Installation Easier + Failed to register SQLDMO.DLL

I rarely write blog to complain about something. I would rather like to share useful information instead.  But this time, I got so frustrated installing SQL2008.

Originally, I have Windows 7 installed with SQL2008 Server. My company has an product that require a file call “SQLDMO.DLL” to be register. (i.e. regsrv32 sqldmo.dll). Unfortunately I kept getting an –2147024770 error.

After trying everything, I decided to uninstall SQL2008 Server and hopefully by reinstalling it would let me register sqldmo.dll.

Btw, I turned off my firewall and UAC just in attempt to register sqldmo.dll…. but nothing works.

Originally, I installed SQL2008 from a MSDN CD follow by an SP1 installation. My colleague suggested me to install SQL2008Express install from one of our company shared directory. So this is what had happened.

1. Install SQL2008Express from company network
2. Complain about Windows 7 compatibility issue, click continue
3. Go online, cannot find SQL2008Express SP1 upgrade, instead, just the full SQL2008Express SP1 installation
4. Uninstall install existing SQL Server just to be safe.
5. Download and install SQL2008Express from here
6. Installation works, but can’t find SQL Management Studio
7. Download and install SQL Management Studio from here
8. The setup screen didn’t say anything bout Management Studio, install, it is the same screen as regular SQL Express installation wizard.
9. Try install it, failed, because I have SQL installed
10. Find another download link: http://www.microsoft.com/express/sql/download/
11. Try to install “Runtime with Management Tools”
12. Cannot install since I have SQL installed
13. Try install “Management Tools”
14. Complain about Windows 7 compatibility issue, ignore it
15. Finally, I got SQL Management Studio

I downloaded total of over 2GB of installation files, took me to whole morning to do it. Yet, I still cannot register sqldmo.dll.

Solution

Ultimately… it turns out that all I needed to do is install SQLDMO from SQL Server 2005 Backward Compatibility components. Yikes, wasted 1/2 of my day.

SQL Server – Login failed for user: Error 18456

Every once in awhile I need to reinstall SQL Server. Every time after I reinstall SQL Server and try to run my project executable, I would run into a connection error which says I cannot login with my specific user name.

First – Ensure your Login user is Added

The first thing I would do is to ensure my restored database has the right user.  In fact, it has to be a user from the SQL Server security group, and not the one from the Database security group.

In the following example, the database apts_dev4.0 has a user name call “MentorStreetsDBUser”. Under the DICKYS2\SQLEXPRESS security, it also has “MentorStreetsDBUser”. However, these two users are not the same at all. The one in the database is created by my previous SQLServer. This logon will not work for the newly installed SQLServer.

For this part, I simply remove the MentorStreetsDBUser from the database, and add a new user by specifying MentorStreetsDBUser from the SQL Server. This way I should able to logon using this account name. Make sure you give this user the proper security rights.

But Wait… I still get a Login Fail message with this user!

Second – Ensure SQL Server Authentication Mode is On

This is the part where I usually forgot to do.

Right click on the SQL Server select Property. Click the Security section. You will see the following:

In the Server authentication, select both “SQL Server and Windows Authentication mode”

By default, SQL Server only allows Windows Authentication login, and not the SQL Server login. After restart SQLServer, everything will work again.

Conclusion

First, I would hope that SQL Server Management would at least so a warning letting me know that the imported database user would not able to log on.

Second, I think that MS SQL Server should default the Server authentication to both the “SQL Server and Windows Authentication Mode”.

In addition they should provide better error message. I personally don’t see a big security issue if you at least tell the user that "SQL Server Mode Login is not supported”.

Restore SQL Server Backup Database => Operating system error 5(Access is denied) using MS SQL Server Management Tool

Occasionally I have to backup a SQLServer database from one machine to other one. Many times I encounter this Access is denied error, and I always forgot why it happens. So, I decided to write the solution in this blog so that I can reference it back in the future.

Backing up the Database

1. You need to have Microsoft SQL Server Management Studio. By the way, SQL2008 SQL Server Management Studio comes with intelliSense, it makes SQL writing much easier.
2. Connect to your database, in Object Explorer windows, right click on the database->Tasks->Backup  
   
3. Add  back destination. The location can only be your database local directory
4. Select Back type to "Full", so that you can transport the database file to another database server.
5. Click OK to complete the operation

Restoring

1. Copy the backup file to your local machine that host your destination database server. SQLServer somehow limited restore location to only local machine.
2. Once copy over, right click the backup file->Properties->Security tab
3. Click Add, you should able to find a user name start with "SQLServerMSSQLUser$username$SQLEXPRESS", add this user and give your SQL user right to read and write.
4. Open your database in SQL Server Management Studio
5. In the Database folder, right click->Restore Database...  
     
6. In the "To database:" field, type a new database name
7. Select "From device:" option
8. Click the browse button on the right and add your back up file.
9. Click Option Tab
10. Make sure your "Restore As" directory has "SQLServerMSSQLUser$username$SQLEXPRESS" security right.
      
11. Double check your options, and select OK

In summary, the reason why you get an access denied error is because MS SQL Server Management Studio has its own user account when interacting with your system. As long as your SQLServerMSSQLUser$username$SQLEXPRESS has access to the read and write permission, you shouldn't encounter Access is denied error.

[Update Mar 3, 2009]

Thank you to an anonymous post, he/she reminded me that you may encounter access denied error if you pick an incorrect restore path.

MS SQL creates two files during restore, the .mdf and .ldf. Usually by default these files are restored to:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

However, sometime (for the reason I don’t know), the default restore path is not the same as above. You may not able to restore if the path doesn’t exist, but more importantly, it may not has the security right.

If you want to restore the database file to another directory, once again, make sure the restoring directory does not have any existing files and has "SQLServerMSSQLUser$username$SQLEXPRESS" user right. Otherwise SQL Management will not be able to write the files in your specified directory.