Group items tagged

"Database development is many aspects behind in comparison to regular application development. If you look how database development is integrated with regular application development or development processes it becomes even more obvious that there is room for improvement in this area. When asked, you will find that many database developers are spending their valuable time writing, maintaining, and testing database deployment scripts. The practices may vary, some write them by hand, some use schema comparison tools to generate the script and modify that, and some went all the way to create their own tools to generate these kinds of deployment scripts."

"Just as it does with file system data, Veracity's decentralized database keeps the full history of the database. Every version of every record is retained in history, with log information about who made the change and when. Veracity also supports push and pull of "database changesets", including the ability to automatically merge (at the level of a record or of a field) and resolve constraint violations."

"The Issue, in which Dylan gets bit in the ass by a legacy database I'm building some message-oriented-middleware (MOM?) for a 3rd party vendor product. The database has evolved over many years and has all of the quirks that you might expect of such a system: primary keys that are implied but not made explicit with constraints, relationships that are implied but not explicit, columns that are not nullable in the business logic but are nullable in the tables, etc. I'm not knocking the vendor: I am keenly aware that keeping a multipurpose database absolutely clean of cruft is a tough challenge indeed. But it does make for some really tough integration points. My major challenge over the past week has been auditing. The vendor system makes very heavy use of sprocs and embeds the creation of the audit trail in those sprocs rather than using what, to my mind, is the more obvious approach of triggers."

"There can be a great difference in the performance of a particular routine in a test database, and in a fully loaded production system. When you hit performance problems in a database under load, and there is excessive locking and blocking, how can you determine exactly where the problems lie, in order to fix them? Read on..."

"In this blog post I'm going to write about the Entity Framework 4.3 Database migration and how to use it without using Code-First or Entity Framework as an OR-M at all."

"Every couple of weeks on one of the forums someone will ask a question about how to restore a backup from SQL 2008 to SQL 2005. The answer to the question is always, you can't restore a backup to a lower version of SQL Server. If you need to migrate backwards it is an entirely manual process. I learned this unfortunate truth a few years ago after upgrading a development server to SQL Server 2005 ahead of upgrading the production server for testing by the developers of our eCommerce application. At the time there was a new database for another web project in development that didn't exist in production, which was on SQL 2000 still, and when I had to move it to QA, also on SQL 2000 still, what was originally planned as a backup/restore turned into a "What do I do now?" moment."

"Like many developers, I happen to be a fan of using uniqueidentifier columns as primary keys on database tables (as opposed to the other common practice of using integers). While I don't do this all the time, in many cases I think it just makes sense, especially when the records in and of themselves don't share a "sequential" relationship to one another. I understand this is a rather controversial topic in some circles; with good arguments on both sides. Quite frankly, whether you personally agree with this practice or not is beside the point. For the purposes of this discussion, it's just important that you understand two things: one, that this done all the time; and two, that this scenario was not properly supported under Entity Framework V1."

" SQL Sentry Plan Explorer is a FREE tool that builds upon the graphical plan view in SQL Server Management Studio (SSMS) to make query plan analysis more efficient. It is a lightweight standalone app that contains many of the plan analysis features introduced in SQL Sentry v6. It does not require a collector service or database. "

"When ASP.NET receives a request for a page, it grabs a thread from a thread pool and assigns that request to the thread. A normal, or synchronous, page holds onto the thread for the duration of the request, preventing the thread from being used to process other requests. If a synchronous request becomes I/O bound-for example, if it calls out to a remote Web service or queries a remote database and waits for the call to come back-then the thread assigned to the request is stuck doing nothing until the call returns. That impedes scalability because the thread pool has a finite number of threads available. If all request-processing threads are blocked waiting for I/O operations to complete, additional requests get queued up waiting for threads to be free. At best, throughput decreases because requests wait longer to be processed. At worst, the queue fills up and ASP.NET fails subsequent requests with 503 "Server Unavailable" errors."

"Takeaway: SQL Server 2005 has several system tables and views that can provide a ton of insight into blocking problems that you may be facing in your database environment. Through these procedures, you can find blocking chains. Learn more about blocking processes."

"Many experienced database professionals have acquired a somewhat jaded view of parallel query execution. Sometimes, this is a consequence of bad experiences with older versions of SQL Server. Just as frequently, however, this view is the result of misconceptions, or an otherwise incomplete mastery of the techniques required to effectively design and tune queries for parallel execution."

"The Crimson Consulting Group has just published a white paper worth reading: Comparing the Business Value of Microsoft SQL Server 2008 R2 and Oracle Database 11g, Perceptions vs. reality in the RDBMS marketplace (April, 2011). The traditional user-friendliness of a mid-tier product has matured in terms of scalability. Today, you will find SQL Server 2008R2 installations running on 256 core machines -- scalability is no longer an issue. Unfortunately, the traditional scalability product has not grown significantly friendlier to developers and users; this leaves SQL Server 2008R2 the clear value winner for both lowest development expense and total cost of ownership for operations."

"SQL Server 2008 introduces two tracking features that enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Before these features were available, custom tracking mechanisms had to be implemented in applications. These custom mechanisms often required schema changes to the tracked table or the use of triggers. Neither change data capture nor change tracking requires any schema changes at the source or the use of triggers."

"JustMock Free Edition is a developer productivity tool designed to make it easy to create mock objects. JustMock Free Edition cuts your development time and helps you create better unit tests without requiring you to change your code. It allows you to perform fast and controlled tests that are independent of external dependencies like databases, web services, or proprietary code. "

" Reflection, like it or not, it is inevitable that eventually you will have to write some code that involves looking at the members of a type at runtime (rather than at compile time). Maybe you are trying to write utility validation / serialization / ORM code, or maybe the interesting property / method is specified at runtime in a configuration file or from the database. Whatever the cause, it is likely that at some point you've written code involving GetType() - something like:"

" Django may be built for the Web, but CouchDB is built of the Web. I've never seen software that so completely embraces the philosophies behind HTTP. CouchDB makes Django look old-school in the same way that Django makes ASP look outdated. -Jacob Kaplan-Moss, Django developer CouchDB's design borrows heavily from web architecture and the concepts of resources, methods, and representations. It augments this with powerful ways to query, map, combine, and filter your data. Add fault tolerance, extreme scalability, and incremental replication, and CouchDB defines a sweet spot for document databases. "

"DBAs and developers alike find it hard to effectively develop, deploy and manage a data-tier application throughout its lifecycle. SQL Server developers today want to create a data-tier application they can test, declaratively deploy across multiple instances while the system deals with version upgrades. SQL Server DBAs on the other hand want to know deployment requirements a priori and they too want the system to perform some of the mechanical upgrade operations instead of managing data-tier applications via a bunch of scripts. In this video we will dive into Microsoft's investments in the SQL Server data-tier application space and demonstrate the concepts and tools that simplify development and deployment of data-tier applications using Visual Studio 2010 and SQL Server Management Studio in SQL Server 2008 R2."

" Are you tired of fighting with overly complex ORMs such as Entity Framework and NHibernate? Are you tired of poorly generated SQL or having to change your business objects to work with your ORM? Do you miss the power and performance of ADO.NET and SQL, but not the manual tedious work? If so FluentData might be the framework for you."

"I actually started out writing an article on how to pass 1, 2, and 3 dimensional "arrays" as parameters in stored procedures. Suddenly it dawned on me that a lot of people still have no clue what a "numbers" or "Tally" table is, never mind how it actually works."