Tablediff is a really handy tool which can be used to fix data sync issues. It is located in the 90\COM directory. If you are ever in a situation whereby replica databases are out of sync then tablediff can save your life. It will compare two tables and generate and output, which includes the T-SQL to fix the data, really handy for avoiding a re-initialisation of the data.
Take a look at the info here: http://msdn.microsoft.com/en-us/library/ms162843.aspx
It’s also listed as one of the top 10 hidden gems in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/gems-top-10.mspx
The Microsoft Certified Master (MCM) program was announced this week at Tech-Ed in the US. The public URL is now live and all the information can be viewed here http://www.microsoft.com/learning/mcp/master/default.mspx
This is a fairly interesting add-in for Visio which eases the process of creating SQL Server diagrams http://visiotoolbox.com/downloads.aspx?resourceid=2&aid=68. I’ve had a quick play and it looks useful, and there is even support for SQL Server 2008
..on MSDN and Technet subscriber downloads. I’m downloading it now so will provide feedback here soon.
Microsoft UK are looking for someone who is passionate about SQL Server to join the consultancy practice. Take a look at the job spec and apply online here http://members.microsoft.com/careers/international/default.aspx?loc=UKD&lang=EN&job=90302479&newapp=0
I find SSMS to be much more flexible that SQL Server 7.0/2000 Enterprise Manager (SEM) however there are some really basic UI features which should be fixed. The display of data types, in particular LOB such as varchar(max) is not consistent through the UI. For example, in the object explorer I can see varchar(max) in the column hierarchy, sp_columns will represent this as TEXT, sp_help <table> displays varchar and finally the publication wizard also displays the type as varchar. This is particularly frustrating when a DBA needs to de-select LOB data types from the article and the data type is not displayed correctly.
This http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx is the first public announcement I have seen which confirms service pack 3 for SQL Server 2005 is on it's way. There is also some really good information appearing on the PSS blog http://blogs.msdn.com/psssql/archive/2008/04/25/sql-server-2005-sp3-and-self-service-hotfixes.aspx about the SP3 build and CU hotfixes.
One of the more frequent requests I hear is for a slipstreamed build (downloadable image or DVD) which includes the service pack, or at least the ability to apply an integrated build. Hopefully this may happen sooner rather than later.
Looks interesting http://www.microsoft.com/sql/dataservices/default.mspx - I'll have to take a closer look. The beta (Connect) sign up page is also available on the site.
Its nice to see that transactional replication in SQL Server 2008 will support the use of the SWITCH partition statement. Unfortunately it looks like the ALTER PARTITION statements will not make it so this will have to be performed manually on the subscriber.
It's a good read and highlights the impact of the CHECKPOINT duration on database mirroring. The paper also compares the use of HP array based technologies.
High availability for Microsoft SQL Server 2005 using array-based replication and host-based mirroring technologies http://h71028.www7.hp.com/enterprise/cache/581666-0-0-0-121.html
I've just had the opportunity of listening to a program manager talk about Hyper-V in Windows Server 2008. I must admit that it looks really impressive, particularly the support for up to 4 cores and the snapshot facility. I can see where I could use this for testing SQL Server builds/installations and troubleshooting performance issues. All I need now is a quad core proc, 8gb RAM, 1 TB storage and a copy of Windows Server 2008 Enterprise Edition!
Just noticed an email in my inbox stating that Windows Server 2008 RTM is available for download on the Connect website, however product keys are not being provided. I'll have to download it later today/tomorrow and have a play with the new features. I'll report back here with my thoughts, particularly the Katmai install experience.
Just noticed a new KB article describing the RML utilities for SQL Server 2000 and 2005 http://support.microsoft.com/kb/944837 which supersedes http://support.microsoft.com/kb/887057. I haven't had a chance to play with this tool yet but it looks pretty good. I particularly like this feature:
"After you capture a trace for an instance of SQL Server, you can use the RML Utilities for SQL Server to replay the trace file against another instance of SQL Server. If you also capture the trace during the replay, you can use the RML Utilities for SQL Server to compare the new trace file to the original trace file. You can use this technique to test how SQL Server behaves after you apply changes"
I have recently been looking at using Oracle subscribers in a SQL Server 2005 transactional replication topology. The main gotcha's I have identified are that the distributor can no longer co-exist on the subscriber (as a remote distributor to offload the log reader agent processing) and propagated schema changes will cause the subscription to be reinitialised. These are fairly important factors, particularly when we are talking about OLTP and terabytes of data. There are a bunch of other details listed in BOL under non-SQL Server subscribers http://technet.microsoft.com/en-us/library/ms151864.aspx which you should be aware of.
The good news? Not much I'm afraid, apart from the fact that the Oracle 10g R2 provider supports x64 and IA64.
I have been wondering if there are going to be any certification (MCP) changes when Katmai ships next year. Fortunately it looks like a similar structure i.e. TS, MCITP levels. There is a good blog entry on this here http://blogs.msdn.com/gerryo/archive/2007/12/11/sql-server-2008-and-certifications.aspx. Hopefully the beta MCP exams will start soon.
There are two certification webcasts scheduled but these won't be happening until February 2008.
I have just gone through the process of having to build a stable Vista x64 OS on my new Lenovo T61p laptop - which I must say is very impressive. Apart from the issues with Groove not completely supporting 64-bit (arghh), I installed SQL Server 2005 Developer Edition (SP2 + hotfix package) and then began thinking about what tools/utilities I use during my day-to-day job and which are a must-have on my laptop. Below is the list of software which I generally install straight away (I'm sure there other useful tools out there):
SQL Server 2000 Books Online (June 2007) http://www.microsoft.com/downloads/details.aspx?FamilyID=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=en - essential documentation for those disconnected working environments.
SQL Server 2005 Books Online (September 2007) http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en - keep up-to-date with the latest 2005 documentation
SQL Server 2005 Express Edition (for testing different scenarios/features e.g. replication subscription) http://msdn2.microsoft.com/en-gb/express/bb410792.aspx
SQL Server 2005 Upgrade Advisor http://www.microsoft.com/downloads/details.aspx?familyid=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en - handy when I need to demo or investigate upgrade issues.
SQL Server 2005 Performance Dashboard Reports http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en - Customers love this software as it exposes a lot of the DMV info in a nice graphical format.
SQL Server 2005 System Views Map http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&DisplayLang=en - not quite as good as the SQL Server 2000 chm system table browser in the resource kit but still useful.
SQL Server 2005 Samples and Sample Databases http://www.codeplex.com/SqlServerSamples
SQL Server DMV Stats v1.01 http://www.codeplex.com/sqldmvstats
Read80Trace http://support.microsoft.com/kb/887057 - according to the PSS guys http://blogs.msdn.com/psssql/archive/2007/11/21/rml-readtrace-ostress-utilities-for-sql-server-2005.aspx a new version should be out soon which will support SQL Server 2005.
PSSDiag http://support.microsoft.com/kb/830232 - useful diagnostic tool which is also
Microsoft Premier Services Reporting Utility (PFE version) ttp://www.microsoft.com/downloads/details.aspx?familyid=00ad0eac-720f-4441-9ef6-ea9f657b5c2f&displaylang=en (I use, and recommend, this version over the others as it supports more hardware platforms e.g. IA64. Read more about MPS reports here http://support.microsoft.com/kb/818742
Visual Studio 2008 (which includes Team Database Edition). Read more here: http://blogs.msdn.com/gertd/archive/2007/11/21/visual-studio-team-system-2008-database-edition.aspx
MSDN Library (April 2007 Edition) – Includes Visual Studio 2005 Service Pack 1 documentation http://www.microsoft.com/downloads/details.aspx?FamilyID=b8704100-0127-4d88-9b5d-896b9b388313&DisplayLang=en
Virtual PC 2007 (must-have for testing legacy and future SQL Server releases) http://www.microsoft.com/downloads/details.aspx?FamilyId=04D26402-3199-48A3-AFA2-2DC0B40A73B6&displaylang=en
Data Mining add-ins for Office 2007 http://www.microsoft.com/downloads/details.aspx?FamilyId=7c76e8df-8674-4c3b-a99b-55b17f3c4c51&DisplayLang=en (also available as part of the feature pack http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en)
3rd Party Software
Redgate sqlprompt - until Katmai is released which has native intellisense :-) http://www.red-gate.com/products/SQL_Prompt/index.htm
Redgate Refactor - which is great for automatically indenting my T-SQL logic http://www.red-gate.com/products/SQL_Refactor/index.htm
Beyond Compare (for quick script/schema comparisons) by Scooter Software http://www.scootersoftware.com/
Erwin Data Modeler (invaluable ER modeler, great tool) http://www.ca.com/us/products/product.aspx?id=260
Litespeed http://www.quest.com/litespeed-for-sql-server/ - I need to be familiar with Litespeed as a few customers use it for VLDB backups.
..and how could I forget Oracle 10/11g http://www.oracle.com/technology/software/products/database/index.html for testing connectivity, SSIS ETL processes etc. for all those migration projects :-)
Please let me know if you recommend any other useful SQL tools and I'll add them to the list.
..should be appearing very soon for download. I can see it in Windows Update so it will hopefully be appearing here http://technet.microsoft.com/en-gb/sqlserver/bb428874.aspx shortly.
...ends on April 8th 2008 so I would recommend upgrading to service pack 2 (and the latest cumulative hotfix package) as soon as possible. SQL Server service pack support is documented on the lifecycle website here http://support.microsoft.com/gp/lifesupsps#Servers
I'm currently working with a customer who had a requirement to backup the mirror database. You may wonder why they want to do this - there are a number of reasons which I won't go into right now. The simple answer is that it is not possible to backup the mirror database as it is in a recovering state. It is not even possible to use any SAN-based solution to take a clone/snap due to the same reasons. What I mean by this is that whilst it may be possible, it will not be supported and the database consistency cannot be guaranteed.
Maybe a feature request for Katmai+1?
I haven't blogged for quite a while now... and my excuse? - there is not enough time in the day! One thing that I have managed to do is schedule a trip to Manchester this Thursday (29th November) to talk to anyone interested about SQL Server 2008 https://msevents.microsoft.com/cui/EventDetail.aspx?culture=en-GB&eventid=1032355918. It should be good as I haven't been to a Technet event for quite a while.
Just noticed that the a new cumulative update for SQL Server 2005 service pack 2 has been released. The KB is here http://support.microsoft.com/kb/943656. The incremental servicing model has also been clearly defined in the following knowledge base article http://support.microsoft.com/kb/935897
The next update package for SQL Server 2005 SP2 has just been released, info available here http://support.microsoft.com/kb/941450