Category Archives: SQL Server

Correlation between MDF, LDF and BAK file whilst execution a database restore

I love it when things look the way they are supposed to be.
Look at the following Performance Montor graph where a +240GB database is being restored.

Location of the files:



  • BAK file on E volume ( turqoise or apple-blue-sea green color as I call it)

  • MDF file on F volume (yellow color)

  • LDF file on G volume (pink color)

Look at the correlation between the backup and MDF file. Also look at the difference in transactions between the MDF and the LDF file.


Steve

Import chinese charachters using CPDTS.DLL

Dear Lord! I”ve been working on a DTS importing chinese data from a SQL Server 2000 in China to a SQL Server 2000 in Europe. The clue was that the folks in Europe must be able to see the chinese characters…


Two important things are needed to accomplish this task:



  1. The client pc where the chinese characters are read must have the asian language pack installed for Windows

  2. The characters need to be converted from codepage 936 to 1250 (depending on the code page of course)




























Chinese Simplified (EUC) 51936
Chinese Simplified (GB2312) 936
Chinese Simplified (HZ) 52936
Chinese Simplified (Mac) 10008
Chinese Traditional (Big5) 950
Chinese Traditional (CNS) 20000
Chinese Traditional (Eten) 20002
Chinese Traditional (Mac) 10002


As far as I know, there is only one way to do it. Please do leave a comment if you think you have a better solution.
A certain DLL should be installed on your SQL Server: CPDTS.DLL. Read all about it on http://www.microsoft.com/middleeast/msdn/CPDTS.aspx


The procedure is quite straight forward and it tends to work but I was unlucky. A chinese woman almost wet her pants when she noticed the chinese characters were 90 degrees rotated….


I still don”t have a solution to this problem but I”ve found a quick workaround:



  1. Register the chinese server and your local server in Management Studio (Yes, Management Studio. Not Enterprise Manager)

  2. Import or export data from one database to the other database

So, why using the DLL? I noticed unreadable text when I performed a select * from [the table on the chinese server] in Enterprise Manager. Then I ran the same query in Management Studio and I was amazed to see chinese charachters. I”ve set up a simple data pump but do keep in mind using unicode datatypes in the destination table. And amazingly the destination table also showed chinese characters and don”t even have the asian language pack for Windows installed!


I hope it comes in handy for someone dealing with chinese data


Steve

Grade yourself as a good SQL Server DBA

The other day I was evaluating myself as how I”m doing as a dba. I”m not going to write down my scores but in my opinion, to be good your should come near something like this:











































































Database administration 10    No discussion here, you have to excel in administration
Installation  9 You might as well be good at it as this is a rather small domain to master
DTS  9
SSIS  9
ETL concepts  7
Clustering  8 Not everyone deals with clustering but one day they will
Security 10 Both Windows and SQL Server security and security concepts in general
VB.NET  7 Important in SQL Server 2005
SQL  9
T-SQL  9
SSRS  6 Athough a typical DBA does not develop reports but setting up SSRS in SQL Server 2000 can be quite difficult
SSAS  5
VBScript  9
Hardware  9
XML  7 We won”t get rid of it anymore
ERD  9
Disaster Recovery 10
Performance troubleshooting and tuning 10

So what”s your score?

Exploring MOM

I”ve been asked at work to evaluate several monitoring tools for high risk business critical high availibilty applications based on SQL Server. As we already have a deployed MOM in production along with the management pack for SQL Server, I thought I”d scroll up my sleaves and have a look…


Boy what a product! MOM, where have you been all my live? It takes a few days before you find your way around but is quite straight forward and doesn”t need training (I think).


I”m pleased with the product for 2 reasons:



  • I don”t have to write any scripts anymore for monitoring a bunch of SQL servers. MOM functions as a single point of monitoring, reporting, alerts and so on.
  • MOM enables you to design your own reports with Reporting Services which makes it a product with endless possibilities.

Some interesting links:


How to create a simple performance graph report for MOM 2005 using a counter.


Microsoft Operations Manager (MOM) 2005 Management Pack Development Guide








 

DBA: what to know

It came accross my mind today what a dba needs to know to do his job.
Many have written about this but so has half the globe about SP2 for SQL Server 2005.


Not only do you need to master general sql server dba stuff but also



  • You need to have a very good understanding of hardware (RAID, SAN, …)
  • Windows Server administration
  • IIS when you”re dealing with Reporting Services for SQL Server 2000
  • Master security (IIS, SQL Server, Reporting Services)
  • Excel in clustering, load balancing
  • DTS, SSIS (ETL in general)
  • Very often SSRS. Maybe not that much when it comes to developing reports but certainly the setup, configuration and administration
  • SQL
  • Programming skills for T-SQL
  • At least some basic database design skills
  • Visual basic.NET since SQL Server 2005 came out (try to do some descent work in SSIS if you don”t know how to program…)
  • Scripting (VBScript)
  • Troubleshooting performance issues (SQL Server, OS, hardware, applications,…)
  • You need to know how to handle projects
  • Be commmunicative with other departments

In my opinion,being a dba is one of the most dificult occupations you will find in a IT department. I still wonder where people find the time to master all this knowledge.


Steve

Custom date formats

Some DB2 twat in China thought he had it going all the way with a custom date format which is nowhere used on this crap ozon – too much CO2-globe. I had to set up a DTS (yes, I”m still using 2000) which imports enough data to keep the job running for 10 hours.


The query I had to write was complex enough it had to be debugged on…DB2. You see, I”m familiar with T-SQL but not with what I had to write today.


Yes, it”s quite possible by writing standard SQL but I mean, what the frack! I”m a SQL Server dba. Can”t they just use normal standard date formats like anyone else??!!


Even als a SQL Server dba you have to deal with DB2 and Oracle and don”t get me started when you need to talk to one of those dba”s! It usually ends up in screaming.


Anyways