Massachusetts recently passed a sweeping new data security law that will have a profound impact on the way the United States, and perhaps the rest of the world, manages and develops data-centric applications. Oddly, most people in the business don’t seem to know about it.
Google “Massachusetts data security law, 201 CMR 17.00” and you’ll find plenty of facts about the new law. I also encourage you to read InformationWeek’s "States' Rights Come to Security Forefront: Massachusetts' new data protection law reaches beyond its borders. Are you ready?" It’s one of the best summaries I’ve seen. But even it falls short of helping you understand the profound impact of this law.
Here are the basics of the new law. If you have personally identifiable information (PII) about a Massachusetts resident, such as a first and last name, then you have to encrypt that data on the wire and as it’s persisted. Sending PII over HTTP instead of HTTPS? That’s a big no no. Storing the name of a customer in SQL Server without the data being encrypted? No way, Jose. You’ll get a fine of $5,000 per breach or lost record. If you have a database that contains 1,000 names of Massachusetts residents and lose it without the data being encrypted that’s $5,000,000. Yikes.
Wednesday, April 28, 2010
A New Law Could Change the Way You Build Database Applications
Why SQL Server 2008 R2 Matters to Small Business
If you're like many small business owners, the news that Microsoft is weeks away from the release of a new version of SQL Server 2008 could seem utterly irrelevant to your interests. But it isn't. Microsoft has added important new features in this version that will help you delve more deeply into your business data, extract the most useful nuggets of information, and present them in a compelling way.
Best of all: If you're savvy enough to build a pivot table in Excel, you'll be able to use these features without the help of professional IT types.
How to Read Excel Cells from SQL Server Integration Services
We can get data in all sorts of formats in the real world. In this new article from me, learn how to use scripting to import data from individual Excel cells.
Read the article at http://www.sqlservercentral.com/links/66237/151372
Thursday, April 22, 2010
SQL Server 2008 R2 has RTMed today
You can get more information, resources and download a free trial here: http://www.sqlserverlaunch.com. MSDN and TechNet subscribers can expect to download it soon.
Some more information here: Data Platform Insider blog
Sunday, April 11, 2010
How to change server name when replication is enabled
Changing a server name is not difficult but Microsoft does not recommen to change the server name when the replication is configured. These are the steps you need to follow.
http://www.sql-server-performance.com/faq/change_server_name_replication_enabled_p1.aspx
Could not find stored procedure 'dbo.sp_MSins_dboTest'
This is a frequent error you get in transactional replication. See how to resolve this at http://www.sql-server-performance.com/faq/could_not_find_dbssp_msins_dbotest_p1.aspx
Queries which include DMFs return a syntax error
When you execute queries which include DMFs the queries return a syntax error. See the full FAQ at http://www.sql-server-performance.com/faq/dmf_syntax_error_p1.aspx
BeyondRelational.Com
I am invited to write blog posts targeting noive DBAs / database developers at www.beyondrelational.com . Since I can’t duplicate those post here I will include the shortcut here to the beyondrelational.com .Those will be tagged as BeyondRelational.com
Thursday, April 8, 2010
Microsoft SQL Server 2008: What's New with R2?
Are you ready for Microsoft SQL Server 2008 R2? This white paper highlights some of the major features included in SQL Server 2008 R2 due to be released in the first half of 2010.
Wednesday, April 7, 2010
Windows Authentication Users Domain Will not Change when Server Name Changed
in practice, you need to change the server name due to many reasons. There are few challenges you need to face as Database administrator. This is one of them.
After changing the server name, you need to run following stored procedures.
sp_dropserver SERVER2
GO
sp_addserver SERVER3, local
GO
SERVER2 being the old server name and SERVER3 is the new server name.
After this if you can login to the server with SERVER3\Administrator if you have configured SERVER2\Administrator user before. However, if you closely look at there is no user called SERVER3\Administrator instead user is SERVER2\Administrator.
When server name is changed, I believe these windows authentication users also changed. Now you need to run following script to change user names accordingly.
/****** Object: Login [SERVER2\Administrator] Script Date: 04/06/2010 14:51:51 ******/
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SERVER2\Administrator')
DROP LOGIN [SERVER2\Administrator]
GO
/****** Object: Login [SERVER3\Administrator] Script Date: 04/06/2010 14:51:51 ******/
CREATE LOGIN [SERVER3\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
Saturday, April 3, 2010
Changing Data Type for Derived Columns?
If you want to add a column to the existing data flow in SSIS, in normal circumstances you are using Derived Column Transformation to accomplish this task.
If you drag and drop the Derived Column Data Flow Transformation and following configuration dialog box will appear.
Unlike in SQL Server 2005, you don’t have an option of selecting the data type you need. It will select default data type for expression you type.
For example, for text data type it will be Unicode string [DT_WSTR], for positive integer’s four-byte signed integer [DT_I4], for float numeric [DT_NUMERIC].
There is a workaround for this. Go to the advanced Editor; navigate to the option shown in following image.
You can select any data type from the available combo box here and if you go back again to the previous configuration, you will see the newly selected data type there as well.
However, I feel this is a bug to be fixed.