SQL Server - Patching : An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

The SQL Server team is moving away from the current priority-driven hotfix release model to a scheduled delivery model. In the scheduled delivery model, a customer can receive a hotfix to address their most critical situations with a short turn-around time. Additionally, a customer can receive a fix that has undergone more testing and that is released on a schedule basis. Therefore, the SQL Server team has created the following delivery mechanisms.

Critical On Demand - COD	On Demand- OD	Cumulative Update - CU	General Distribution Release - GDR
The hotfix is released on or before a mutually agreed upon date based on the customer's need	The hotfix is released on or before a mutually agreed upon date based on the customer's need	The update is released every 2 months.	A GDR addresses an issue that has a broad customer impact, that has security implications, or that has both. A GDR is determined and issued by Microsoft as appropriate and when appropriate. GDRs are kept to a minimum.
		It contains all previous critical on-demand hotfixes to date.

 

ISM eliminates the need for hotfix rollup releases. The first CU after a new release contains the hotfix rollup, plus any additional QFEs.

A hotfix package does not replace a service pack. A hotfix package is optional. A hotfix package can be installed or uninstalled at any time. Additionally, hotfix packages are cumulative. Therefore, the latest OD hotfix package or CU hotfix package includes all previously released hotfixes.

Quick Fix Engineering - QFEs are used for the majority of fixes where the effects of the problem are not widespread or severe enough to warrant a GDR.

Can we have database on SIMPLE Recovery model for Transactional Replication?

Yes. We can have Publisher database on SIMPLE recovery model.

The reason is that;

• When executing, the Log Reader Agent first reads the publication transaction log and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data transactions that have been marked for replication. Next, the agent batch copies those transactions to the distribution database at the Distributor.
  
• The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
  
• Only committed transactions are sent to the distribution database by LogReader agent.
  
• After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed.
  
• Finally, the agent marks the rows in the transaction log that are ready to be truncated. Rows still waiting to be replicated are not truncated
  
• The transaction log on the Publisher can be dumped without interfering with replication, because only transactions not marked for replication are purged.
  

SQL Server 2005 Cluster: SQL Server Service not coming online, error on TCP port is already in use

 

SQL Server Error Log:

2010-06-04 15:19:37.520 Server Server is listening on [ 10.0.0.136 <ipv4> 2813].

2010-06-04 15:19:37.530 Server Error: 26023, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.

2010-06-04 15:19:37.530 Server Error: 17826, Severity: 18, State: 3.

2010-06-04 15:19:37.530 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2010-06-04 15:19:37.530 Server Error: 17120, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event Viewer Error

Event Type: Error

Event Source: MSSQL$PROD2K5ISD

Event Category: (2)

Event ID: 26023

Date: 4/06/2010

Time: 2:51:31 PM

User: N/A

Computer: PROD2K5ISD

Description:

Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

Resolution

The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid

SQL Server 2005 /2008 Express Edition Backup automation

SQL Database Backup Script

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)   select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') while @IDENT is not null begin     SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT /*Change disk location here as required*/     SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\SQLBACKUP\Backup\'+@DBNAME+'.BAK''WITH INIT' PRINT @SQL     EXEC (@SQL)     select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB','AdventureWorks') end

 

DOS Batch files to backup the databases

@echo off c: if not exist c:\SQLBackup\Logs MD C:\SQLbackup\Logs if not exist c:\SQLBackup\Backup MD C:\SQLbackup\Backup cd c:\SQLBackup Echo Backup Started >> c:\SQLBackup\Logs\SQLBAckup.log Date /T > c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log Echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log isql -S (local)\Express -E -i c:\sqlbackup\backup.sql >> c:\SQLBackup\Logs\SQLBAckup.log echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log echo. Date /T >> c:\SQLBackup\Logs\SQLBAckup.log Time /T >> c:\SQLBackup\Logs\SQLBAckup.log echo Backup Finished >> c:\SQLBackup\Logs\SQLBAckup.log echo. echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log

 

Folders will look like,

 
Schedule it on Windows Task Scheduler

That's it the SQL Server 2005 / 2008 Express Edition database backup is automated now!

SQL Server Lever collation change

1. Backup all databases.(both system and User)

2. If applicable, backup DTS packages, Jobs, SSIS packages, mailing profiles or other objects installed by the application.

3. Script/take screenshot of logins with level of access on server level as well as the database level, user objects for example - tables, functions, types and stored procs, extended stored procs, if any on model or master db.

4. Detach all the user databases.

5. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI


6. Check the tempdb collation and it should be ?Latin1_General_CI_AS? create users with help of screenshot/Script and restore user objects from step 3. Run scripts for functions and types if any.

7. Attach user databases from step 4 / Restore all the databases from the backup from step 1 and all the objects from the backup taken in step1. Refer client mail for further clarifications.

Error message when you open SQL Server Configuration Manager in SQL Server 2008: "Cannot connect to WMI provider. You do not have permission or the se

While opening of SQL Server Configuration Manager if you get error message of

"Cannot connect to WMI provider. You do not have permission or the server is unreachable"
Cause:
This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server 2008. The 32-bit instance and the 64-bit instance of SQL Server 2008 share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

Workaround:
To work around this problem, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Refer the KB# 956013

The SQL Server Service Broker or Database Mirroring transport is disabled or not configured

MOM raised alert of "The SQL Server Service Broker or Database Mirroring transport is disabled or not configured".

The Service Broker is enabled by default in SQL Server 2005. Service Broker is enabled by default when a new database is created. Please try to run the following query to see the Broker option on a database.

SELECT name, is_broker_enabled FROM sys.databases;

This alert is triggered by event 9666 in the Application Event log for the OpsMgr database server.However, this event is always generated when you restart SQL because neither the Service Broker nor Database Mirroring are enabled for the Master or
Model databases. "SELECT is_broker_enabled FROM sys.databases" identifies
that the Service Broker is enabled for all other DBs. The alert is a bit counter productive if the rule is there to try to identify which databases are missing this option that actually need it; e.g. the OpsMgr db.Currently my only option is to disable the rule.

How to Script Database Schema

1. On the database schema to be scripted out, right click
2. Select Tasks --> Generate Scripts
3. Select all the objects in the selected databases
4. Select the appropriate General & Table Options
5. Select the output format

Testing a Connection from Client to SQL Server using UDL

A connection from client to SQL Server can easily be checked with the help of a .udl file. This is also helpful for building a connection string.

Step 1 - Create a .udl file
Create a text file and name it for example test.udl
Double click on test.udl to open the connection dialog.

Step 2 - Choose the type of connection
Go to the first tab “Provider”
Choose “Microsoft OLE DB Provider for SQL Server” or any other provider according to your needs.

Step 3 - Set up connection properties
Go to the second tab “Connection”, choose the server, enter username/password and choose a database from the drop down list.

Step 4 - Connection String
Once you click on “OK” a connection string based on your connection settings is written to test.udl. After you open test.udl you will find a connection string like this.

[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Password=wrf%32g;Persist Security Info=True;User ID=siva;Initial Catalog=msdb;Data Source=SQLTEST

CPU usage reaching 100% with SP3 on SQL Server 2005

Symptom:
You install an instance of Microsoft SQL Server 2005 on a computer that has many processors. You upgrade to SQL Server 2005 Service Pack 3 (SP3) or to Microsoft SQL Server 2008. You run a query that uses the Like operator in the WHERE clause or that uses a string comparison function, such as the CharIndex() function. When you run the query many times on multiple connections at the same time, the CPU usage reaches 100 percent, and the server performance decreases significantly.

Then refer the Microsoft article and apply the cumulative update

No Surface Area Configuration (SAC) Tool in SQL Server 2008

In SQL Server 2008, Surface Area Configuration (SAC) Tool has been added to the deprecated feature list.

So how to do the functionality of SAC configuring Service, Connection and features?

1. For Instance and Database Engine Features:
Select Facets on Properties of Instance / Database on SQL Server Management Studio.

2.For Connections, Protocols and StartUp options:
Use SQL Server Configuration Manager

3.SSAS Features:
It can be done at Property Settings

4.SSRS Features
It can be done by editing the RSReportServer.config file or by using the facets feature

Feature Pack for Microsoft SQL Server 2005 and SQL Server 2008

Please have a look on the Feature pack of SQL Server 2005 and SQL Server 2008.

SQL Server 2005

SQL Server 2008

SQL Agent fails on SQL Server 2005

The error log from starting sqlagent is the following:
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[000] Unable to connect to server '\SQL2005'; SQLServerAgent cannot start
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
[382] Logon to server '\SQL2005' failed (DisableAgentXPs)
[098] SQLServerAgent terminated (normally)

Resolution:
-----------

Client side setting would have messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no".

If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client). Just run the sqlncli.msi

Microsoft SQL Server 2008 advantages over Oracle 11g

SQL Server compared to Oracle

Microsoft SQL Server 2008 advantages over Oracle 11g

* Better Performance and Scalability: SQL Data Services, Filtered Indexes, Advanced Sparse Columns, Column Prefix Compression
* Better Security: Module Signing using Certificate, and integrated updates and patching
* Better Business Intelligence (BI) tools: Optimized cube designers, on-demand processing and instance-based rendering, optimized integration with the 2007 Microsoft Office system

Find more on here

Get up to 25% off Select Microsoft Certification Exams

Get Your Voucher Code for your Discounted Exams

Find the exam of your choosing from the lists below and select "Get Voucher," complete the registration form and your discounted voucher code will be emailed to you. Once you have obtained your code, plan and schedule your next exam at http://www.prometric.com/Microsoft/default.htm.

http://www.prometric.com/microsoft/careeroffer.htm

* Step 1: Using the voucher number, schedule and pay for your initial exam via Prometrics's web site
o You will need to have your voucher number available during the registration process.
* Step 2: Take your exam.

Candidates must register, schedule and take their discounted exams by December 31

How to reset Internel Protocol TCP / IP

One of the components of the Internet connection on your computer is a built-in set of instructions called TCP/IP. TCP/IP can sometimes become damaged or corrupted. If you cannot connect to the Internet and you have tried all other methods to resolve the problem, TCP/IP might be causing it.

Use the command below:
netsh int ip reset c:\resetlog.txt

Reference : How to reset Internet Protocol (TCP/IP)

You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP

You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP or in Windows 2000 or in Windows Server 2003

You may be unable to use the following components at the same time in Microsoft Windows XP, in Microsoft Windows 2000, or in Microsoft Windows Server 2003 Standard Edition with Service Pack 1 (SP1):

* A Cisco virtual private network (VPN) client
* The Internet Protocol security (IPsec) implementation that is built into Windows XP, Windows 2000, and Windows Server 2003

In this situation, you cannot use a VPN tunnel to access resources on an enterprise network on which Domain and Server Isolation policies are deployed.

Refer this here

Do we need SSIS to create Maintenance Plan

Before SQL Server 2005 SP2 was released Maintenance plans were integreated with SSIS but after SP2 Microsoft has changed that and we do not need SSIS to be installed to run maintenance jobs. And it carried over to SQL Server 2008 too.

How to change the owner of a maintenance plan

Scenario 1:
-------------
One of our members of staff had left and we had the usual case of a few jobs failing with:

"Unable to determine if the owner (DOMAIN\xxx) of job has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."

Scenario 2:
------------
A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected.

The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days. On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.

/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers

UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'

For SQL Server 2008:

update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'MaintenancePlan'

SQL Server 2000 Service account privileges

For SQL Server 2000, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, then the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) must have these user rights:

* Act as Part of the Operating System = SeTcbPrivilege
* Bypass Traverse Checking = SeChangeNotify
* Lock Pages In Memory = SeLockMemory
* Log on as a Batch Job = SeBatchLogonRight
* Log on as a Service = SeServiceLogonRight
* Replace a Process Level Token = SeAssignPrimaryTokenPrivilege