I just deployed the NOTS project. the NOTS project's old database table is a un-formalized flat table.

I extract it into 4 tables schema in new sqlserver 2005 database.

The problem some other application still use the old table, and create report base on that.

So every updating in new database must synchronized to old table.

Regular dts and simpler SSIS is not usable.

I build a system do completed the job:

1.    A trigger in the new db table to catch “insert,update,delete” and insert to updateLog table.

2.    Create s SSIS pagckage to do the synchronize task.

3.    Schedule a job in Sqlserver 2005 to run every 5 mins.

Here is the script:

1.  The trigger, updateTrigger.sql (1.16 KB)

Notice the “SET NOCOUNT ON” is very important, otherwise it will cause nHibernate update error message:

SQL insert, update or delete failed (expected affected row count: 1, actual affected row count: 2). Possible causes: the row was modified or deleted by another user, or a trigger is reporting misleading row count.

2.    The script in SSIS package

P_ Package_SynNOTS.dtsConfig (.86 KB)

Package_SynNOTS.dtsx (27.6 KB)

 ScriptMain.vb (27.03 KB)

3.    The deployment doc: NotsDB deployment Guide.doc (276 KB)





Categories: SQL Server 2005


Categories: SQL Server 2005


Performance Limitations in SQL Server Express

The SQL engine of SQL Server Express supports 1 CPU, 1 GB RAM and a 4 GB database size. This distinction gives SQL Server Express well defined cut-off points to differentiate it from other SQL Server 2005 editions. Unlike MSDE, SQL Server Express eliminates the confusion created by the workload governor.

1 CPU: SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. This limitation prevents the use of parallel query execution in SQL Server Express.

1 GB RAM: The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, etc. is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1 GB in total, but it will never use more than 1GB for the buffer pool. This limitation prevents the use of Address Windowing Extensions (AWE) with SQL Server Express.

4 GB Database Size: The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server. There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and DTC is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Applications planning to use SQL Server Express are recommended to keep these changes in mind when designing their applications.

Limitation in Enterprise Features

The following SQL Server 2005 enterprise level features are not available in SQL Server 2005 Express Edition:

  • Analysis Services (both OLAP and Data Mining)
  • Integration Services (DTS successor)
  • Notification Services
  • Report Builder (although Reporting Services is included)
  • SQL Agent (See Teratrax Job Scheduler)
  • Database Tuning Advisor
  • Full-text search
  • Log shipping

Enterprise Availability Limitations

Unlike other editions of SQL Server 2005, the Express edition does not support Fail-over Clustering or Database Mirroring.

Database Mirroring: Database Mirroring extends log shipping capabilities and enhances availability of SQL Server systems by providing automatic fail-over to a standby server.

Fail-over Clustering: Fail-over clustering is the ultimate fail-over mechanism SQL Server can provide. A SQL Server node in a cluster of nodes sharing one disk array can fail-over to another node without affecting the availability of the server cluster. The disk array where databases reside is central to the SQL Server cluster.

Networking Support in SQL Server 2005 Express

Only the shared memory on the local machine is accessible by default for SQL Server Express, although the user can explicitly turn on other supported protocols such as TCP/IP and Named Pipes. VIA and HTTP protocols are not supported in SQL Server Express. With only shared memory available by default, connections from a remote machine to SQL Server Express will fail unless the networking is turned on. To turn networking on, Use SQL Computer Manager to enable relevant protocols and start SQL Browser.

SQL Browser is a new service in SQL Server 2005 that is used to identify the ports that named instances listen on. Since shared memory does not use it, this service is turned off in SQL Server Express by default. This means that the user will have to start this service so that network access can work.

Note One interesting fact is that SQL Browser listens on UDP port 1434. However, pre-SP3 versions of SQL Server 2000 holding port UDP 1434 may result in failure of SQL Browser name resolution, since they may refuse to give up the port. The workaround is to upgrade all SQL Server 2000/MSDE instances on the machine to SP3 or higher.

Categories: SQL Server 2005

In Sqlserver 2005, database can very easy to attached with project and distribute to other,

For a website use Sqlserver Express, your database file may be in you app_data folder, in you web.config, it defined like this:

<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

But if you use a remote database server, you have to attach the database file to the databaser server.

First copy the mdf file and ldf file to database server, for production database, mdf and ldf file used to in diffenent disk.

Then run below T-Sql scripts to attach database.

USE master;
ON (FILENAME = 'C:\Projects\MyProject\App_Data\ASPNETDB.MDF'),
(FILENAME = 'C:\Projects\MyProject\App_Data\aspnetdb_log.ldf')


In SQL Server Management Studio, a newly attached database is not immediately visible in Object Explorer. To view the database, in Object Explorer, click View, and then Refresh. When the Databases node is expanded in Object Explorer, the newly attached database now appears in the list of databases.

For Detactch a Database:
Detach the AdventureWorks database by executing the following Transact-SQL statements:


USE master;
EXEC sp_detach_db @dbname = N'AdventureWorks';

Categories: SQL Server 2005