Skip to main content

Posts

Showing posts from June, 2010

DB2 – Create a Database

OK for my next DB2 trick and remaining off the path of the righteous for the time being we will look at creating a DB2 database. I assume that you have the DB2 RDBMS software installed, you can get a free 'community' copy of the express edition, follow this link for the download. Being a SQL DBA with an MSDN subscription I installed DB2 on Windows Server R2 2003 running on VM Workstation. It can also run on a variety of *nix OS too.As with SQL Server there are many ways to create a database in DB2. In this example I am going to use the wizard, I'm new to this so I'll go with the simple approach. Assuming you have all the components installed, as I do, you need to fire up Control Manager.Note: There are differences in the physical architecture of database files when comparing DB2 to SQL Server. As my understanding improves I may write a little about that topic but for now we'll focus on using wizard and creating a database.Before we start let me give you a bit of ba…

DB2 Editions

This is a little bit off topic for me but on Friday I was asked to look at a DB2 instance and sort out some issues with the CPU maxing out. Make no mistake I’m no DB2 expert, but I have done some small pieces of work on it previously, and about a year ago I started making some notes . I’m going to turn these notes into blog posts, this will help my learning and give a place to reference and maybe it will help someone else out on the way.DB2 like SQL Server has a series of edition with different features available in each one, here are some notes on the DB2 editions:DB2 Express C – Full functional version of DB2 that is free to download use and distribute. The C stands for Community.DB2 Express edition – Similar to the Express C version but there is a cost for support for this edition. It is designed for a quick installation and subsequent use. It supports 2 CPUs and 4GB of RAM. It can run on Linux, Windows and Solaris.DB2 personal edition – Designed for single user use, It frugal with…

Lock Pages in Memory

It is generally recommended that 'lock pages in memory' be enabled for your SQL Servers to prevent the OS paging out SQL Server memory.how do you do this?Well, the answer is straight forward...If you have SQL Server Enterprise Edition:You allocate the service account running SQL Server the 'Lock Pages In Memory' permission.How Can I Tell if 'Lock Pages in Memory is Enabled?'NoteI am using Windows 2003 R2 and SQL Server 2008 SP1 for this demo but I have also done this on Windows 2008 R2 and it works just the same way. It is a 64 bit environment.If you are running SQL Server with the Local System account, I know most of you are not, but for those of you that are, SQL Server will automatically 'lock pages in memory'. Bob Ward has a blog post on this here: (http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx)If you are using the local system or a domain account with appropriate permissions you will …

24 Hours of PASS

On the 19th/20th May (last month) the Professional Association of SQL Server (PASS) held its second 24 hours of PASS event . '24 hours of PASS' consists of 24 individual hour long meetings/presentations/training session all with a SQL Server 2008 R2 theme, held online and delivered by some of the most respectable names in the SQL Server community. This is a great event well worth looking into and attending.I was on holiday at the time so wasn't able to attend any of the events 'Live' so to speak and even if I had been able to 24 hour long sessions over the course of 1 day or 24 hours period would be too much. I need my beauty sleep. So to allow people to catch up on what they missed PASS kindly record these sessions and make them available online to watch at your convenience. Obviously you lose the ability to ask questions and interact with the presenter but it is a great opportunity to catch up on anything you missed.You can find the sessions here:http://www.sqlpa…

Windows 2008 R2 Install Error - Using VMWare Workstation

Recently I was installing Windows Server 2008 R2 Enterprise Edition onto a virtual machine (VM) I was setting up to carry out some testing when I encountered the following error:"The unattended answer file contains an invalid product key. Either remove the invalid key or provide a valid product key in the unattended answer file to proceed with the Windows Installation."The error box is below:The VM was hosted on VMWare Workstation. I know I had a valid key, it was a key from my MSDN subscription. After a short trawl on the internet I found a few forum posts with people encountering similar issues. The one I used can be found below:http://communities.vmware.com/thread/244248 From what was said in a few of these threads It seems that when setting up the VM using VMware Workstation but before I began the OS installation I had entered the Windows 2008 R2 product key as part of the VM setup wizard. VMWARE or Windows didn't like this and the forum post suggested I remove the p…

Football Update

Well the World Cup is now half way through, 32 games played, 32 games to go. The talk here in the UK has focused around England their poor start. Two games played, one goal scored, one goal conceded, two draws, no wins, no losses, and they seem to lurch from crisis to crisis. Starting with the goalkeeper and his now infamous error, moving onto what can only be described as a very poor display against, one of the weakest, on paper anyhow, teams in the competition in Algeria and now ex-captain John Terry being publicly critical of ‘Don’ Fabio and the ‘Don’ calling this his ‘Big Mistake.’ The ‘Don’ stripped Terry of the captaincy earlier this year for Terry’s off the field antics with his best friend and England Team mates girl friend. As a direct result of this Wayne Bridge is taking no part in the World Cup and retired from International football. From a semi independent point of view it looks like Terry is determined to leave his mark on this competition one way or another.So we look …

SQL Server MasterClass

This Thursday gone, (June 18th) I went to a hotel near Heathrow airport in London for what was titled a “SQL Server MasterClass” and I was not disappointed.I have attended several taught MCP SQL Server training courses over the years and several conferences including SQL Bits and this MasterClass taken Paul Randal and Kimberly L Tripp has to be the best training/knowledge transfer day I have been on. Kimberly and Paul have an amazing knowledge of SQL Server and such a great presentation style that makes this day a must for any SQL Server professional. The agenda looked something like this:1. KEYNOTE Bridging the gap between Developers and DBAs – The crux of this was that Developers need to write code that scales to a high volume production environment, thus helping out the DBA and DBA’s need to be flexible and help Developers with training and advice. The key to achieving the utopia was communication and team work and this set the tone for the rest of the day.2. This took us onto a “S…

Finding Duplicates

This is just a short post that uses a contrived example to demonstrate how to find duplicate records in a table. I needed to identify some duplicate records for a supplier recently and I wanted to make some notes on what I did for future reference.

First I will create a very simple table for this example

CREATE
TABLE [dbo].[Dups]

(
[FirstName] [nvarchar](50)
NULL,
[lastName] [nvarchar](50)
NULL,
[Company] [nvarchar](50)
NULL

)
ON [PRIMARY]

I will then add some example data, including some duplicate rows:

INSERT
INTO [C_AVG].[dbo].[Dups]

(
[FirstName],
[lastName],
[Company]

)
VALUES (

'Gethyn',

'Ellis',

'GRE'

)


INSERT
INTO [C_AVG].[dbo].[Dups]

(
[FirstName],
[lastName],
[Company]

)
VALUES (

'Lisa',

'Ellis',

'GRE'

)

INSERT
INTO [C_AVG].[dbo].[Dups]

(
[FirstName],
[lastName],
[Company]

)
VALUES (

'Ron',

'Ellis',

'GRE'

)

INSERT
INTO [C_AVG].[dbo].[Dups]

(

The Football World Cup

This day only comes around once in every four years, we've all been waiting for it for months. It's been in the news and media, there has been a great deal written about qualification, preparation and selection and now with all that done and dusted its finally arrived...The start of the Football World Cup.For my American friends, this is probably better known as the soccer world cup, but to me football is football and this is the football world cup.For the next four weeks, day in day out the best players in the world will represent their countries and someone will become world champions. Italy won it last time in 2006. Spain and Brazil (as always) are the bookies favourites this time. This year it's being held in South Africa. This means that the majority of the games will be shown on TV when I'm home from work. I think there is only 1 hour difference in time, with South Africa being on GMT +2 and the UK being on British Summer Time (BST) which is GMT+1.My team Wales,…

SSMS 2008 and changing a SQL 2005 SA password.

You can probably tell from some of my more recent posts that I have been doing some work lately on SQL Server 2008. I have also recently (the last two months) upgraded my work desktop to Windows 7. As a result of this of this, and the continued migration away from SQL Server 2000 I have only installed the SQL Server 2008 client tools (Management Studio) onto my desktop and I use that for working on both SQL Server 2005 and SQL Server 2008 instances.This week, for reasons beyond the scope of this post I wanted and needed to change the sa password on one of my SQL Server 2005 instances. No problem I thought, I already had SSMS 2008 open on my desktop. I connected to the SQL Server 2005 instance using Windows Authentication. (The SA account is not generally used) I opened the security folder, and then the logins folder, right-clicked on the sa login and selected properties. The properties box opened, I typed a new 'password' into the 'password and confirm password' dialog…

T-SQL Tuesday #007 - Policy Based Management

It's that time of the month again, time for the next round of T-SQL Tuesday posts. This month is being hosted by Jorge Segarra (Blog | Twitter) and we are blogging about new features in SQL Server 2008 and SQL Server 2008 R2. Plenty of scope then as this is quite a wide topic. I have posted previously on file streaming, you can see those posts here but for this post I am going to keep it simple and fresh and look at another new feature of SQL Server 2008, Policy Based Management.
I have been meaning to write something on policy based management for some time. Policy Based Management allows DBAs and administrators to apply policies or enforce rules on all the SQL Server 2008 instances they look after and ensure that a consistent approach is followed on all instances. It can be used by DBAs to enforce a 'standard' configuration across the SQL Server farm.
Where would you use it?Well, firstly only on SQL Server 2008 and beyond. It won't work on 2005 or 2000.
There are nume…

Cannot Connect to SQL Server

I recently installed a new SQL Server 2008 instance on a brand new Windows 2008 R2. When I tried to connect to said instance using Management studio on my desktop i received the following error message:"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)"An initial google search on the error returned a whole bunch of information on SQL Server 2005 and enabling remote connections. Well you don't need to 'enable remote connections' as such in SQL Server 2008 but I did check that the TCP/IP protocol had been enabled…

SQL Server 2008 Installation Fails to Start

Recently I have been installing a new SQL Server 2008 instance on Vmware vSphere based  virtual server, the OS was Windows 2008 R2. I have done this several times before and I don't recall ever getting this issue. Seeing as it took me a short while to figure out what was going on I decided to make some notes about it here. I attached the SQL Server 2008 iso to the VM, auto-run failed to kick in so off I went to the CD/DVD drive to start the setup manually. First up, it warned me that I needed to update Windows installer and the .Net framework to 3.5. I clicked OK to do this and it ran successfully and then the server prompted me to reboot. When the server had restarted I ran the setup again but this time nothing happened, no error, no start screen, nothing. I did this a few times again, I rebooted again, not sure why I rebooted, more out of habit than necessity and just to make sure. I tried setup again, same thing. Off I went to the event viewer and I found an Error in the log th…

Weekly Update - Internet Issues

I haven’t written anything technical or SQL Server related for that matter on my blog for a few of weeks. Mainly because I spent a couple of  weeks in Florida celebrating my birthday. I did take my laptop on my holidays mainly because we booked everything flight, car, hotel etc online and I thought it would useful to have at hand if I needed it in an emergency. It is also useful to call home, SKYPE’s international rates are pennies and when you compare that to my mobile phone companies rates they are a bargain. The hotel in Orlando threw in free WiFi which was great. A few days into the holiday I had a few issues trying to connect to the hotel’s wireless provider  and after some troubleshooting with the aid of the helpdesk I got connected.  I did have to uninstall my UK mobile internet provider software to be able to connect, which at the time I thought nothing about as I had all the software safely tucked away at home. Until I return home and try reinstall and the software and I get …