SQL Server 2005 - Rebuild System Databases

To rebuild, locate SQL media and go to the command prompt and run setup.exe.  Remember to pass the following switches.

start /wait setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

/qn will suppress any error/information messages and send them to the error log.

The repercussion of a rebuild is you must reinstall service packs, if any, all your user-defined databases, including the Reporting Services support db.  Logins or server configurations will also have to be recreated.

After the system databases are rebuilt, you are returned to your default configuration and databases.  It is best to recover the master database, if you have a backup and your logins and databases will automatically appear.  If you don't have a backup of the master database, you will have to reattach each user-defined database and recreate the logins.

Note: Extract from Professional SQL Server 2005 Administration

So what is the difference between the SQL 2005 configuration tools?

The two tools discussed here are:
1. SQL Server Configuration Manager
2. SQL Server Surface Area Configuration

SQL Server Configuration Manager
This configures the SQL services. It is used to configure SQL Services, Network Configuration, Protocols for SQL and Native Client Configuration.

SQL Server Surface Area Configuration
There are many new features added in SQL 2005 that are not necessary used by all organizations, for example CLR integration feature or Database Mail. Microsoft decided that to keep the system secure and more usable, these features are not turned on by default. The SQL Server Surface Area Configuration (SQL SAC) is the tool to use to turn on the features turned off by default.

SQL Server 2005 for experienced SQL Server user. What do you want to know?

This is an extract from the Microsoft SQL Server 2005 web site on SQL Server 2005 Management Studio Solutions (SSMS).  It is a very useful overview for experienced user who is intending to upgrade to SQL 2005.

Automatic Refresh
- is now available

Backwards Compatibility
- SSMS will support 2000 and 2005 databases
- SSMS will not support SQL 7 databases
- You can have SQL 7/2000 tools and SSMS installed on the same machine
- SSMS will not be available as a separate product so taht it can be installed on SQL 2000 deployments

Data Tuning Advisor
- will not support cross server joins, only cross databases

Diagrams
- preserved when upgrading

DMO
- will exist to ensure that older applications will still be supported

Enterprise Manager
- SSMS display databases in the left pane in enumerated format.  A summary page will display in the right pane.  Filtering is available

Express Manager
- available in SQL Express.  You can use Express Manager for running queries.  EM can be used by developers to run queries without having to provide them with the SSMS tool.
Note: SSMS is not available in SQL Express.  However, you can manage SQL Express thorugh SSMS

Installation
- You can install SQL 2005 side-by-side with SQL 2000
- You can retain .NET Framework 1.1
- All 32-bit tools will be available with 64-bit.
- You can save SSMS configurations via export for new installation

ISQL and OSQL
- Will still be supported (albeit deprecated), but SQLCMD is recommended for faster processing and enhanced options.

Logs
- Management plans will allow you to clean up and truncate logs easier
- There will not be a built in utility to read the data in transaction logs

Mail
- SQL Mail (MAPI based) is still available but has been deprecated.
- Database Mail (formerly SQLiMail) is based on SMTP is much improved in stability/features. 
- However Database Mail does not read mail (required SQLMail, Collaborative Data Objects (CDO) or WebDAV).

Maintenance Plans
- You can edit maintenance plans as a SSIS (formerly DTS) package and add additional steps as necessary.

Migration
- is handled at setup time.
- You can detach data/log files from SQL 2000 and attach them in SQL 2005.
- However, you cannot revert SQL 2005 data/log files to sQL 2000.

MMC
- Management Studio does nto rely on MMC.
- However, SQL Computer Manager does

Multiselect
- There is no multiselect available in Object Explorer.

Owner vs Schema
- Owners own objects.  Schemas contain the object.

Profiler
- For Analysis Services, Profiler will allow you to capture SQL batches, the starting and completing of stored procedures, and the scanning of tables.

Schema
- Views are available in sys schema objects for accessing schema information
- There are many new sys schema objects.  Sysdatabases can be viewed through backward compatibility.

Scripting
- You can script to clipboard, query window or file

Scripting out Data
- You can use SSIS (formerly DTS) in the BI Development Studio to create scripts for importing and exporting data

SSMS Windowing
- There is no multi-monitor support
- Windowing is configurable

Stored Procedures
- When performance tuning stored procedures, you can use Query Editor to allow you to view execution plan.

Synchronizing
- You cannot synchronize between different databases.
- Scripting, however, is an option (script to query and then run query)

Templates
- are similar to SQL 2000'S Query Analyzer.  They are stored on the client machine.

Tracefile
- You can read trace files with the Server Management Objects (SMO) API called Trace Objects (SQL Server is not required)

Upwards Compatibility
- Registration groups will convert to SQL 2005
- Enterprise Manager 2000 will not be able to access SQL 2005 Server databases

Visual Studio
- SSMS does not require the VS Development environment
- SSMS is a specialized version of the VS 2005 Interactive Development Environment (IDE)
- SSMS VS 2005 IDE supports T-SQL, MDX, MX and XMLA.  You cannot compile VB.NET, C#, J# or VC++
- Solutions in SSMS will allow you to organize script and logically group connections
- You will need VS 2005 to debug stored procedures and step through code.

XML
- is natively supported.

SQL Replication - subscription(s) have been marked inactive and must be reinitialized

I get this common problem with Transactional replication sometimes:

Error Message : The subscription(s) have been marked
inactive and must be reinitialized. NoSync subscriptions
will need to be dropped and recreated.

I looked around for a solution where I can update the distribution entity that stores the subscription details and this is what I found:

Updating the status in MSsubscriptions table in the distribution database will resolve the problem.
Status is changed to 0 for an expired subscription.

Subscription status:
0 = Inactive
1 = Subscribed
2 = Active

update MSsubscriptions set status=2 where publisher_id='value'
and publisher_db='value' and publication_id='value' and subscriber_id='value' and subscriber_db='value'

* Replace 'value' with the value in your MSsubscriptions.

Microsoft to release .NET Framework source code.

The .NET Framework source code will be included in the VS 2008 release. There are conditions with the release of the source code. The source code will be released under the Microsoft Reference License which means developers are allowed to look at the source code but are not allowed to redistribute it or use it in their own code.....

 

 

CHOOSING A NETWORK PROTOCOL IN SQL SERVER

Brief overview

 

TCP/IP - this is the most widely used.  Default port for SQL is 1433.

Named Pipes - this a protocol developed for LAN.

VIA - Virtual Interface Adapter (VIA) protocol works with VIA hardware.

If you have a fast network, there is no significant difference in performance whether you use TCP/IP or Named Pipes.  For slower networks, e.g
WAN or dial-up, it is advised to stick with TCP/IP.  Named Pipes requires more overhead and is not suitable for slow networks.

 

Technology Digg widget

Issue with display date caused by locale setting in aspx

Recently I was assigned the task of moving web app. written for US locale to a UK locale. Most of you will know that when you run your aspx script in US locale with give you the date format as mm/dd/yyyy and running it in UK locale with give you a different date format dd/mm/yyyy. I discovered this short subroutine that will change the locale to your choice.

<%
ReturnDateTime(1033, "English (US)")
ReturnDateTime(2057, "English (UK)")
ReturnDateTime(3081, "English (Australia)")
ReturnDateTime(1031, "German")

Sub ReturnDateTime(locale, description)
Session.LCID = locale
End Sub
%>


Example:

if you want to set your script to display in US locale, call the sub by simple adding

ReturnDateTime(1033, "English (US)")

To display in UK locale, simple add to your script

ReturnDateTime(2057, "English (UK)")

Undocumented function to convert binary to string in SQL Server

The function is located in the master db. The function is fn_sqlvarbasetostr

Example on how to use it :

select master.dbo.fn_sqlvarbasetostr(a.data) from _binary_object a where a.id = 'C7243386-5E41-11D5-B346-009027B6BA27'