Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Tuesday, 27 April 2010

You know Microsoft SQL Server is considered an enterprise database when Management tells you it costs too much !  Micro$oft has finally done it, and management has declared they don't want it.  I don't necessarily agree, but I do follow orders well.

The cost increase of 25% and virtualization licensing changes in R2 are unacceptable.

We're now reviewing Open Source databases and conversion costs, we're also contacting all our vendors and will be insisting on supporting some other DBMS than Microsoft SQL Server either now or by the end of the current contracts.

Microsoft has done a great job with sql 2008 r2, most features we don't need  [BI / MDM], didn't ask for, don't want and a cost increase to boot.  There are good features in SQL 2008 R2, but not at this cost; especially when we won't be utilizing most of the new features.

I'm not looking forward to Open Source, but I might be impressed, 10 years ago IBM DB2 pricing was reaching the tipping point and I began to learn a little respected database called Microsoft SQL Server, now it's time to begin learning something new.

I'd love to hear from some shops that began supporting open source databases, as obviously it will take some time to convert; but all new implementations on sql will most likely cease.

Tuesday, 27 April 2010 08:35:12 (Central Standard Time, UTC-06:00) | Comments [1] | General Technology | SQL Server#
Monday, 26 April 2010

Hard to believe, but it's true !

I've had a nice machine that came with windows vista, 64 bit; but it locked up constantly.  Over the months i've tried many things, even went with installing windows xp 32 bit, same behavior.  Finally I had concluded it must be a bad peice of hardware causing this mystery lock-ups...and let the thing become a paper weight.

Finally decided to try windows 7 last week, popped the dvd in, formatted the hard drive (no sense upgrading a non-working os) and installed windows 7.

Wow, windows 7, right out of the box, my machine finally works.

It's been on for a week without a single "lock-up", amazing !  Micro$oft may have gotten one right.

 

Monday, 26 April 2010 14:03:47 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Saturday, 12 September 2009

I'm in the middle of an all-nighter for a SAN Migration.  Lots of down time, here's what I found worth reading:

The unspoken truth about managing geeks.

Understanding Service Level Agreements for Database Development.

Is SQL Server's latest security hole a real threat?


Very interesting syntax of the in-clause I didn't know existed:

SELECT * FROM person.contact

WHERE 'thomas' in(Firstname,LastName,LEFT(emailaddress,7))

From the I did not know this would work department


Big competitors spark Netezza’s partner-friendly strategy

Keeley Hazell looks hot while she's shilling for something.

Don't ask me how I'd go from Keeley to Jeremiah Johnson, but all nighters due that to you, than and i've never been normal, it's boring.

"where ya headed?"
"Same place you are Jeremiah, Hell, in the end."

Link to download sound track to the movie, Crow Corner., a long time ago I had it on LP, doubt I'd ever find it on cd or mp3, but all night'ers have a way of helping you find things.

 

 

Saturday, 12 September 2009 20:40:51 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server | Web_Blog#
Thursday, 10 September 2009

Recently we upgraded to a new version of HP SIM (systems insight manager), of course not testing it or letting the DBA's know.  Suddenly some things crash.  The new version of HP Sim provides a "richer discovery model"; oh it's rich!

Seems the new version performs some type of scan on the scsi bus, which causes our multi-path software (EMC Powerpath) to loose connectivity to the SAN, this causes the file system to "Panic", and all filesystems unmount.  Nice.

SIM is a hardware monitoring solution from HP for HP servers.  The server administration team loves it, the SQL DBA's don't mind it.  It of course uses SQL Server for a back-end database, so it helps keep us employed as well !  Basically SIM provides hardware inventory and monitoring of the servers.

Specifically: 

Version:  Systems Insight Manager 5.3 with SP1 - Windows
Build version:  C.05.03.01.00 

Using the Manual Discovery Task that ships with the product.

The issue was most notable with our clustered servers, especially Polyserve.  Below were the error messages:

I/O error in nodelist_get for filesystem on psv30: nlblocknr=10, blocknr=10, nlsize=8192, size=8192, count=16.
umount: unmounting filesystem from psv30.
Filesystem on psv30 has finished disabling itself, and has no more writes to drain.
A psv-bound subdevice (psv7 - 0x8001) has been removed from the system.
Filesystem on psv39 has suffered a critical I/O error, and will be disabled to protect filesystem integrity.
The device, \Device\Harddisk140\DR645, is not ready for access yet.
\Device\MPIODisk398 is currently in a degraded state. One or more paths have failed, though the process is now complete.

Work closely with your administrators and be careful of how these monitoring solutions will affect your production servers.

Thursday, 10 September 2009 11:05:55 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve | SQL Server#
Tuesday, 25 August 2009


SQL Server SAN Migration

I think this is my 3rd or 4th SAN Migration casued by:
 - completely moving data centers
 - changing the storage backends to different vendors
 - consolidating SANS
 - growing to a bigger san to consolidate more

There are several different scenarios to consider
 1.  Stand alone SQL Servers Instances on Internal Disk to SAN
 2.  Stand alone SQL Servers on SAN Disk (changing sans).
 3.  VM Ware SQL Servers (required to be on SAN)
 4.  Microsoft Clustered SQL Servers
 5.  Polyserve Clustered SQL Servers
 6.  BCV's / SAN Mirroring / replication technologies
---------------------------
The most important thing to remember is to backup.  The next most important thing, no matter what the SAN Engineers, Windows Engineers or Vendors tell you about SAN Migration, YOU AS THE DBA ARE RESPONSIBLE.  Understand the migration plan for each scenario, regardless of what anyone says, the DBA is always left holding the bag.  If you don't understand the migration plan / scenario, make them explain it, learn it, try and practice it.  I'll explain with an example. 

One of the important items in most scenarios is updating to latest drivers and versions of software, even this step can be dangerous.  In a recent effort to patch servers to the latest version of drivers the SAN disk just "disappeared", when it came back, there was NO DATA.  No amount of research could yield what happenned to the data.  Refer back to the most important thing!
----------------------------

All of the different scenarios are simple, with careful preperation and a good windows and SAN team. 

1.  Stand alone SQL Servers instance on Internal Disk to SAN.

Usually the most difficult thing here is that you are taking an existing stable server and adding a lot of new complexity to it.  Drivers and hardware for the SAN, sometimes this does not always go well, if possible I try and get new hardware and completely swap the machines, configuring the new machine in advance for the SAN and installing sql; taking several dry runs to ensure it's stable.  The next issue is the down time in copying the data from the internal disk to the san.  Than switch the drive letters and start SQL.  Don't forget the most important thing.

2.  Stand alone SQL Servers on SAN Disk (changing sans).

Here is where having a good windows and san team can help you.  Most of the migrations I've been involved with the Windows and SAN team will setup a mirror between the SANS.  Than on migration day we stop SQL Server, the windows and san team ensure the mirror is up to date, than split the mirror and hook the server up to the new storage and ensure the drive letters and mount points come up.  SQL DBA restarts sql, boom, your done.

Sometimes if your switching san vendors, you can't setup a mirror.  Now things get dicey.  Can you get "hooked" up to both sans simultaneously, of so than your ok, stop sql, copy the data to the new san, reset drive letters and mount points, restart SQL. 

If you can not get "hooked" up to both sans simultaneously than you need to default to some type of backup and restore mechanism, tapes or copying sql files to local disk (if you have room).  I'm usually not a fan of this, as I find that different vendors use different drivers, switching vendors means now you have both vendors drivers on the machine, the machine decays and becomes unstable, NOT FUN.  Don't forget the most important thing.

3.  VM Ware SQL Servers (required to be on SAN).

These have been my easiest san migrations.  That's because we have awesome dedicated VM Ware administrators.  They do it all, just schedule the outage.  But trust me, Don't forget the most important thing, check your backups went to tape, double check your Disaster Recovery plan.

4.  Microsoft Clustered SQL Servers

I've only done Microsoft Cluster migrations with SAN Mirrors, and it has been uneventful.  This is because the "mirrors" end result is the preservation of clustered resources (quorum), drive letters etc.  But don't forget the most important thing.  Again a great windows and san team makes this easier.  I'd fret to switch vendors on the Clusters, but if I ever do I'll update this post.

5.  Polyserve Clustered SQL Servers

This is the SAN Migration worst case scenario, "The clustered file system".  Below are the steps we followed for migrating sans with Polyserve.  Don't forget the most important thing.

-Dump vsql and vsqlinstance information from cluster
 -mx vsql dump >> vsql.txt
 -mx vsqlinstance dump >> vsqlinstance.txt
-Get a listing of all storage by copying the grid on storage summary to excel
-stop and disable all SQL instances and VSQLs
-copy the virtual root for each sql server instance to another server (outside the cluster)
-deport ALL dynamic volumes (paths are automatically unassigned)
-stop cluster services on all cluster nodes
-copy the entire c:\polyserve directory to another server (outside the cluster) for each machine (CYA)
-manage the storage to unpresent all LUNs from the old array
-break the mirror relationships and then present all of the mirrored LUNs
-create three new 1GB LUNs on the new array and present them for new mem parts
-put partitions on the three new LUNs
-go into the config utility on node 1 and delete old membership partitions and add the three new membership partitions
-start cluster services on this node
-export the config to other nodes and start the service on the rest of the nodes
-import all importable dynamic volumes
-assign paths
-enable instances and vsqls
-done
Polyserve SAN Switch.doc (29.5 KB)

 

6.  BCV's / SAN Mirroring / replication technologies

Administring the advanced SAN technologies is different for each vendor and quite proprietary.  You definetly want to test and work with each one individually and ensure it all works.  The details of this are far outside the scope of a simple blog post, but having great SAN engineers will make this easy, as they generally setup the mirrors, clones and movement of them to different machines or remote locations.

Don't forget the most important thing.

Tuesday, 25 August 2009 10:16:01 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve | SQL Server#
Thursday, 20 August 2009

Ok, for whatever reason we compress the XML Blobs we store in the database.  This created the problem for me to view the data, as the DBA is always asked to go find this record in the XML.

I ended up creating a little windows form application in VS2005, using C# that can decompress the blobs so I can view the data.

I also added a peice that shows how to compress the blobs and save them to a database.  There was also a fun peice about displaying byte[] data in datagridview, it's in there!

I've also created a sample database with one table and a few records that are compressed, it's a sql server backup file, or you'd have to modify the code to create a table with some compressed blobs to see it work.

This was a just a fun thing to do, we get a lot of mileage out of compressing the XML data.

BlobTest.bak (2.58 MB)
XMLBlobViewer.ZIP (64.2 KB)

Screen shot:

Thursday, 20 August 2009 13:33:46 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Tuesday, 21 July 2009


Opening Installshield Cab Files / Extract Installshield cab file

Recently I needed to open an Installshield cab file to extract some SQL Server scripts from a failed vendor installation.  I was surprised to find that I was not able to do so.  Installshield does not use the same type of cab file that Microsoft does.

I found a command line utility to view and extract the cab files.  I also found an excellent shareware program, called ZipScan from foobar software. (if your allowed to use these at your company than great !).

In order to extract an installshield cab file I've put this together, you need two files (all listed at the end)

i6comp020.zip and i6comp103b.zip.

1.  Extract i6comp020.zip and put just the .exe in the folder with the cab file resides.
2.  Extract i6comp103b.zip and put only the zd51145.dll in the same folder as well.
3.  To extract the cab file open a command window and execute:

i6comp e file_to_extract.txt some_cab_file.cab

You can also put the file back in the cab file with the same utility

i6comp r file_to_put_back.txt some_cab_file.cab
-----------------------------------------------------------
e_wise.ZIP (97.47 KB)
extr_cab.ZIP (101.93 KB)
exwise05.ZIP (109.66 KB)
i5comp201.ZIP (100.69 KB)
i6comp020.ZIP (121.02 KB)
i6comp103b.ZIP (73.13 KB)
iscab.ZIP (374.39 KB)
isdcc122.ZIP (55.35 KB)
isdcc210.ZIP (96.39 KB)
wisdec100b.ZIP (367.53 KB)
zipscan_eval_setup.ZIP (876.67 KB)


Helpful files and utilities when dealing with installations
-----------------------------------------------------------
e_wise.zip        99806 2002/01/09  e_wise.zip - WISE Setup unpacker
                        Long desc:  e_wise.zip - WISE Setup unpacker (c) 2001
                                    Veit Kannegieser The idea for E_WISE comes
                                    from EXWISE from Andrew de Quincey. This
                                    version - reimplemented in Pascal - can
                                    handle more WISE-versions - can retrive
                                    filenames and date/time Use PEC.EXE to run
                                    on OS/2 Repackaged for installing Win32
                                    programs using Odin on OS/2 (eCs)
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
extr_cab.zip     104372 2002/01/09  Microsoft CAB file extractors
                        Long desc:  Microsoft CAB file extractors (c) Microsoft
                                    1994 - 1997 DOS and Win32 console mode
                                    versions of Microsoft's Extract tools for
                                    CAB files. Available for download in
                                    multiple other places on the internet,
                                    too. Extract.EXE runs on MDOS,
                                    Extrac32.EXE runs using PEC.EXE on OS/2.
                                    Repackaged for installing Win32
                                    applications using Odin on OS/2 (eCs).
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
exwise05.zip     112292 2002/01/09  exwise v0.5
                        Long desc:  exwise v0.5 - (c) 1998 Andrew de Quincey
                                    http://www.tardis.ed.ac.uk/~adq
                                    adq@tardis.ed.ac.uk This decompresses WISE
                                    install executables Use PEC.EXE to run on
                                    OS/2. Repackaged for installing Win32
                                    programs using Odin on OS/2 (eCs)
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
i5comp201.zip    103111 2002/01/09  i5comp v2.01 - InstallShield v5.x
                                    decompressor
                        Long desc:  i5comp v2.01 - InstallShield v5.x
                                    decompressor InstallShield v5.x
                                    Compression and Maintenance utility -]
                                    fOSSiL - Aug-24-1999 [- for Win32. Win32
                                    executable, use PEC.EXE to run it on OS/2.
                                    Use PEC.EXE to run on OS/2. Repackaged for
                                    installing Win32 programs using Odin on
                                    OS/2 (eCs)
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
i6comp020.zip    123921 2002/01/09  i6comp v0.20 - InstallShield v6.x CAB Tool
                        Long desc:  i6comp v0.20 - InstallShield v6.x CAB Tool
                                    Fully functional now. i6comp v0.20 Release
                                    Notes ~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                    (InstallShield v6.x CAB Util) -] fOSSiL -
                                    Jun-16-2000 [- -] Morlac - Dec-27-2000 [-
                                    This is a more improved package than
                                    i6comp103b!!! Use PEC.EXE to run on OS/2
                                    Repackaged for installaing Win32 programs
                                    using Odin on OS/2 (eCs)
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
i6comp103b.zip    74884 2002/01/09  i6comp v1.03 beta - InstallShield v6.x CAB
                                    Tool
                        Long desc:  i6comp v1.03 beta - InstallShield v6.x CAB
                                    Tool Release Notes ~~~~~~~~~~~~~
                                    (InstallShield v6.x CAB Util) -] fOSSiL -
                                    Jun-16-2000 [- This is a earlier beta than
                                    i6comp020!!! Use PEC.EXE to run on OS/2.
                                    Repackaged for installaing Win32 programs
                                    using Odin on OS/2 (eCs)
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
iscab.zip        383373 2002/01/09  The InstallShield Cabinet File Viewer for
                                    Win32
                        Long desc:  The InstallShield Cabinet File Viewer for
                                    Win32 (ISCabVu.exe) lets you select an
                                    InstallShield cabinet file and view its
                                    compressed files, file groups, components,
                                    and setup types and the properties of
                                    those items. It also lets you extract
                                    files from the cabinet file. CAB file
                                    editor (iscab.exe) lets you modify cabinet
                                    files. These two programs are extracted
                                    from InstallShield 6.21 package and
                                    "improved" by me so you don't have to know
                                    media password and component passwords.
                                    PS. Run setup.reg first. Runs on Windows
                                    only. Repackaged for installing Win32
                                    programs using Odin on OS/2 (eCs).
                        Runs on:    Windows 95/98/ME/NT4/2000
                        Contact:    Herwig Bauernfeind
isdcc122.zip      56677 2002/01/09  isDcc v1.22 - installshield script
                                    decompiler
                        Long desc:  isDcc v1.22 - installshield script
                                    decompiler (c) 1998 Andrew de Quincey
                                    http://www.tardis.ed.ac.uk/~adq
                                    adq@tardis.ed.ac.uk This is an
                                    installshield script decompiler. It
                                    supports all versions of installshield up
                                    to v5.5 (as far as I know). Use PEC.EXE to
                                    run on OS/2. Repackaged for installing
                                    Win32 programs using Odin on OS/2 (eCs).
                                    Use PEC.EXE to run on OS/2.
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
isdcc210.zip      98704 2002/01/09  isDcc v2.10 - installshield script
                                    decompiler
                        Long desc:  isDcc v2.10 - installshield script
                                    decompiler (c) 2001 Mr. Smith
                                    MrSmith_@hotmail.com This is a further
                                    modification of Andrew de Quinceys
                                    installshiled decompiler. Bug fixes: 1) I
                                    hope I fixed the problem of isDcc messing
                                    up labels ! 2) Fixed the bug which printed
                                    "string" instead of the modulo sign "%".
                                    Use PEC.EXE to run on OS/2. Repackaged for
                                    installing Win32 programs using Odin on
                                    OS/2 (eCs).
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
odinbug030.zip    36087 2001/08/15  Odin Bug Report Generator 0.3.0
                        Long desc:  Odin Bug Report Generator 0.3.0 (c)
                                    copyrighted FREEWARE Tool to generate Odin
                                    Bug Reports that are compatible with the
                                    standards that Team Odin has defined in
                                    the file ReportingBugs.TXT that is
                                    distributed with every Odin build. Bug
                                    reports can be created comfortably and
                                    fast.
                        Runs on:    every OS/2 that runs Odin
                        Requires:   VROBJ.DLL (VX-REXX runtime dll)
                        Contact:    Herwig Bauernfeind
                                    (herwig.bauernfeind@aon.at)
wisdec100b.zip   376352 2002/01/09  InstallShield Script decompiler
                        Long desc:  InstallShield Script decompiler (c) 1998 by
                                    NaTzGUL Use PEC.EXE to run on OS/2.
                                    Repackaged to install Win32 programs using
                                    Odin on OS/2 (eCs)
                        Runs on:    every version of OS/2 (eCS) that runs Odin,
                                    WinXX
                        Requires:   recent Odin build
                        Contact:    Herwig Bauernfeind
--------------------------------------------------------------
--------------------------------------------------------------
Useful links:
--------------------------------------------------------------
http://hobbes.nmsu.edu/cgi-bin/h-viewer?sh...2/i6comp020.zip
http://hobbes.nmsu.edu/cgi-bin/h-viewer?sh.../i6comp103b.zip
http://www.neowin.net/forum/index.php?showtopic=281349
http://www.datapool.de/Windows/Tools/Decompiler/
Tuesday, 21 July 2009 10:50:16 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Friday, 26 June 2009

The new loop.  I used to always default to using a vbs script to loop through and delete files, now I use forfiles, a one liner, and much easier to look at, just like the pic below!  Who knew looping could be so much fun.

Deleteing backups, log management...tiresome tasks.  Recently learned a new trick from SQL Puma for deleting files using a utility from the windows resource kit.  A program called forfiles.exe.  Much easier to use than deleting with a VBS Script (see my posts on Archive Logs and SQL Logs).  Powershell will eventually make even forfiles obsolete, but it is not on each of our servers and the windows support team is reluctant to put it there :( ForFiles is on every one of our windows 2003 servers so it's good to go.

To delete all backups older than 6 days:

forfiles /p "i:\backups\full" /m "*.bak" /c "cmd /c del /Q @path" /d -6

Delete all files in the standard TEMP folders and all their subfolders after 9 days:

forfiles -p"%SYSTEMROOT%\TEMP" -s -c"cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" -d-9
forfiles /p "%TEMP%" /s /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -9

Delete all *.TMP files in the system root (like C:\WINDOWS) that are older than 9 days:

forfiles /p "%SYSTEMROOT%" /m *.TMP /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -9

Delete old IIS log files:

forfiles /p "%SYSTEMROOT%\system32\Logfiles\HttpErr" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99
forfiles /p "%SYSTEMROOT%\system32\Logfiles\W3Svc1" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99
forfiles /p "%SYSTEMROOT%\system32\Logfiles\SmtpSvc1" /c "cmd /c if @isdir==FALSE echo del @file & sleep 8 & del @file" /d -99

Dealing with spaces in filenames:

Forfiles -p R:\MyFiles -s -m *.* -d -365 -c "Cmd /C Echo 0x22@Path\@File0x22"

Friday, 26 June 2009 08:51:10 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Friday, 15 May 2009

Recently I had a nice experience of working an outage of a SQL Server caused by a SAN Issue.  Here is where clustering breaks down.  Fortunately I work in a big shop which uses Microsoft, Veritas, Polyserve and VM Ware clustering technologies; but all of them have a single point of failure, the SAN.

The official response to the problem was:

We are experiencing intermittent {vendor here} issues causing some SAN storage to become read only. Server team is closely monitoring for this condition and putting the setting back to read/write. A fix is available and being planned for Saturday night, unless the issue becomes more prevalent that it is now.

 

Lovely.  What is missing from the statement above is depending on which clustering technology you are using, it may require a reboot to bring the storage back for windows (sometimes all nodes !).  Veritas, Polyserve and VMWare seem to handle san / fiber hickups the best.

 

It may be time to research a stretch cluster with different sans and some type of replication or mirroring.  The uptime of 9's (pick your number) is a difficult task to reach and in my opinion not truly possible with one SAN.  I've seen too many SAN Failures.  SANS are supposed to be built in redundant everything, but somehow almost all my outages on High Availability SQL Implementations are the SAN.

 

Of course it has to be something, i'm not inferring that a SAN is no good or poorly designed, just that as every point of failure is addressed, another one appears. 

 

How the vendor could know about this issue and not let us know, is confusing in itself.  The vendor is responsible for maintenance and patching of the SAN, seems they wanted to keep this bug "close to the vest" and maybe just "roll" it in with some other firmware patching.....i'm not impressed.

 

Keep your vendors accountable and ask them how often they patch the san, and what patches are missing from your environment.  Work with the vendor so they know that you are willing to accept patches and get them applied, don't wait for the bug to affect you before applying it.

 

This may apply to SQL Server as well, how often do we patch to a specific level and try and stay steady there, not wanting to apply all the cumulative updates, unless it affects something.  It may be an affect you don't like.

 

Be more pro-active.

 

Friday, 15 May 2009 12:14:28 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve | SQL Server#
Friday, 03 April 2009

Recently had to perform an update to some VBS Scripts I wrote in the past to archive and delete logs.  (Previous post: http://www.lifeasbob.com/2008/02/26/SQLServerLogManagement.aspx)  The scripts I've used in the past automatically move all files in a specific directory to an archive directory, and then delete the archived directories that are older than 30 days.  This was nice, but most of the directory names and the number of days were hard coded in the script, in addition it did not use wild cards (*.txt) to move the files, so it was all files or nothing.

I updated the VBS Scripts to take wild cards and directories (both source and target) as parameters.   The annoying thing is I implement these jobs via windows task scheduler (no sql agent on the box), and in order to call the vb scripts with parameters I ended up using batch files as I had multiple iterations of different wildcards and directories, guess we haven't come very far after all!

Anyway you may find this stuff useful for things where someone creates a process which generates lots and lots of files of any type.  In my case these were about 10 files per day, exported to excel, and we needed to archive them to different sub-directories based on the wild card.  What you end up with looks like this in Task Scheduler and File exploder...er, i mean explorer...


I've uploaded the examples of the vbs and bat files below, all have been renamed to have a .txt extension, so rename them after download.

Archive_Daily_logs_bat.txt (.6 KB) 
Archive_Daily_Logs_vbs.txt (4.95 KB)
Archive_Delete_Dirs_bat.txt (.36 KB)
Archive_Delete_Dirs_vbs.txt (.75 KB)

For the powershell Evangelist's, yes this can all be done in powershell, but since I only have powershell installed on 1 of 170 servers, it will have to wait.  Eventually i'm sure we will push powershell to 170 servers.  Soon i'll be creating a new powershell category and begin uploading and converting all these.

Friday, 03 April 2009 12:04:01 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Wednesday, 25 March 2009

Recently I ran into an issue where I needed to edit a large number of dtsConfig xml files.  I thought there was an option in the windows operating system to perform a find and replace across multiple files, but alas I can't find it...

Than I remember a vb script I had hobbled together from several other people to do this.  There are several share ware, free ware and other programs to do this, but I really think that find and replace is so basic of a task that it should be easy to kick out...

So here it is, remember that vbs files are a security issue when downloading from the internet, so i've uploaded them with a .txt extension.  Rename it to .vbs.  There are two programs, a replace.vbs and Replace_in_All_files.vbs.  The latter is the one that you must edit with the directory to look for files, extension of files to search and the find and replace string.

Use at your own risk.  The program will make a backup of the file.  VB Script is not the most efficient find and replace program for very large files, but if you have 100 or so dtsConfig files to edit, it works like a charm !

The same type of program can be done in a Power Shell script, and it is the cool new buzz language, but since it is only installed on 1 out 170 servers we support, it's not quite useful yet....i'll post the power shell script later.

replace.txt (1.76 KB)

Replace_in_All_files.txt (1.5 KB)

Wednesday, 25 March 2009 11:37:40 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server |  SSIS#
Thursday, 05 March 2009

Not sure if I just work for the most screwed up company in the world, or if I've just had a long run where I only seem to work for screwed up companies.  Maybe i'm drawn to them in some sort technology sociopath mental illness issue.  I was once told that, ""People with Mental Illness Enrich Our Lives".   If the same applies to a particular companies technology, than i'm enriched a lot!  To the problem...

 

The infinite wisdom of the powers that be of my company changed the domain\user login from domain\{first initials}{lastname} to a "generic number".  I know there are a lot of "smiths" and "guptas", so the original naming convention was not perfect either, but it worked for 80% of the employees.  The generic number is very frustrating for SQL Server DBA's.

 

The fun of verifying users for SOX Audits, just became a joy on par with cleaning toilets and replacing sewer lines, no longer easy enough to just run a query to get user's and permissions, now you need to translate the cryptic AD Login, so that management can verify each user....hmmm...thankyou sir, may I have another?  Imagine looking at sp_who2, or Activity monitor, or whatever query you use to view who is connected to your sql server and seeing, {domain}\x111111 as the user who is running a table scan against every table in the most important database you have, and the user was even nice enough to put a table lock hint on the query, nice! 

 

Now go find x111111 so you can ring his bell, or revoke their access and maybe they'll call you.  The x111111 is useless to you, at least the other AD login convention was useful most of the time, now it's completely useless.  Not only does it frustrate me on troubleshooting problems, but also adding new users.   Even though we modified the ticket system to ask for the login, invariably a significant number of tickets don't include the login name, and we have to bounce tickets around until we can find the users new cryptic AD Login.

 

To resolve this I had to write a utility that queries active directory and looks up the id of x111111 and returns their name.  I've included the two scripts (vbs - so rename them), one does a lookup by name (so horkay returns a553542) or by id (so a553542 returns Robert Horkay).  We also incorporated them into a web page, so that we could quickly and easily run these queries against Active Directory - (don't forget that your web.config will need to impersonate an identity so that you can query active directory). 

 

Next I'm going to have to write a custom version of the Activity Monitor, that uses the CLR to translate the cryptic Active Directory Login to a usable name, so that we don't have to bounce between management studio and a web page.

 

VBS Scripts (ensure to rename the txt to vbs).
FindbyLogin.txt (1.58 KB)
FindByName.txt (1.96 KB)
Web Page (remove .txt and change extension to .aspx and .vb)
AD_Users_aspx.txt (3.68 KB)
AD_Users_aspx_vb.txt (4.94 KB)
Web.config line for impersonation so the web pages will run [probably other ways to do this through the application pool etc, but this is how i did it].
<identity impersonate="true" userName="domain\sql-svc-acct" password="removed"/>
Thursday, 05 March 2009 12:05:30 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Thursday, 29 January 2009

Mountpoints are fun and easily can solve issues with more than 26 drive letters (don't ask), but recently we ran into several issues with monitoring the mountpoints.  In particular we have a report that is based on past growth history of a database, disk size and disk free space, and it estimates when a drive will be at 80% capacity and when it will fill up.   With 100's of database servers this report can prioritize and pro-actively identify which server will encounter a problem next and when.  Of course the limitation is in bold, drive. 

Report Example:

When we began using Mountpoints the report was not as accurate and it needed to be adjusted.  We have some internal services that collect the drive size and free space to a central DBA database.  Review of this monitoring reveals it is using a WMI Query, a quick review of the WMI SDK shows another call that will pick up mountpoints, Select * from WIN32_Volume.  Life is good.

Not so quick after hurdling from drives to drives and mountpoints, a problem was revealed where the WMI call failed on two servers.  Englightenment.  These two sql servers have also been giving us odd issues with SQL Management Studio (SSMS), which is highly dependent on WMI, SMO, .NET and probably some other stuff.  Fixing WMI on these two servers fixed the issue and corrected the SSMS issues.

Steps to fix WMI (Thanks to our Windows Team for the steps below):

1. net stop winmgmt
2. del %SystemRoot%\System32\WBEM\Repository /s /q
 
If that does not work, then I
 
1. remove all rights from %system32%\wbem\Repository\FS
2. disable the "Windows Management Instrumentation" service
3. reboot
4. add rights back to %system32%\wbem\Repository\FS
5. delete the contents of %system32%\wbem\Repository\FS
6. set the "Windows Management Instrumentation" service back to Automatic
7. start the "Windows Management Instrumentation" service

Here is a short quick Visual Basic Script (VBS) you can save to a text file with a .VBS Extension to see the call to WMI to check disk space for mount points or drives.  It filters out certain mount points for Polyserve as we don't want to monitor those, also for some reason z:\ is mapped in our environment and this wmi script pulls that with nulls, so you need to test for those.  You can also use Powershell, but it is using a wmi call underneath the hood as well, and we have yet to install powershell on all our servers.

Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//BCCMAPP02")_
    .ExecQuery("Select * from Win32_Volume")
For Each objItem In DiskSet
    Ignore = False
    if len(objitem.name) >= 51 then
        If UCase(Left(objitem.name,51)) = _
            UCase("C:\Program Files\PolyServe\MatrixServer\conf\mounts") Then
            Ignore = True
        End If
    End If

    If Ignore = False Then
        msgbox(objItem.Name & vbCrLf & "Percent Free = " & _
            round((objItem.freespace/objItem.Capacity)*100,2) & _
            " = " & objItem.freeSpace & " = " & objItem.Capacity)
    End If

Next
Thursday, 29 January 2009 12:33:07 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve | SQL Server#
Monday, 19 January 2009

The server could not load DCOM. [SQLSTATE 42000] (Error 7404).  The step failed.

Began receiving this error from failed sql server jobs on a sql server 2000 box and a windows 2000 box, after a reboot.

We didn't spend too much time researching the issue, as we were still in a maintenance window, so we performed another reboot and things worked fine.

I've seen many dcom errors over the years, but not this particular one from SQL Server, most likely com+ or ms dtc sub-systems (services) did not start or failed to start on the reboot.  There were no system or event log entries that were immediately helpful, though there were other odd entries that seemed related to dcom not working:

The server {73E709EA-5D93-4B2E-BBB0-99B7938DA9E4} did not register with DCOM within the required timeout.

The NetBackup SAN Client Fibre Transport Service service failed to start due to the following error:

The service did not respond to the start or control request in a timely fashion.

Timeout (30000 milliseconds) waiting for the NetBackup SAN Client Fibre Transport Service service to connect.

BMSS failed to bind to a network interface card (NIC) because none was specified in the registry. Check the registry to make sure the network bind policy is correct.

Monday, 19 January 2009 08:50:57 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Tuesday, 06 January 2009

Where did the add remove programs go in windows 2008 ?

Of course, it was renamed to "Programs and Features", still under control panel.

Also from Start --> Run --> appwiz.cpl

 

Tuesday, 06 January 2009 07:46:43 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Monday, 15 December 2008

I've got a development box, old dev box, windows 2000, sql 2000, that runs a restore script daily.   This Restore script is a vbs file, it restores a file ftp'd from the production box.  It has run for many years with no issues, now every 3-4 days it hangs and runs forever.  So far the only solution has been to reboot the box.

I've dug around a bit and found many instances (10, 15, sometimes 20+) of dw20.exe running in the process / task list.

This is listed as the windows error reporting tool.  Not sure what is erroring or being reported on, but since this server is shared with devlopers who have local admin access to the server, i'd bet someone did something that than began causing this error...either configuration changes or installed some software, difficult to determine.

So far I've only found a two easy solutions, put the box on a daily reboot, or disable the windows error reporting tool.

I'm going to try and disable the tool and see what happens:

http://support.microsoft.com/kb/841477 

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

To disable the Application Error Reporting tool, add a DWReportee value of 1 to following registry keys:

HKEY_CURRENT_USER\Software\Policies\Microsoft\PCHealth\ErrorReporting\DW
HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\PCHealth\ErrorReporting\DW
HKEY_CURRENT_USER\Software\Microsoft\PCHealth\ErrorReporting\DW
HKEY_LOCAL_MACHINE\Software\Microsoft\PCHealth\ErrorReporting\DW
Monday, 15 December 2008 11:07:50 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Wednesday, 08 October 2008

I have done some recent work on MySQL. Most of my work on the database side pertains to MS SQL Server, Oracle and DB2 but at times, I have done some MySQL related work as well. Here are some good links on MySQL:

Wednesday, 08 October 2008 09:45:55 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Monday, 06 October 2008

Recently needed to check the uptime of some windows server, mostly to verify that we had rebooted them all.

Quickly found a command line:

There are two options

1 - net statistics server

2 - SystemInfo |FIND /I "System Up Time"

Be nice to include this in my monitoring program, so it could be quickly queried from a centralized place, or embed this in some windows script that could take a list of them and run it remotely. 

Thanks to Rick Mcintosh.

Monday, 06 October 2008 10:08:30 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Sunday, 17 August 2008

I love patching clustered servers (not!), and patching polyserve is usually quite simple and not an issue.   I'm not referring to Polyserve patches, but Operating System (microsoft, hba, other drivers etc) patches.

We normally have designed a special "rolling" sequential patch of 15 minute intervals for the clustered servers, trying to give each server enough time to recover before the next one rolls.

Something happenned with that sequence, and things rolled like a ping-pong !  Polyserve stabilized, but there was an issue with two instances.  Review of the issue determined that the mount points that these instances rely on, did not happen, meaning the mounts failed.

I attempted to remount the volumes and received the following error, "10.10.49.114 assign_drive_letter failed: "D:\Mounts\SysData\" is already a reparse point.". 



I'm not really sure why this happenned, meaning windows, polyserve, san related root cause.  I attempted to reboot the nodes that these failed on and recieved the same error, hey a reboot fixes everything under windoze, right ?

I found the only way to correct this was to find the folder where these "reparse points" (junction point or mount point) stem from and to completely delete that folder.  So in the above example I had to delete the folder eamload_data1 under eam (remember to do this on each node that has a problem).  I then assigned the mount point as normal, and all the instances worked fine.

I've seen sporadic issues with mount points in the past, polyserve and non-polyserve.  See a previous point here on how to remove a ghosted mount point...

http://www.lifeasbob.com/2008/03/25/ManuallyRemoveAMountPoint.aspx 

It'll be interesting to see if this happens again or was a "one time" deal.

Sunday, 17 August 2008 14:42:27 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve#
Tuesday, 12 August 2008

Recently we upgraded the Proliant Support Pack (PSP) to higher version (8.10.0.0), and then we began to recieve the following error from the HP Network configuration Utility:


WARNING: The version of the miniport driver(s) for the following adapters are not compatible with the HP Network Configuration Utility software installed. Please install a newer version of the driver(s) for each adapter listed. The driver name, current version, and minimum required version are shown for each adapter:


Don't forget to upgrade your other software after upgrading the Proliant Support Pack.

Tuesday, 12 August 2008 08:10:44 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Tuesday, 03 June 2008

Upgrading Team Foundation Server to 2008.  The install / upgrade fails with the following sharepoint error:


Event Type: Error
Event Source: Windows SharePoint Services 3
Event Category: Database
Event ID: 5586
Date:  6/2/2008
Time:  4:38:01 PM
User:  N/A
Computer: TSTTFAPP01
Description:
Unknown SQL Exception 15023 occured. Additional error information from SQL Server is included below.

User, group, or role 'WSS_Content_Application_Pools' already exists in the current database.


I attempted to fix by adding the ServicesPipeTimeout key to 60000 and changed the WaitToKillServiceTimeoout to 120000, located at HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control


This did not resolve the error.

I've seen posts that this error can be ignored.

We're engaging Microsoft support for a solution.

 

 

Tuesday, 03 June 2008 08:34:24 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Wednesday, 07 May 2008

Windows\Installer Files (The Patch Cache).

 

The Windows root directory usually contains a folder, c:\windows\installer; we have noticed over time that this folder can grow to consume a significant amount of space, 2-6 gigabytes of space.  Many of these installer files are “dead” and can be moved, not removed !  Moving them to another partition may free up space.  Be careful as some installtion programs or modifications to existing programs will reference this directory, and if you can not move the files back, you have a very big problem.  Specifically installations of SQL Server 2005 require this directory for adding additional instances or options not selected, and if the files are not found in c:\windows\installer, you can not install or modify the programs.

 

The Patch Cache and Freeing Space

 

 Reference from http://blogs.msdn.com/heaths/archive/2007/01/17/the-patch-cache-and-freeing-space.aspx

When you install a patch using Windows Installer, the .msp file is cached in the %WINDIR%\Installer directory. This accounts for some of the space required by Visual Studio 2005 Service Pack 1.  A single patch is cached only once regardless to how many products the patch applies.

Starting with Windows Installer 3.0, any patches that contain the MSIPatchSequence table cause the Windows Installer service to cache any of the original files being replaced into the baseline cache.  Any files being replaced in the latest minor upgrade by small update patches with this table are also cached. It is this baseline cache that consumes a lot of drive space on the system drive after installing VS 2005 SP1. The baseline cache facilitates patch uninstall by storing the original files so that they can be copied back to the target locations. Files in existing patches do not need to be cached because they are contained within the cached .msp files. For this reason and because Windows Installer will require these patches during repair and future patch scenarios, the .msp files should not be deleted except by uninstalling the patch from each product to which it's applied. The baseline cache also improves performance when using binary deltas.

Baseline caches are created separately for per-user unmanaged installations, and for both per-user managed and per-machine installations. If you enable Windows Explorer to display system files or type dir /a:s under %WINDIR%\Installer you'll find a directory structure like the following:

  • %WINDIR%\Installer
    • $PatchCache$
      • UnManaged
        • {UserSID}
          • {Squished ProductCode}
            • {ProductVersion}
      • Managed
        • {Squished ProductCode}
          • {ProductVersion}

Be careful doing any modifications under %WINDIR%\Installer. Like registry changes, making mistakes here could cause problems that may require the difficult task of rebuiling the Windows Installer cache or even reinstalling Windows.

To free up space, you can remove the baseline cache for Visual Studio 2005 under %WINDIR%\Installer\$PatchCache$\Managed by deleting the directory with the squished GUID representing the ProductCode for whichever Visual Studio 2005 products you have installed. The squished GUID is a transformed ProductCode. Attached you'll find a list of product names, product codes, product languages, product editions, and squished GUIDs for Visual Studio 2005 and the .NET Framework 2.0.

Again, be aware that by removing the baseline cache for a product, future repair, patch install, and patch uninstall scenarios may require your original installation media. If you have the drive space it is recommended that you keep the baseline caches available.

 

Wednesday, 07 May 2008 08:15:36 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Monday, 21 April 2008

While Upgrading Polyserve 3.4 to 3.6, performing a rolling upgrade, on the first server, after starting the services, while performing the license file upgrade, the following error is thrown across all Other servers in the cluster, and they shutdown ! 

So far we are turning off Data Execution Prevention (DEP), for ClusterPulse.exe. 

UPDATE:  Turns out the root cause we started the 3.6 upgrade with the wrong server, you must start with the highest IP Address, we started with the lowest.

To disable DEP for a program or server wide perform the following:

  • Right click on My Computer
  • Click on Properties
  • Click on the Advanced tab
  • Click on the Settings button in the Performance section
  • Click on the Data Execution Prevention tab

Screen Captures of the Error Message:

Screen Captures of the solution:

Screen Captures of Turning it off:

 

Monday, 21 April 2008 16:00:49 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve#
Friday, 28 March 2008

Nothing worse than an upgrade that breaks stuff.  Work recently upgraded our symantec client to Symantec Endpoint Protection MR1.  Great. 


Next thing you know i'm getting the outlook error:


The solution was of course a patch, which is documented at:

http://service1.symantec.com/support/ent-security.nsf/854fa02b4f5013678825731a007d06af/4430b74b4f4ba805882573c60003a35c?OpenDocument 

Outlook attachments are unavailable with the AntiVirus Outlook plug-in installed and disabled.

Question/Issue:
After installing Symantec Endpoint Protection MR1, Outlook attachments are unavailable with the AntiVirus Outlook plug-in installed and disabled.


Solution:
This is an issue with Symantec Endpoint Protection 11 MR1.


To resolve this issue without using the patch there are two steps:

  • If you upgraded from Symantec Endpoint Protection 11 to Symantec Endpoint Protection 11 MR1 then you will need to disable the Outlook plug-in both in Symantec Endpoint Protection and in Outlook.

      To disable the plug-in from the Symantec Endpoint Protection Manager (SEPM):
        1. Open up the AV policy in SEPM.
        2. Click on Microsoft Outlook Auto-Protect
        3. Unselect Enable Microsoft Outlook Auto-Protect

      To disable the plug-in from within the Symantec Endpoint Protection (SEP) client interface:
        1. Open up the SEP client UI.
        2. On the AV bar, click Options and then click Change Settings...
        3. Click on Outlook Auto-Protect
        4. Unselect Enable Microsoft Outlook Auto-Protect

      To disable the plug-in from Outlook:
        1. Open Outlook
        2. Click Tools -> Options
        3. Select the Other tab
        4. Click the Advanced Options button
        5. Click the Add-in Manager button
        6. Unselect the Symantec AntiVirus Outlook Protection


 

  • If you installed Symantec Endpoint Protection 11 MR1 directly without upgrading from Symantec Endpoint Protection 11 and the above steps do not work, you will need to go to "Add or Remove Programs" to remove the AntiVirus Outlook Plug-in.

      Steps to uninstall the Outlook Plug-in:
        1. Open up the Control Panel
        2. Open Add or Remove Programs
        3. Select Symantec Endpoint Protection
        4. Click Change
        5. When the installer comes up click Next
        6. Select the Modify option and click Next
        7. Open the AntiVirus Email Protection
        8. Click on the Outlook Scanner and select the This feature will not be available option
        9. Click Next and then Finish
Friday, 28 March 2008 13:33:17 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Tuesday, 25 March 2008

Don't ask me why, but the Mount Point failed unmounting via gui.

No error, no message, but the mount point still shows in explorer.  Reboot, still shows in explorer.

Drop to the command line and run

MountVol

MountVol d:\crm /D

 

 

 

Tuesday, 25 March 2008 13:30:38 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve#
Thursday, 20 March 2008

How do you determine which IP Address is bound to which NIC card in a mult-honed machine ?

Recently I ran into the task of ensuring the nic cards on our Clustered machines were all named with the standard "Private" and "Public" as opposed to various things like "network adapter 1" etc.

All our Private nics in the cluster start with 192.x.x.x.  So to ensure I was naming them properly I needed to find what IP address was bound to "network adpater 1" etc.  This seems easy but for some reason took me a while to figure this out.

( and if you look carefully in this example you will see the current nic card labeled "admin" has a public IP Address, someone goofed ! and reversed them, so this is important stuff to know how to check !)

We found two ways to do this, through windows control panel, and through our vendor's nic card configuration utility (HP).

Select Start -> Control Panel -> Network -> {Adapter Name} than right click and select status.

From the Status properties window you can select the "advanced" tab and determine which IP Address is bound to this adapter.

Our particular Vendor is HP, and from the HP Network Utility you can select a NIC Card and then choose, Information, and in the Details section you can find the IP Address.

Thursday, 20 March 2008 09:35:09 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve#
Friday, 14 March 2008

We use an EMC Symmetrix SAN.  We zone lots of storage.  Recently had some new storage zoned and we could not "see" the storage.  Turns out that 64 bit windows can only see the first 256 luns zoned to an FA Channel.  Now 32 bit windows does not have this issue, so things actually "took a step backwards" with 64 bit.

 

This was a frustrating development.  Currently we have no solution from Microsoft (thankyou).  The work around is to have our SAN Administrator zone luns over 256 down another FA Channel.

 

Plan you SAN Carefully.

 

Found this:

310072 Adding support for more than eight LUNs in Windows Server 2003 and in Windows 2000

http://support.microsoft.com/default.aspx?scid=kb;EN-US;310072

Friday, 14 March 2008 08:34:09 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve | SQL Server#
Thursday, 13 March 2008

MySQL Dump File to SQL Server.  Some how I thought this would be easy, while it was not overly difficult it did require some basic skills, parsing.  I ended up not taking this as far as I wanted, but all i needed was a down and dirty import of a mysql dump file into sql server.  There are many ways to do this, the best two options are to (purchase a utility) or (install mysql, import the dump file, create a proper export that sql server can read and/or an ssis package).  I didn't want to install mysql or purchase a utility. 

 

I wrote a c# program that parses the dump file out into a DDL and DML File.  I than took the DDL File imported it into Erwin as a mysql database, than changed the database to SQL Server and forward engineered the schema to a TSQL Script file.  I then created a SQL Server database off of the TSQL Script file.  Next I ran the TSQL DML File against the database, and poof, i'm done. 

 

This example will not work for every mysql dump file, nor did i perform lots of testing, i just needed something converted.  It is not pretty code.  But maybe it can help you...

 

There was some interesting bits in here; reversing a string, parsing a file one byte at a time and evaluating the byte, word breaking analysis and word grouping.

 

The resulting files still needed a little cleanup, but it was very easy.  The DDL File had one data type that erwin didn't recognize.  The dml file has some extraneous data from the parse, but it is easily removed.

 

I've included examples of the source dump file i used, and the resulting ddl and dml files.

DDL_Output.txt (2.8 KB)
mysql_dump.txt (121.02 KB)
DML_Output.txt (207.73 KB)

 

Thursday, 13 March 2008 09:23:30 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | SQL Server#
Monday, 10 March 2008

Recently had an issue with a friends machine, completely dead / unresponsive.  Regular troubleshooting steps yield no real reason why the machine would be in such a state, but it is 4 years old and based on the amount of hair, dust and tobacco smoke found in the machine, it's a wonder it ran this long, "Buddy, buy a new machine, it's not worth fixing". 

I acquire another brick in my basement.

Two or three weeks go by, I find I need a cd drive for a test machine I have, remembering the brick, I scavenge the cd-rom and start the machine up, nothing !  Crap, i've just burned up my own test machine !  Sure enough, now I have two complete bricks, and the test machine was running fine before I put in the cd rom from the other machine.  Never have I seen a cd rom fry a system board, but it has to be the root cause. 

Be careful of used parts, you get what you pay for.  You can bet both these bricks are going to the recycling center asap !

In attempting to fix my test machine I had to troubleshoot the system board and put together this list.  It didn't help me, but was interesting to research:

 

AMI BIOS

Number of Beeps

None
You're supposed to hear at least one beep. If you truly don't hear anything, your computer's power supply is no good. Either that or your motherboard or PC Speaker is broken.


One
One beep is good! Everything is A-OK, that is if you see things on the screen. If you don't see anything, check your monitor and video card first. Is everything connected? If they seem fine, your motherboard has some bad chips on it. First reset the SIMM's and reboot. If it does the same thing, one of the memory chips on the motherboard are bad, and you most likely need to get another motherboard since these chips are soldered on.


Two
Your computer has memory problems. First check video. If video is working, you'll see an error message. If not, you have a parity error in your first 64K of memory. First check your SIMM's. Reseat them and reboot. If this doesn't do it, the memory chips may be bad. You can try switching the first and second banks memory chips. First banks are the memory banks that your CPU finds its first 64K of base memory in. You'll need to consult your manual to see which bank is first. If all your memory tests good, you probably need to buy another motherboard.


Three
Basically the same thing as 2 beeps. Follow that diagnosis above.


Four
Basically the same thing as 2 beeps. Follow that diagnosis above. It could also be a bad timer 1.


Five
Your motherboard is complaining. Try reseating the memory and rebooting. If that doesn't help, you should consider another motherboard. You could probably get away with just replacing the CPU, but that's not too cost-effective. Its just time to upgrade!


Six
The chip on your motherboard that controls your keyboard isn't working. First try another keyboard. If it doesn't help, reseat the chip that controls the keyboard, if it isn't soldered in. If it still beeps, replace the chip if possible. Replace the motherboard if it is soldered in.


Seven
Your CPU broke overnight. Its no good. Either replace the CPU, or buy another motherboard.


Eight
Your video card isn't working. Make sure it is seated well in the bus. If it still beeps, either the whole card is bad or the memory on it is. Best bet is to install another video card.


Nine
Your BIOS is bad. Replace the BIOS.


Ten
Your problem lies deep inside the CMOS. All chips associated with the CMOS will likely have to be replaced. Your best bet is to get a new motherboard.


Eleven
Your cache memory is bad and your computer disabled it for you. You could reactivate it by pressing -Ctrl- -Alt- -Shift-
-+- , but you probably shouldn't. Instead, replace your cache memory


Phoenix BIOS

1-1-3
Your computer can't read the configuration info stored in the CMOS. Replace the motherboard.


1-1-4
Your BIOS needs to be replaced.


1-2-1
You have a bad timer chip on the motherboard. You need a new motherboard.


1-2-2
The motherboard is bad.


1-2-3
The motherboard is bad.


1-3-1
You'll need to replace the motherboard.


1-3-3
Same as AMI BIOS 2 beeps. Replace the motherboard.


1-3-4
The motherboard is bad.


1-4-1
The motherboard is bad.


1-4-2
Some of your memory is bad.


2-_-_
Any combo of beeps after two means that some of your memory is bad, and unless you
want to get real technical, you should probably have the guys in the lab coats test the memory for you. Take it to the shop.


3-1-_
One of the chips on your motherboard is broken. You'll likely need to get another board.


3-2-4
Same as AMI BIOS 6 beeps. Keyboard controller failure.


3-3-4
Your computer can't find the video card. Is it there? If so, try swapping it with another one and see if it works.


3-4-_
Your video card isn't working. You'll need to replace it.


4-2-1
There's a bad chip on the motherboard. You need to buy another board.


4-2-2
First check the keyboard for problems. If nothing, you have a bad motherboard.


4-2-3
Same as 4-2-2.


4-2-4
One of the cards is bad. Try yanking out the cards one by one to isolate the culprit. Replace the bad one. The last possibility is to buy another motherboard.


4-3-1
Replace the motherboard.


4-3-2
See 4-3-1


4-3-3
See 4-3-1


4-3-4
Time of day clock failure. Try running the setup program that comes with the computer. Check the date and time. If that doesn't work, replace the battery. If that doesn't work, replace the power supply. You may have to replace the motherboard, but that is rare.


4-4-1

Your serial ports are acting up. Reseat, or replace, the I/O card. If the I/O is on the motherboard itself, disable them with a jumper (consult your manual to know which one) and then add an I/O card.


4-4-2
See 4-4-1


4-4-3
You math coprocessor is cussing. Run a test program to double-check it. If it is indeed bad, disable it, or replace it. Disabling is fine, because you probably don't need it anyway.

 

Monday, 10 March 2008 08:20:37 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Wednesday, 20 February 2008

All glory goes to Kevin Niederhauser with an assist from Jeff Pickett ;

Here's what I did to get the data that I needed. the bit mask is on the groups table, column gr_group_id. It is related to the (int) column on the users table...us_group.

 

SELECT sa.full_name, gr_group_name from

[USERS] u, [GROUPS] g, [qcsiteadmin_db].[td].[USERS] sa

where substring(u.us_group, g.gr_group_id + 1, 1) = '1'

and u.us_username = sa.user_name

order by sa.full_name

 

Wednesday, 20 February 2008 13:50:27 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
DTA - Failed to initialize MSDB dat...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll