Archive for January, 2008

Sqlserver 2000 Installation Steps

Posted: January 4, 2008 in Sqlserver

    ÿ
    Establish whether this request truly needs a dedicated server, or if it can just reside on one of the shared servers. If a dedicated server is required, continue.

    ÿ Review DBA policies with the person making the request.

      ÿ Database Support – General.doc
      ÿ DBA Team SQL Server Support.doc

    ÿ Ensure licenses are in place. Establish that the project has purchased the appropriate licenses for the edition and number of users for the database. Clear this with Asset Management (TBD).

    ÿ The requestor must supply a server. Assist them in determining adequate resources—memory, CPU, and disk. Advise them to only install SQL Server, no other applications.

    ÿ Install SQL Server software. If this is a remote installation, work with Siemens to copy the software CDs to the server.

    ÿ During Installation, when you select the type of Installation, Change the default drive values for the data files, which should be other than the “Bin” Folder, which is always in “c:” drive.

          ÿ C:\Program Files\Microsoft SQL Server\MSSQL\Bin
          ÿ D:\Program Files\Microsoft SQL Server\MSSQL\Data

    ÿ Licensing Per Seat should be “100
    ÿ Best Practices:

      ÿ For all servers:

        ÿ Immediately patch the server to the latest SQL Server patch.
        ÿ Add “Domain\SQL Admins” to “System Administrators”
        Create a login “
        Domain\SQL Admins” or Whatever login you use
        ÿ In the SQL Enterprise Manager, Right Click on the Server and go to Properties
        ÿ In Properties tab, Set Autostart “SQL Server Agent”, “SQL Server” and “MSDTC”
        ÿ In Memory tab, Under “Dynamically configure SQL Server Memory”, check and see to that the “Maximum(MB)” should be less than the total Physical Memory of the System.

        ÿ Under Security tab, Set “Start up Service account” should be, “This account” “Domain Name\Username (“Domain\sqlexec”)

        ÿ Under Security tab, Set “Audit Level” to “All”
        ÿ Under “Database Settings” Setup default drive values for data and transaction log files.

          ÿ D:\Program Files\Microsoft SQL Server\MSSQL\Data
          ÿ D:\Program Files\Microsoft SQL Server\MSSQL\Transaction Logs
          or
          (Incase of three drives)
          ÿ E:\Program Files\Microsoft SQL Server\MSSQL\Transaction Logs

        ÿ Now expand “Management” Folder, Right click on the “SQL Server Agent”,

          § In the “General” tab, “Mail Session”, select the existing Profile (you get the profile only if the Outlook express is already configured in the system)

          § To configure the OUTLOOK (Mail Host: EmailServerName, Mail Box: SQLExec)
          § In the “Advanced” tab, Under “Restart Services”,

            § Set “Auto restart SQL Server if it stops unexpectedly” on
            § Set “Auto restart SQL Server Agent if it stops unexpectedly” on

        ÿ Setup Three database maintenance plans

          ÿ Plan 1 – ALL USER DATABASES backup

            ÿ All user databases
            ÿ Reorganize data and index pages
            ÿ Remove unused space from database files
            ÿ Shrink database when it grows beyond 50 mb
            ÿ Check database integrity
            ÿ Include indexes
            ÿ Attempt to repair any minor problems
            ÿ Backup the database as part of the maintenance plan
            ÿ To Disk
            ÿ Use the default directory
            ÿ Remove files older than 4 weeks
            ÿ Backup file extension “BAK”
            ÿ Write report a text file in directory (default directory)
            ÿ Delete text report files older than 4 weeks

          ÿ Plan 2 – ALL SYSTEM DATABASE backup

            ÿ All system databases
            ÿ Reorganize data and index pages
            ÿ Remove unused space from database files
            ÿ Shrink database when it grows beyond 50 mb
            ÿ Check database integrity
            ÿ Include indexes
            ÿ Attempt to repair any minor problems
            ÿ Backup the database as part of the maintenance plan
            ÿ To Disk
            ÿ Use the default directory
            ÿ Remove files older than 4 weeks
            ÿ Backup file extension “BAK”
            ÿ Write report a text file in directory (default directory)
            ÿ Delete text report files older than 4 weeks

          ÿ Plan 3 – ALL TRANSACTION LOG BACKUP HAVING RECOVERY MODEL as Full

            ÿ Select databases having recovery model full
            ÿ Remove unused space from database files
            ÿ Shrink database when it grows beyond 50 mb
            ÿ Backup the transaction log of the database as part of the maintenance plan
            ÿ To Disk
            ÿ Use the default backup directory
            ÿ Remove files older than 4 weeks
            ÿ Backup file extension “TRN”
            ÿ Write report a text file in directory (default directory)
            ÿ Delete text report files older than 4 weeks

      ÿ For DBA Team owned servers:

        ÿ Remove “builtin\administrators” from “System Administrators”

          ÿ Expand the “Security” tab, click on the “Server Roles“ and then right click on the “sysadmin” -> select “Properties” and then check in the Role members, if “builtin\administrators” exist, if yes, then remove it.

        ÿ Set “Start and run SQL Server” to “na\sqlexec”.
        ÿ Setup alert system and configure the following alerts
        Optional: Include the ”TRN log Maintenance Plan” in the alert notification list.

            · Full MSDB Log – “Error – 9002”
            · Full TempDB – “Error – 9002”
            · “Server name” – Sev19 Errors
            · “Server name” – Full Log – “Error – 9002”
            · “Server name” – Insufficient Resources

        ÿ Right click on the “Operator” folder to create a New Operator

      ÿ For requestor owned servers:

        ÿ Set “Start and run SQL Server” to either system account, or account specified by requestor
        ÿ Set “Service startup account” to either system account, or account specified by requestor
        ÿ If the requestor wants alerts

          ÿ Get Outlook client installed
          ÿ (Details TBD)

Thanks
Md. S.Hassan
Sqlserver Database Administrator

Advertisements

Sqlserver 2008 Installation Steps

Posted: January 4, 2008 in Sqlserver

Introduction
This article will highlight the steps involved to install SQL Server 2008. The installation is simple and straightforward. Trial and preview versions can be downloaded from Microsoft at http://www.microsoft.com/sql/2008/prodinfo/download.mspx . If you’re new to SQL Server, then deciding which additional components to install will require a small amount of research. There are several different versions of SQL Server including Enterprise, Standard, Workgroup, Developer, and Express. In addition, there are 32 and 64-bit offerings. The following Database Journal article examines the new features and benefits of SQL 2008: http://www.databasejournal.com/features/mssql/article.php/3691821.

Installation
If your SQL Server media came as one exe, double click it, and SQL will ask for a temporary unzip location. After the unzip, a message box will popup saying Extraction Complete. Navigate to the unzip folder, and find “setup.exe”. Double click it to begin the install. Accept the terms and conditions. The next step will install any required prerequisites such as Dot Net and SQL Support files. After the prerequisites are installed, a system check will run. All the actions should come back green.

IIS (Internet Information Server) is required to be installed on the OS if SQL Reporting Services will be installed. If the System Check discovers an error, the details can be viewed by either clicking a hot link on the error, or pressing the Report button on the bottom. Continuing will bring up the registration screen.

Components to Install
This next Screen, Components to Install, determines which features and applications will be installed.

The first option, “SQL Server Database Services”, is the SQL engine. By default, all available sub items are selected with it. The sub items are visible by clicking the Advanced button at the bottom of the screen. The sub items included with the engine are

    · Replication – Objects used for copying items from one database to another.
    · Full Text Search – The optional engine used for text searches.
    · Database Files – Creates data folders on the file system.
    · Shared Tools

So selecting “SQL Server Database Services” installs the engine, but no management tools or documentation. For these applications, select “Workstation components”. This option installs the items commonly associated with SQL Server, such as the Management Studio and Books On Line (BOL). The following components are also installed:

    · Network Libraries such as ODBC and OLE DB.
    · Management Tools including SQL Server Management Studio, Configuration Manager, Profiler, and Replication Monitor.

  • · BIDS (Business Intelligence Development Studio), used for Reporting Services to create reports and designs.
    · The Software Development Kit (SDK).
    · XML tools.
    · Legacy DTS and DMO.
    · Sample Applications.
  • SQL Server Books On Line

The sample database, Adventure Works, is missing from the list. To have it installed, click the Advanced button then select it from the Feature Select list.

Analysis Services
The next optional group of components to select is Analysis Services. SQL Server Analysis Services enables the creation of Business Intelligence objects such as Data Mining and OLAP (Online Analytical Processing). In simple terms, if we think of a typical SQL database as being created to store transactions of some activity, then Analysis Service is a specialized database designed to report on that activity.

By checking the Analysis Service, all available tools and items are included with it. They are visible by clicking the Advanced button. There are only two itmes, Data Files and Shared Tools.

Reporting Services
SQL Server Reporting Services enable the creation of web-based reports that have the look and feel of traditional fat client reports. If you’re familiar with Crystal Reports or MS Access, or some other “banded” reporting tool, then you have the idea. The reports created can be centrally stored and managed in SQL Server. If you are a Visual Studio developer, there is ReportViewer control that allows these types of reports to be created without SQL Server involved, but without SQL, there isn’t central management.

When the Reporting Service is selected from the Feature Selection screen, all available Reporting Service sub items are checked also. These include the Report Engine and Shared Tools.

Integration Services
Integration Services are used to create “packages” that perform workflow and ETL (extraction, transformation, and load) tasks. SSIS (SQL Server Integration Services) includes graphical wizards and tools for creating these objects. SSIS is the replacement of SQL 7 DTS (Data Transformation Services).

Setup
Clicking next from the “Components to Install” screen takes us to the “Instance Name’ screen. If the default of “Default Instance” is kept, SQL Server will have the same name as the machine it’s being installed on. To use a different name, select “Named Instance”.

Clicking Next will bring up the “Service Account” screen. Here we can specify the accounts SQL will use to run. Selecting “Use the built in System account” will run all the SQL services under a local machine account.

Continuing brings up the “Authentication Mode” screen. By default, Windows logins only are used. If you wish to use SQL logins in addition to Windows logins, select Mixed Mode and supply a password for SA. SA is the built in system administrator SQL account. You may need SQL logins for third party database applications. Click Next.

This next screen, Collation Settings, allows the collation and sort order to be set. Unless there are specific circumstances, keep the default of Dictionary order, case insensitive.

If Report Services was selected as a feature to be installed, a configuration screen will appear next. Click next to keep the default of configuring SSRS, or select “Install but don’t configure” if custom SSRS setup is required.

On the Error Usage screen, keep the defaults if you permit Microsoft to gather usage statistics to help future development.

At the end of the installation, SQL will report the status of setup. If there were any errors, they’ll be displayed here. Additional details can be found in the logs under the MSSQL/LOG directory.

Conclusion
The installation of SQL Server 2008 is straightforward and simple. If you’ve installed SQL 2005, then the 2008 installation will be very familiar. There are many new features and improvements included with SQL 2008. This Database Journal article examines these in detail: http://www.databasejournal.com/features/mssql/article.php/3691821.

Script for Alter Trace Permission

Posted: January 4, 2008 in Sqlserver

Use Master
GRANT Alter Trace TO sqluser
Grant Alter Trace To “Domain\UserName”

Change the ‘SA’ password without having old password

Use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:
————————————————————————————————

EXEC sp_password NULL, ‘newpassword’, ‘username’
GO

Stored procedure sp_ToExecuteAll SSIS packages Remotely

Posted: January 4, 2008 in Uncategorized

————-Configure proxy account———
sp_xp_cmdshell_proxy_account [ NULL { ‘account_name’ , ‘password’ } ]
——–To Enable xp_cmdshell sp——–
sp_configure ‘show advanced options’, 1GORECONFIGUREGOsp_configure ‘xp_cmdshell’, 1GORECONFIGUREGO

—————————————————-
Create procedure [dbo].[sp_ProcessAllFilesInDir]
@FilePath varchar(1000) ,
@FileMask varchar(100) ,
@ProcSp varchar(128)
as set nocount
on
declare @File varchar(128) ,
@MaxFile varchar(128) ,
@cmd varchar(2000)

create table #Dir (s varchar(8000))
select @cmd = ‘dir /B ‘ + @FilePath + @FileMask insert #Dir exec master..xp_cmdshell @cmd
delete #Dir where s is null or s like ‘%not found%’
select @File = ”, @MaxFile = max(s) from #Dir while @File @File select @cmd = @ProcSp + ‘ ”’ + @FilePath + ”’ , ”’ + @File + ”” exec (@cmd) end
drop table #Dirgo
Create procedure [dbo].[sp_ExecutePackage]
@FilePath varchar(1000) ,@Filename varchar(128)asDeclare @cmd varchar(1000) select @cmd = ‘dtexec /F “‘ + @FilePath + @Filename + ‘”‘ exec master..xp_cmdshell @cmdgo

exec sp_ProcessAllFilesInDir ‘D:\’,’package1.dtsx’,’sp_ExecutePackage’
or
Exec [dbo].[s_ProcessAllFilesInDir] @FilePath = ‘d:\TestPackages\’ , @FileMask = ‘t1*.dtsx’ , @ProcSp = ‘[dbo].[s_ExecutePackage]’

Script to find orpahend users

Posted: January 4, 2008 in Uncategorized

—–For Sql users—-Run the below query on Source Server’s..

Use Master
SET NOCOUNT ON
SELECT ‘EXEC sp_addlogin @loginame = ”’ + loginname + ””
,’, @defdb = ”’ + dbname + ””
,’, @deflanguage = ”’ + language + ””
,’, @encryptopt = ”skip_encryption”’
,’, @passwd =’
, cast(password AS varbinary(256))
,’, @sid =’
, sid
FROM syslogins where loginname Not like ‘NA%’ and loginname not like ‘Builtin%’ and loginname Not like ‘sa’

— For Windows Users—-
Use Master

SELECT ‘EXEC sp_grantlogin @loginame = ”’ + loginname + ””
,’ EXEC sp_defaultdb @loginame = ”’ + loginname + ””
,’, @defdb = ”’ + dbname + ””
FROM syslogins
WHERE loginname NOT IN (‘BUILTIN\Administrators’)
AND isntname = 1

—Copy the Results and Execute on Destination server’s where you have copied or migrated the databases

USE master
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘sp_show_huge_tables’ AND type = ‘P’)
DROP PROC sp_show_huge_tablesGO
CREATE PROC sp_show_huge_tables
(@top int = NULL,
@include_system_tables bit = 0)
AS

Script to Find Recovery Model of all Databases

Posted: January 4, 2008 in Uncategorized

Use master

SELECT name, DATABASEPROPERTYEX(name, ‘Recovery’) AS Expr1, DATABASEPROPERTYEX(name, ‘Status’) AS Expr2 FROM sysdatabases WHERE (DATABASEPROPERTYEX(name, ‘Recovery’) = ‘full’)ORDER BY name