One of the most common questions that I have been getting recently has been "what source control provider do you use and why?". This article is the first in a series of two articles about source control. This article takes a quick look at criteria that I believe is important to consider when it comes to evaulating source control systems for use. The next article in the series will be a review of the source control system that I use, in relation to the evaulation criteria that I list in this article.
Read more...
Posted by Mitchel on Wednesday, April 08, 2009
It seems that every client project I have started in recent months has involved a new form of complexity in SQL Server scripts. The most recent task that I had to tackle was with generating a comma separated list based on entries in a table. Now this was something that I needed for each and every row of a result set, so I didn't want to do the actual list creation in .NET. So, reaching back to some content and examples that I built for my "Creating Dynamic SQL Pivots" article which will be published in an upcoming edition of SQL Server I found the following helpful snippet of code.
Read more...
Posted by Mitchel on Friday, January 30, 2009
One of the biggest failure points I see with third-party DotNetNuke modules, including some of my first released modules were failures due to the lack of support for ObjectQualifier or DatabaseOwner values within the SqlDataProvider files. Although the process to add support for these two tokens is easy to add, it is very hard for those of us that like to test our database structures outside of DotNetNuke before we actually commit to building our modules. Thankfully, with a bit of research, I have found a way to build SQL Scripts for DotNetNuke using SQL Server Management Studio (SSMS) in a normal fashion and using a simple find/replace operation to bring everything into compliance with DotNetNuke integration. This post will explore the method that I use to accomplish this.
Read more...
Posted by Mitchel on Monday, January 26, 2009
One very common scenario in when working with User Interface database queries is the concept of a "Dynamic Search Query". A select statement that can have optional items in the where clause. There are a number of methods to implement this, however, they have varying levels of difficulty and, overall each have their own tradeoffs, in implementation, readability, performance, and accuracy. This article explores a few of the most common methods of doing this.
Read more...
Posted by Mitchel on Friday, January 09, 2009
One item that many people find unusual about my DotNetNuke installation guides is my insistance on NOT using the Database.mdf, dynamically attached database file for the creation of a DotNetNuke database. In my tutorials I have quickly dismissed the topic by noting that "issues" come up when trying to use a dynamically attached database and I leave it at this. Well after seeing more questions on this topic recently in the DotNetNuke.com forums I wanted to take a moment to put my $0.02 out there as to WHY I don't like it. I will do this by discussing quickly what the differences are in the two approaches, then I'll discuss the "downfalls" of each.
Differences in Approach
Everyone is most likely familiar with the common process for creating a SQL Server databse, during the creation process two files are added the .mdf which stores the actual data and the .ldf which stoes the log. When the database is created the files are typically placed inside the "Program Files" folder, storing them with the rest of the SQL Server files, although this location can be changed by the Server Administrator.
The dynamic attachment method uses a pre-defined database.mdf file that is located in the App_Data folder of the DotNetNuke installation. When the system connects to the database server this file is attached to the server and operates like a database on the server would normally, however, all data is stored inside the DNN location.
Why I Think Its Bad
One of the most common arguments supporting Dynamically attached databases is that they "save time". Well That might be true for the first connection, however, in my experience the time spent troubleshooting later makes it a much longer process. The first thing to remember is that the database is attached and it has a name associated with it, DotNetNuke when working with DNN, if you go to install a second DotNetNuke installation on the same server with the dynamically attached file an error will appear noting that the database already exists. For beginners this can be a very upsetting experience.
However, the most problematic downfall of dynamically attached databases in my experience is that you must "Attach" the database before you can manipulate it in SQL Server Management Studio or SSMS Express. This is a very common stumbling block as individuals will know that the database exists, however, SSMS will not show the database at all, until it has ben successfully attached.
Overall
I feel that overall there are no compelling reasons that would direct a person to use a dynamically attached database rather than a standard SQL Server database especially when it comes to databases that need to be administered on a regular basis. If working in a test environment it might be helpful to have a dynamically attached database, but that is a limited use case, and for the most part DotNetNuke databases at one point in their life will need to be administered, backed up, restored, or moved. For these actions a standard database is a good way to go.
Please share any comments/feedback below.
Posted by Mitchel on Monday, November 24, 2008
Finally getting the opportunity to get back to SQL Server 2005 development, and coming to the conclusion that NOT all of my projects have to support SQL Server 2000 I started looking all of those "fun" new items that we all really wanted. This article is going to go through a scenario that demonstrates how to use Common Table Expressions, Rank() and Partition to get results for a very common data scenario. We will present this information with an introduction to the problem, scripts to setup the problem in a test environment, and lastly the implementation, with a review/summary at the end.
Read more...
Posted by Mitchel on Saturday, November 01, 2008
Recently when working with a client I was asked how in a SQL Server database environment with multiple databases how we can prevent users from seeing all other databases on the server. Permissions are configured to restrict who can actually query the database, but in the end, individuals still can see the names of the other databases on the server, getting potentially an idea of who else you are hosting, or information that you just don't want to share. I have found many blog articles that talk about how to get around this, but all of them were a bit choppy, so I thought I would take a moment to walk through a demo of the process. This article walks through the entire scenario.
Read more...
Posted by Mitchel on Wednesday, October 15, 2008
Recently I was tasked with the creation of a large database system that consisted of a database table with 5 date colums, and a varchar primary key. This table was to hold upwards of 3.5 million records, and I needed to know exactly how much disk space was going to be needed to store not only the database, but also the index required to facilitate the search requirements. After looking for a number of different ways to do this, and many free third party tools I decided that the most simple way to do this with the tools that I had available was to generate my own method to populate a test database.
Read more...
Posted by Mitchel on Friday, September 12, 2008
Recently I have been working on creating a dynamic reporting engine for use within the DotNetNuke system, a method to allow for the execution of a stored procedure and the proper display of the results. This includes creating a list of parameters, including dynamically loaded lookup systems and more. Well the most complex item that I have had to condition for was how to deal with filter options that require the use of the SQL IN clause, there are many ways of doing this, but I have found a way that appears to work perfect for my needs, this will be explained in this posting.
Read more...
Posted by Mitchel on Friday, August 08, 2008
In one of my previous blog entries I provided a helpful script that would allow you to find the space used for all SQL Server tables in a single database. It was a very simple method using the SP_SPACEDUSED stored procedure that is included in SQL Server. In that article I touched a bit on the general usage fo the method.
SP_SPACEUSED TableName
Where TableName was the specific table you are researching. Well there is a secondary option with a second parameter that is a very helpful item as well. I'll provide a quick scenario that sets up my specific example. I was working with a table that has about 80 columns, and somewhere in the neighborhood of 1.8 million records. Trying to condense the size of the database, we removed 20 columns that were no longer necessary. Prior to dropping the table we ran SP_SPACEUSED to get the table size, which provided us the following.
Reserved: 5,474,304 KB Data: 3,451,368 KB Index_Size: 196,704 KB
Overall the table itself was in the nighborhood of 3Gb, we executed a statement that dropped 20 columns from the table, however, running SP_SPACEUSED for the table only provided us the same results. You can pass SP_SPACEUSED a second parameter which expects a value of 'true' or 'false' with a default of false. If it is set to true, it will update usage information before returning the results. We then ran the following statement
SP_SPACEUSED TableName 'true'
Now we got the accurate information that showed our reduction in size.
Reserved: 1,826,320 KB Data: 1,727,624 KB Index_Size: 98,344 KB
I hope that this might help those of you optimizing table sizes and noticing incorrect numbers!
Posted by Mitchel on Friday, July 18, 2008
Previous Page
1 of 3
Next Page