Cool MS SQL Server Tools

October 15, 2004

Sqldiag – Sqldiag is a utility used for report generation and collection of diagnostic information on database server / operating system configuration parameters. Sqldiag gathers the information, even if Microsoft SQL Server 2000 services are stopped. The report generated by Sqldiag contains the following information:
Complete dump of all SQL Server error logs; Registry information, related to SQL Server; SQL Server system dll versions; Output, generated by: sp_configure, sp_who, sp_lock, sp_helpdb, xp_msver and sp_helpextendedproc; Information about all system processes (master..sysprocesses); Information about all user processes and connections (including Input buffer SPIDs and Dead locks); Information about operating system parameters (including reports about: OS version, Video display, Drivers, DMA, Memory, Services, IRQ and ports, Devices, Environment and Network); Info about the last 100 user’s queries. The Sqldiag utility is installed to the \Program Files\Microsoft SQL Server\MSSQL\Binn directory by default.

Profiler – Profiler is the executable for SQL Server Profiler. SQL Server Profiler is typically used for monitoring SQL Server events, such as debugging T-SQL statements and stored procedures and troubleshooting problems (by capturing them in a real-time and replaying later).

Sqlmaint – Sqlmaint is a maintenance utility. Sqlmaint performs a set of tasks, specified by the DBA, on one or more databases (for example backup databases, update statistics, rebuild indexes, DBCC check).
The Sqlmaint utility is installed to the \Program Files\Microsoft SQL Server\MSSQL\Binn directory by default

bcp – A utility used for the interactive process of bulk coping of data between the SQL Server 2000 instance and data file (format information file should be specified or a default bcp.fmt should be used instead). The bcp utility is the typical example of a “two-way” tool, i.e. copying data “into SQL Server instance” or “out of SQL Server instance” is allowed. Alternatively, bcp can be used for copying data:
Between SQL Server instances with different language collations; To or from a view; Returned from a T-SQL query (to data file); Between the Microsoft SQL Server and database servers of other vendors; Between the SQL Servers working on different processor architectures; To or from a database table (including temporary tables); Between databases within one SQL Server instance. The bcp utility is installed by default to the \Program Files\Microsoft SQL Server\80\Tools\Binn directory

itwiz – itwiz allows the Index Tuning Wizard to be executed from a command prompt. Index tuning using an itwiz is similar to tuning via Index Tuning Wizard with a user interface. The itwiz utility is installed to the \Program Files\Microsoft SQLServer\80\Tools\Binn directory by default.

osql – A utility for interactive Transact-SQL scripts and stored procedures execution. It uses ODBC libraries for communicating with the database server. Osql can be started directly from the operating system command prompt and uses a standard output device (monitor, by default) for displaying results. The osql utility is installed to the \Program Files\Microsoft SQLServer\80\Tools\Binn directory by default.

Simple Enabling/Disabling Constraints/Triggers on the entire SQL 2000 Database
sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? CHECK CONSTRAINT all”sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? ENABLE TRIGGER all”

Advertisements

Simple SQL Server/MSDE Database Installation through osql

October 13, 2004

Here, a MSDE database is dropped, attached, and a user is given rights on the db
With minor changes to the osql parameters a Server name can be provided and this script will work for an SQL 2000 database.

–drop old db
osql -E -S -Q “DROP DATABASE [dbname]”

–copy the mdf to the target loc
copy “c:\installtemp\dbname*.?df” “C:\program Files\Microsoft SQL Server\MSSQL\Data”

–make sure the db file is not read only
attrib -r “C:\program Files\Microsoft SQL Server\MSSQL\Data\dbname*.?df”

–attach the db to the target instance/server
osql -E -S -Q “EXEC sp_attach_db @dbname = ‘dbname’, @filename1 = N’C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname_Log.LDF’, @filename2 = N’C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname_Data.MDF'”

use dbname
–add a user to access this db apart from default db admin user
EXEC sp_grantdbaccess “domain\user”, “domain\user”
GO

–grant read access to this user
exec sp_addrolemember N’db_datareader’, “domain\user”
GO

–grant write access to this user
exec sp_addrolemember N’db_datawriter’, “domain\user”
GO

–security script to make sure sps and fns have exec priv.
osql -E -S -Q -i “c:\installtemp\dbnameSecuritySetup.sql”

–Security script is below — dbnameSecuritySetup.sql
–recurively grant exec priv to all sps and fns in db.
USE dbname
DECLARE @sExecQry sysname
DECLARE EXEC_SPS CURSOR LOCAL FOR
select ‘grant exec on ‘ + QUOTENAME(name) + ‘ to “domain\user” ‘ from sysobjects where (type = ‘P’ or type=’FN’) and objectproperty(id,’IsMSShipped’)=0
OPEN EXEC_SPS
FETCH NEXT FROM EXEC_SPS INTO @sExecQry
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@sExecQry)
–PRINT @sExecQry — debug only
FETCH NEXT FROM EXEC_SPS INTO @sExecQry
END
CLOSE EXEC_SPS
GO

–Perform checks on the database
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
GO

DBCC CHECKDB
GO

DBCC CHECKALLOC
GO

DBCC CONCURRENCYVIOLATION
GO

DBCC DROPCLEANBUFFERS
GO

DBCC FREEPROCCACHE
GO

DBCC UPDATEUSAGE(0)
GO


Migrating Oracle Databases to SQL Server 2000

October 8, 2004

SQL Server 2000 only works on Windows-based platforms, including Windows 9x, Windows NT, Windows 2000 and Windows CE.
In comparison with SQL Server 2000, Oracle 9i Database supports all known platforms, including Windows-based platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

Sometimes there is the migration issue, so here goes…

There are some nice articles on Oracle to SQL Server migration at

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx

SQL Server vs Oracle Feature differences
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm

There is a nice tool for the Migration of stored procs/SQL at
http://www.swissql.com/oracle-to-sql-server.html

Latest Top Ten TPC-C by PerformanceVersion 5 Results
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp

Top Ten TPC-C by Price/PerformanceVersion 5 Results
http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp

Oracle vs SQL Server
http://www.dba-oracle.com/oracle_tips_oracle_v_sql_server.htm

Thanks to the authors of these public sites for the relevant information.


Should 4+1Views based Architecture be a standard for High Level Design documents

October 7, 2004

The template and details are at:

http://www.cs.ubc.ca/~gregor/teaching/papers/4+1view-architecture.pdf

“To describe a software architecture, we use a model composed of multiple views or perspectives. In order to eventually address large and challenging architectures, the model we propose is made up of five main views

  • The logical view, which is the object model of the design (when an object-oriented design method isused),
  • the process view, which captures the concurrency, availability, performance and synchronization aspects of the design,
  • the physical view, which describes the mapping(s) of the software onto the hardware and reflects its distributed aspect,
  • the development view, which describes the static organization of the software in its development environment.
  • The description of an architecture—the decisions made—can be organized around these four views, and then illustrated by a few selected use cases, or scenarios which become a fifth view.”

Thanks to the Author – Philippe Kruchten – and IEEE for this invaluable experience paper.


Testing SSL on Win Server 2003/IIS6

October 6, 2004

Hi there,

Been busy with lots of work with .NET Remoting Performance Testing and stuff.

Found something interesting so here goes.

There’s a nice easy way to test your IIS6 – SSL performance, install the free SelfSSL Certificate(SelfSSL Version 1.0) from the IIS Resource Kit (http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&displaylang=en) . Its very easy to use (that’s what we look for right?) . Check out http://www.visualwin.com/SelfSSL/ for detailed directions on how to get your site into https (for testing only).

The following very useful (performance, analysis and deployment) tools are also available in the IIS6 Resource Kit package :

  • IISCertDeploy.vbs Version 1.0
  • Log Parser Version 2.1
  • Metabase Explorer Version 1.6
  • Permissions Verifier Version 1.0
  • Web Capacity Analysis Tool Version 5.2

Thanks to Microsoft and the authors of the http://www.visualwin.com/ site on which there is lots of other interesting info. on Win 2003 and IIS6.