Tag: sql
[SOLVED] Microsoft SQL Server 2008 x64 Error: 15401
by Steve Hernandez on Nov.17, 2009, under Uncategorized
I recently came across this error within an Server 2008 Active Directory Environment where I attempted to add a domain user account to Microsoft SQL Server 2008 x64.
As you may see on the internet, the problem is in fact related to SID, but in my situation it was not the SSID of the user, but of the actual server. The instance of Server 2008 x64 is running in VMWare Workstation 7 and was created by cloning another VM. This, of course, makes them identical, such that changing the computer name only results in what ‘appears’ to be disparite systems. In a workgroup this shouldn’t be an issue, but once Active Directory is introduced, it gets wacky. AD relies on SID’s, and thus, acts unpredictably when 2 machines / users / groups have the same SID. This is further exagerated when SQL Server is involved, since it creates and / or stores its own SID for the user.
Here are the steps I followed to solve this issue:
- Remove the server running SQL Server from the domain. Reboot.
- Verify the AD Computer Account has been deleted for the system (on a domain controller).
- Run newSID.exe (found here) and change the SID of the server running SQL Server to a new random SID. Reboot.
- Add server back to the domain and verify the account is created within AD.
Once I did all that, the user added with ease.
Good luck.
NAS / Software and Hardware RAID Performance Benchmarking
by Steve Hernandez on Feb.15, 2009, under Technology
I recently came across an application provided by Intel for the purpose of benchmarking NAS devices (Define: Network Attached Storage). I’ve been looking for a solution to benchmark the new RAID-5 array I created on my Windows Server 2008 server (I love the RAID’s redundancy, but the write speeds are quite low). I wanted to get a better idea of its performance, as Windows will report 250 – 150 MB READS and 20 – 50 MB WRITES, which is considerably poor considering the 4 drives are individually benchmarked at ~100 MB READS and 60 MB WRITES (They are Western Digital Caviar Blues / RE3 / RE16).
The software can be found here or from Intel here.
System Specifications:
Operating System: Microsoft Windows 2008 Enterprise Edition, SP1
CPU Type: Pentium 4 630, 3000 MHz w/ Hyperthreading
Motherboard Name: Dell Dimension 8400
Motherboard Chipset: Intel Alderwood i925XE
Motherboard Frontside Bus Speed: 800 MHz
System Memory (RAM): 4 GB, Dual Channel
System Memory Speed / Timings: DDR2-533 (266 MHz) / 4-4-4-12
Hard Drive Controller Interface: SATA I (1.5 Gbits / s) = 150 MB/s (maximum)
Hard Drives: Western Digital
- Drive #1 – WDC WD5002ABYS-01B1B (465 GB)
- Drive #2 – WDC WD5002ABYS-01B1B (465 GB)
- Drive #3 – WDC WD5000AAKS-00A7B (465 GB)
- Drive #4 – WDC WD5000AAKS-00A7B (465 GB)
- Drive #5 – WDC WD800BB-00JHC0 (74 GB) – SYSTEM
Raid Configuration: Software 4 disk RAID-5 (4 x 500GB, 1.36 TB Effective Storage Space)
System Memory Benchmark: Copy – 4790 MB/s, Read – 6266 MB/s, Write – 3341 MB/s, Latency – 92.7 ns
System Processor Benchmark: CPU ZLib – 10592 KB/s, CPU AES – 3055
Benchmarks completed using Everest Ultimate Edition v4.20.1257
All drives are SATA II (3.0 GB/s) but are running in backwards-capability mode.
The primary roles of the server is to serve files and store backups (File Server) and web and application development and website hosting (Web Server). I do not stream media, play music from the server or write extremely large amounts of data that are time sensitive. Therefore, my decision for RAID-5 was based on the space savings (n – 1) storage availability and the redundancy by means of the parity. I am willing to give up performance (write and read) for storage space and redundancy, but I want to know how much I’m actually giving up.
Tests
- HD Video Playback, 2x HD Video Playback, 3x HD Video Playback, 4x HD Video Playback
These benchmarks examine the behavior of the NAS unit while (simultaneously) playing one or more HD video files at 720p using Windows Media Player. Intel gives a percentage rate for the sequential reading of data in these tests, which lies at 99.5% for the HD Video Playback Test. With 2x HD Video Playback, it lies at 18.1%. The result is 6.6% with 3x HD Video Playback and 9.6 % with 4x HD Video Playback.
- HD Video Record
This test writes an HD Video file in 720p format to the NAS unit. This test is made of up of mostly sequentially transferred data.
- HD Video Playback & Recording
HD Video Play & Record examines the behavior of the NAS unit when simultaneously reading and writing an HD Video file in the 720p format. The sequentially-transferred data in this test is approximately 18% of the test.
- HD 2x Playback 2x Record
This benchmark is similar to the one above, but the proportion of sequential file operations is 3%.
- HD Playback With Office
This metric measures the data transfer rates when an HD Video file is read from the NAS unit while working with the Office applications. This test is made up of 608 files. The proportion of sequential file operations is 53.2%.
- HD Playback With Backup
Like the previous test, but this time an HD Video file is played while simultaneously carrying out a backup on the NAS unit.
- Content Creation
This benchmark is made up of 95% write operations to the NAS unit. This simulates the creation of files on the NAS unit such as is the case when, for example, using video editing programs.
- File Copy To NAS / File Copy From NAS
These tests determine the data transfer rate when copying files to or from the NAS unit. In both of these test processes, a 4 GB file is copied. Unlike with Backup / Restore, 64 KB is read and written.
- Directory Copy From NAS / Directory Copy To NAS
Similar to the previous test, files are copied to and from the NAS unit. A total of 126 files with a total size of approximately 188 MB are written and read across the network.
- Photo Album
This test determines how the NAS unit handles the supply of a multitude of small files—for example, viewing digital photographs stored on the NAS unit. It simulates the viewing of a total of 169 photographs with an overall size of approximately 1.2 GB.
Considerations and Assumptions
These tests were conducted on the Server, not over the network, which will probably result in very high throughput. I did this to get a feel for the actual performance, not that felt over the wire, and because I’m currently running 100 Mbps (Cat 5e) on my LAN, not gigabit. Thus, I did not want the network to be the bottleneck in these tests, as it would be saturated and skew the results.
I ran the tests 6 times, in each, manipulating the environment to establish a real world benchmark. Since this benchmark is done via an application, it will compete with other processes for resources. Additionally, it will compete with the actual Operating System who is responsible for transfering the data to the disk and calculating the parity for the raid (which is quite memory and CPU intensive). For test 6 I ran the system in Safe Mode, utilizing only necessary processes and services to run the operating system (Safe Mode without Networking). Thus, the numbers are quite high. In the others, I ran Outlook, SQL, browsed the internet and downloading files via torrents (to the actual RAID, so this will mess with the numbers as well) – not all at the same time, of course, except for test 4.
Results
| Test | 1 | 2 | 3 | 4 | 5 | 6 | Avg MB/s |
| HDVideo_1Play | 267.789 | 267.789 | 80.364 | 54.54 | 59.134 | 274.603 | 167.37 |
| HDVideo_2Play | 223.736 | 223.736 | 68.638 | 44.619 | 75.123 | 223.204 | 143.18 |
| HDVideo_4Play | 177.201 | 177.201 | 70.222 | 54.731 | 73.009 | 180.005 | 122.06 |
| HDVideo_1Record | 626.098 | 626.098 | 651.41 | 618.888 | 678.788 | 732.636 | 655.65 |
| HDVideo_1Play_1Record | 82.744 | 82.744 | 85.224 | 71.802 | 79.058 | 155.986 | 92.93 |
| ContentCreation | 6.199 | 6.199 | 5.983 | 5.628 | 6.141 | 6.022 | 6.03 |
| OfficeProductivity | 25.383 | 25.383 | 25.474 | 20.958 | 26.249 | 27.955 | 25.23 |
| FileCopyToNAS | 633.475 | 633.475 | 748.91 | 690.827 | 768.598 | 806.578 | 713.64 |
| FileCopyFromNAS | 147.096 | 147.096 | 79.675 | 57.42 | 80.561 | 151.573 | 110.57 |
| DirectoryCopyToNAS | 65.156 | 65.156 | 25.13 | 18.384 | 23.418 | 81.739 | 46.50 |
| DirectoryCopyFromNAS | 44.048 | 44.048 | 20.412 | 15.53 | 20.929 | 47.274 | 32.04 |
| PhotoAlbum | 27.748 | 27.748 | 26.411 | 19.508 | 28.171 | 29.204 | 26.47 |

Discussion
I attribute the absurd file copy speeds to caching and buffering at both the OS, RAID and the Drive level, as well as the writes for the video recording. All in all, I’m pleased with the benchmarks and am satisfied with the set up. One thing to note is that the tests were done from the SYSTEM drive to the RAID, and thus, the SYSTEM drive introduced an additional bottleneck as the transfers from it to the RAID were going to be quite slow (the drive is benchmarked at ~56MB READ and ~30 WRITE).
Remove Duplicates in MSSQL
by Steve Hernandez on Jul.25, 2008, under Technology
– Initiate transaction – if we fail, it won’t commit the changes
– Declare the variables to store the values returned by FETCH.
DECLARE @accountId varchar(40), @appearances int
– Get the recordset indicating the AccountId with duplicate entries
DECLARE duplicate_cursor CURSOR FOR
SELECT AccountID, COUNT(AccountID) AS namecount
FROM warehouse
GROUP BY AccountID
HAVING COUNT(AccountID) > 1
– Open the recordset
OPEN duplicate_cursor
– Perform the first fetch and store the values in variables.
FETCH NEXT FROM duplicate_cursor
INTO @accountId, @appearances
– Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
– delete all records for this accountId minus 1
– Determine how many records must be deleted
SET @appearances = @appearances – 1
– Limit the result of this delete to the above calculated maximum
SET ROWCOUNT @appearances
– Execute the delete
DELETE warehouse
WHERE AccountID = @accountId
FETCH NEXT FROM duplicate_cursor
INTO @accountId, @appearances
END
CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor
– Reset the rowcount limits
SET ROWCOUNT 0
GO
Remove Duplicates in MSSQL / De-dupe
by Steve Hernandez on Jul.20, 2008, under Technology
– Initiate transaction – if we fail, it won’t commit the changes
BEGIN TRAN dedupe
– Declare the variables to store the values returned by FETCH.
DECLARE @accountId varchar(40), @appearances int
– Get the recordset indicating the AccountId with duplicate entries
DECLARE duplicate_cursor CURSOR FOR
SELECT AccountID, COUNT(AccountID) AS namecount
FROM warehouse
GROUP BY AccountID
HAVING COUNT(AccountID) > 1
– Open the recordset
OPEN duplicate_cursor
– Perform the first fetch and store the values in variables.
FETCH NEXT FROM duplicate_cursor
INTO @accountId, @appearances
– Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
– delete all records for this accountId minus 1
– Determine how many records must be deleted
SET @appearances = @appearances – 1
– Limit the result of this delete to the above calculated maximum
SET ROWCOUNT @appearances
– Execute the delete
DELETE warehouse
WHERE AccountID = @accountId
FETCH NEXT FROM duplicate_cursor
INTO @accountId, @appearances
END
CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor
– Reset the rowcount limits
SET ROWCOUNT 0
GO
– End and Commit our transaction
COMMIT TRAN dedupe
MSSQL 2005: Database Diagram support objects cannot be installed
by Steve Hernandez on May.09, 2008, under Technology
If you ever get a message like this when trying to create a diagram in SQL 2005
“Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.”
Here’s step by step what you have to do:
EXEC sp_dbcmptlevel ‘yourDB‘, ‘90′;
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO “yourLogin”
go
use [yourDB]
go
EXECUTE AS USER = N’dbo’ REVERT
go
Where is ASP.NET 3.5 on IIS?
by Steve Hernandez on May.07, 2008, under Technology
Where is ASP.NET 3.5 on IIS ?
Most people on installing .NET 3.5 on the server “expect” an entry for v3.5 under the ASP.NET version Tab in IIS manager. And not finding that entry start panicking. If you have been one of those read on. The more tech savvy folks try to run aspnet_regiis -i from the v3.5 framework folder… but even that is missing
That brings us to the question
What exactly is ASP.NET 3.5 ?
Take a look at the v3.5 framework folder you will not find most of the files needed for ASP.NET to run like aspnet_isapi.dll / aspnet_filter.dll / aspnet_regiis.exe / aspnet_regsql.exe…
The reason being .NET 3.5 is not a stand alone framework like v2.0 or v1.1 . It is just an extension of the 2.0 framework. The extension provides developers with new language features like Anonymous Types, Extension Methods, Lambda Expressions and of course LINQ …. and AJAX is now integrated … also you have a few new ASP.NET controls <asp:ListView>, <asp:LinqDataSource>, <asp:DataPager>
Will I get ASP.NET version 3.5 under the ASP.NET tab in IIS 6.0 ?
Nope. Because as far as IIS is concerned nothing has changed. The aspnet_isapi.dll from the version 2.0 folder will still handle the requests and there is no new ISAPI dll specific to version 3.5. So you don’t have to change any Script Mappings in IIS …just leave the web application pointing to v2.0 under the ASP.NET Tab. And this brings us to the next question
What exactly is the extension ?
For that if you have Visual Studio 2008 go ahead and create a Website targeting the .NET 3.5 framework. Then go to the Property Pages of the Website and select References. You will find an entry like this
And those four assembly references provide the new features of v3.5. Here is a 10,000 ft view of the functionality implemented in these assemblies
System.Core – Provides namespaces like System.Linq, System.IO
System.Data.DataSetExtensions – LINQ to DataSet
System.Web.Extensions – AJAX
System.Xml.Linq – New XML API.. supports LINQ to XML
Do I have to configure anything manually ?
No. When you install the .NET 3.5 redistributable on the server it installs these assemblies in the GAC. And any website that refers to these assemblies can fetch them from the GAC so you do not have to configure anything manually. All you need to do is publish the content to a web application that is marked with ASP.NET v2.0
And now you should be thinking
The new language features should require a new compiler. How exactly is the new compiler used ?
The answer is your web.config file. When you create an ASP.NET 3.5 website in Visual Studio 2008 an entry is made in the web.config something like
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" warningLevel="4"
type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<providerOption name="CompilerVersion" value="v3.5"/>
<providerOption name="WarnAsError" value="false"/>
</compiler>
The new features do require a new compiler to be used and the web.config tells .NET that the v3.5 compiler has to be used.
What about IIS 7.0 ?
IIS 7.0 is no different.
Professional Resume of Steve Hernandez
by Steve Hernandez on Mar.30, 2008, under Uncategorized
.: Professional Resume of Steve O Hernandez :.
SQL Server Express 2005 will not install: Error 1706
by Steve Hernandez on Jan.21, 2008, under Technology
An installaton package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package ‘sqlncli.msi’
Open the downloaded executable with WinZip / WinRAR, etc. OR run the following command from a command prompt: SQLEXPR.EXE /x:c:\sqlexprtmp
Find the installer package sqlncli.msi and run it.
When prompted, select Uninstall and allow the uninstallation to complete.
Re-run the SQLEXPR.EXE installation executable. No reboot is required.
Joomla User login errors using Internet Explorer 7.0 (IE7)
by Steve Hernandez on Aug.16, 2007, under Technology
So registered users on one of my Joomla controlled sites simply could not log in if they were using Internet Explorer (6 or 7). Firefox and Opera work like a charm (of course :-p), but logging in using IE would just result in them being thrown back to the main page, with the error “Unauthorized to see this content”. So this is what I did to fix it on my site. I have a shared hosting account on a FreeBSD Unix Server hosting it’s own mySQL database.
I changed the configuration.php as follows:
$mosConfig_live_site = ‘http://www.mydomain.com/joomla’;
to:
$mosConfig_live_site = ‘http://mydomain.com/joomla’;
And then I changed the htaccess file (htaccess.txt in the root of the joomla installation) by adding the following lines:
Options +FollowSymlinks
RewriteEngine on
rewritecond %{http_host} http://www.mydomain.com/$1 [nc]
rewriterule ^(.*)$ http://mydomain.com [r=301,nc]
Now this alteration may not be 100% correct, but it’s what I got after searching the net for awhile.
Now all requests coming to the server with www in front are changed to the domain name without
it and internet explorer is working lovely. I’m not sure if it’ll work the other way around, but I wouldn’t think so. Additionally, this of course only works on Linux/Unix systems running Apache with the modwrite option on, and wouldn’t work on a Windows server (I think, don’t quote me though).
Good luck.