Ports needed

http://www.sqlservercentral.com/articles/networking/75481/


Service

Type

Port#

Comments

Default Instance

TCP

1433

Official socket allocated by IANA to Ms for SQL Server, this can be changed to anything above 1024.

Named Instance

TCP

XXXXX

As & what you assign in the dynamic port range. There have been changes since Win 6, see below for the available range.

DAC Default Instance

TCP

1434

Remote connections through DAC are disabled unless turned on manually. For named instance ports other than 1434 are used.

SQL Browser / SQL Server Resolution Protocol

UDP

1434

Used by an application level protocol SSRP on top of which the browser service runs.

It helps when connecting to non-hidden instances named instances. In such cases TCP port is dynamic (unless specified) & determined when the Database Engine starts. It’s not needed if all connections contain the port#. When uninstalling SQL 9.0 from a machine running SQL 8.0 check the existence of registry key IsListenerActive, because if it exists SSRP will fail to listen.

Denali Browser does not support sending information about SQL 8.0 instances.

Refer à http://msdn.microsoft.com/en-us/library/cc219750(v=PROT.10).aspx

DTS / SSIS

3882

Be cautious a malformed request to port 3882/tcp can cause DOS.

When communicating with remote SSIS port 135 is used & if it’s a SSIS package is running against a database server you need 1433 or as specified. Uses msdts1 protocol for service type msdts1.

SSAS

TCP

2393, 2394, 2725

OLAP Services 7.0 used TCP ports 2393 & 2394. Though Ms has reserved UDP ports 2393 & 2394 those are not used by OLAP Services. Analysis Services uses TCP port 2725. For backward compatibility, Analysis Services uses TCP ports 2393 & 2394 when connected with an OLAP Services 7.0 client.

SSAS

TCP

2383

Standard port for the default instance of Analysis Services. User configurable;

Browser SSAS

TCP

2382

Client connection requests for a named instance of Analysis Services that do not specify a port number are directed to SQL Server Browser.

RDP

TCP

3389

Providing the remote desktop to a client or VDI keep your eyes open because the default encryption certificate (RSA pk stored in mstlsapi.dll), is there with widows base install. A Man-in-the-Middle (MITM) attack can intercept the exchange of RDP encryption information. Check here for safety ribbons secure RDP using Transport Layer Security http://technet.microsoft.com/en-us/library/cc782610%28WS.10%29.aspx

For 6.0 Network Level Authentication offers much stronger protection http://blogs.technet.com/askperf/archive/2008/02/16/ws2008-network-level-authentication-and-encryption.aspx

Dynamic Port Range

To comply with Internet Assigned Numbers Authority recommendations, Ms has increased the dynamic client port range for outgoing connections. Since the release of Win 6.0 new default port range is 49152 – 65535 which was earlier 1025 through 5000.

Service Broker

User configurable; there is no default port. BOL conventional configuration uses TCP 4022.

SSL

TCP

443

When used with HTTP forming HTTPS, it provides an encrypted communication channel.

HTTP endpoint

Used when connecting through a url, this is user configurable; this can be customized while creating an endpoint. Port 80 for CLEAR_PORT traffic & 443 for SSL_PORT traffic.

HTTPS endpoint

TCP

443

Default instance running over an HTTPS endpoint, used for a connection through url which used SSL.

iSCSI

3260, 860

SQL Agent File Copy

135

Agent to copy backup files to the shared folder on the standby server.

137, 138, 139, 445

File copy on UNC shares.

SQL Debugger

TCP

135

Exception for IPsec traffic might also require you to set an inbound rule for 500 & 4500 if IPsec is used for network communication.

After opening port 135 include the applications Visual Studio à Devenv.exe / Management Studio à ssms.exe.

Database Mirroring

User configurable; there is no default port. While setting multiple instances be cautious to not to break the quorum. BOL conventional configuration uses TCP 7022.

Replication

TCP

1433

For push transactional replication a working 1433 between distributor & subscriber is all you need, however in pull subscriptions few other ports are needed; when you launch an initialization of a subscriber SQL uses the windows default port 445 for mapped drives to copy down scripts.

FTP (21) can be used initially to transfer schema & data over the internet; it can also use HTTP (80) or File & Print Sharing (ports 137,138, 139).

You can put merge replication to use WEB synchronization using port 80 or encrypted 443. Replication uses the IIS endpoint, when syncing over HTTP (80 by default but configurable), however IIS process connects to SQL Server through standard ports.

Keep in mind when synchronizing over the Web using FTP, there is no transfer between subscriber & IIS, it’s all amid IIS & the publisher.

Cluster Service

UDP

3343

Cluster services control & manage the cluster database. Like the Heartbeat process - Cluster network driver (Clusnet.sys) performs intra-node communication between each node of the cluster by periodically exchanging sequenced, unicast/multicast UDP datagrams in the cluster. This determines whether all nodes are running correctly & network links are healthy. Generally this does not happens over the public network.

There are cases when the range of random available IP ports that the cluster service uses to initiate communication through RPCs is less than 100 ports & connection to the Cluster Admin fails (refer to 154596(http://support.microsoft.com/kb/154596/ ) ).

RPC – 135 / Cluster Network Drv – 3343 / SMB – 445 / NetBIOS – 139 / RPC 5000-5099 / 8011-8031

135 (RPC endpoint mapper/DCOM), (RPC endpoint mapper over UDP).
For nodes running multiple services, ports 5000-5099 (or more) may be needed for remote RPC connectivity. If closed, error 1721 might occur when you connect to a remote cluster.
Cluster service requires at least 100 ports for communication through RPC. Count of ports available may get too low when other services like DNS, WINS, Ms SQL Server service & others are using some of the necessary ports.
Ports 8011-8031 must be open for internode RPC connectivity or the cluster log will indicate that a SPONSOR is not available. Again these errors occur because there are not enough ports available for RPC communication between a node trying to join the cluster & a node that can sponsor the new node.
Cluster Admin

UDP

137

Random Ports

UDP

Check dynamic port range

RPC

TCP

135

Filestream

139 & 445.

SSIS

TCP

135

DCOM

WMI

TCP

135

Used by SSCM, it runs over DCOM (aka Network OLE) when accessing remote data. After initial connection DCOM randomly assigns a port for further communication where some tools may require a TCP port > 1024 (aka TCP high port) opened on the remote host.

IPsec traffic

UDP

500 & 4500

Should be set to allow ISAKMP traffic to be forwarded for both inbound & outbound filters.

MsDTC

RPC

Since NT 4, MSDTC has been performing as the transaction coordinator for components with COM & .NET architectures. Connected resources can be databases, message queues or file systems which may be distributed. Messages are sent on TCP 135 while the responses are on a dynamically assigned port.

Check list for SQL Server Monitoring

http://www.mssqltips.com/sqlservertip/2522/sql-server-monitoring-checklist/

http://www.mssqltips.com/sqlservertip/2551/automate-sql-server-monitoring-with-email-alerts/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20111212

Best Practices in SSIS frm SQLCAT continued

You should be cautious with SSIS logging. It carries a large I/O overhead and can slow application performance dramatically. Unless absolutely required I would limit SSIS logging t

o OnError and OnTaskFail and OnWarning events

Always consider the ordering of outputs within, for example, a Conditional Split transformation. The first output stream should be used to handle the common cases that apply to the most rows in the data flow. Specify the order in which the conditions are evaluated. Order is significant, because a row is sent to the output corresponding to the first condition that evaluates to true. Handle the exceptions after the common cases.

Increase the values of the DefaultBufferMaxSize and DefaultBufferMaxRows properties of Data Flow tasks. This serves to reduce the number of buffers moving through the data flow. Be careful however, that this does not lead to a situation where the data flow engine is swapping buffers to disk, as this would be counter-productive.

Implement parallel execution. Experiment with the MaxEngineThreads and MaxConcurrentExecutables properties of packages to optimise throughput in SMP architectures. Test throughput and reset values accordingly.

Best Practices in SSIS frm SQLCAT

SSIS

http://sqlcat.com/sqlcat/b/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

The perfmon counter that is of primary interest to you is Process / % Processor Time (Total). Measure this counter for both sqlservr.exe and dtexec.exe. If SSIS is not able to drive close to 100% CPU load, this may be indicative of:

• Application contention: For example, SQL Server is taking on more processor resources, making them unavailable to SSIS.
• Hardware contention: A common scenario is that you have suboptimal disk I/O or not enough memory to handle the amount of data being processed.
• Design limitation: The design of your SSIS package is not making use of parallelism, and/or the package uses too many single-threaded tasks.

Tips to improve

• Use the NOLOCK or TABLOCK hints to remove locking overhead.
• To optimize memory usage, SELECT only the columns you actually need. If you SELECT all columns from a table (e.g., SELECT * FROM) you will needlessly use memory and bandwidth to store and retrieve columns that do not get used. .
  
• If possible, perform your datetime conversions at your source or target databases, as it is more expensive to perform within Integration Services.

If Integration Services and SQL Server run on the same server, use the SQL Server destination instead of the OLE DB destination to improve performance

Commit size 0 is fastest on heap bulk targets, because only one transaction is committed. If you cannot use 0, use the highest possible value of commit size to reduce the overhead of multiple-batch writing. Commit size = 0 is a bad idea if inserting into a Btree – because all incoming rows must be sorted at once into the target Btree—and if your memory is limited, you are likely to spill. Batchsize = 0 is ideal for inserting into a heap. For an indexed destination, I recommend testing between 100,000 and 1,000,000 as batch size.

A key network property is the packet size of your connection. By default this value is set to 4,096 bytes. This means a new network package must be assemble for every 4 KB of data. As noted in SqlConnection.PacketSize Property in the .NET Framework Class Library, increasing the packet size will improve performance because fewer network read and write operations are required to transfer a large data set.
If your system is transactional in nature, with many small data size read/writes, lowering the value will improve performance.

Since Integration Services is all about moving large amounts of data, you want to minimize the network overhead. This means that the value 32K (32767) is the fastest option

Do not sort within Integration Services unless it is absolutely necessary

• Try to perform your data flows in bulk mode instead of row by row. By doing this in bulk mode, you will minimize the number of entries that are added to the log file. This reduction will improve the underlying disk I/O for other inserts and will minimize the bottleneck created by writing to the log.
  
• If you need to perform delete operations, organize your data in a way so that you can TRUNCATE the table instead of running a DELETE. The latter will place an entry for each row deleted into the log. But the former will simply remove all of the data in the table with a small log entry representing the fact that the TRUNCATE occurred. In contrast with popular belief, a TRUNCATE statement can participate in a transaction.
  
• Use the SWITCH statement and partitioning. If partitions need to be moved around, you can use the SWITCH statement (to switch in a new partition or switch out the oldest partition), which is a minimally logged statement.
  
• Be careful when using DML statements; if you mix in DML statements within your INSERT statements, minimum logging is suppressed.

Integration Data Type mapping to database data Type

Data Type	SQL Server (SQLOLEDB; SQLNCLI10)	SQL Server (SqlClient)
DT_BOOL	bit	bit
DT_BYTES	binary, varbinary, timestamp	binary, varbinary, timestamp
DT_CY	smallmoney, money	smallmoney, money
DT_DATE
DT_DBDATE	date	date
DT_DBTIME
DT_DBTIME2	time(p)	time(p)
DT_DBTIMESTAMP	datetime, smalldatetime	datetime, smalldatetime
DT_DBTIMESTAMP2	datetime2	datetime2
DT_DBTIMESTAMPOFFSET	datetimeoffset(p)	datetimeoffset(p)
DT_DECIMAL
DT_FILETIME
DT_GUID	uniqueidentifier	uniqueidentifier
DT_I1
DT_I2	smallint	smallint
DT_I4	int	int
DT_I8	bigint	bigint
DT_NUMERIC	decimal, numeric	decimal, numeric
DT_R4	real	real
DT_R8	float	float
DT_STR	char, varchar
DT_UI1	tinyint	tinyint
DT_UI2
DT_UI4
DT_UI8
DT_WSTR	nchar, nvarchar, sql_variant, xml	char, varchar, nchar, nvarchar, sql_variant, xml
DT_IMAGE	image	image
DT_NTEXT	ntext	text, ntext
DT_TEXT	text

DimDate

----- Populate Date Dimesion ---- Created by Pradeesh Mathew

select * from dimdate

----------------------------------------------------------

CREATE TABLE [dbo].[DimDate](

[DateKey] [int] NOT NULL,

[Date] [date] NOT NULL,

[DateFullName] [nvarchar](50) NULL,

[DayofWeek] [tinyint] NOT NULL,

[DaynumberinMonth] [tinyint] NOT NULL,

[DaynumberOverall] [smallint] NOT NULL,

[DayName] [nvarchar](9) NOT NULL,

[DayAbbreviation] [nchar](3) NOT NULL,

[WeekNumberinYear] [tinyint] NOT NULL,

[WeekNumberOverall] [smallint] NOT NULL,

[WeekBeginDate] [smalldatetime] NULL,

[Month] [tinyint] NOT NULL,

[MonthNumberOverall] [smallint] NOT NULL,

[MonthName] [nvarchar](9) NOT NULL,

[MonthAbbreviation] [nchar](3) NOT NULL,

[Quarter] [nchar](2) NOT NULL,

[Year] [smallint] NOT NULL,

[YearMonth] [int] NOT NULL,

[FiscalMonth] [tinyint] NOT NULL,

[FiscalQuarter] [nchar](2) NOT NULL,

[FiscalYear] [smallint] NOT NULL,

[IsLastDayinMonth] [nchar](1) NOT NULL,

[SameDayYearAgoDate] [smalldatetime] NULL,

[DateType] [nvarchar](50) NULL,

[IsWeekend] [bit] NULL,

[IsLeapYear] [bit] NULL,

[IsHoliday] [bit] NOT NULL,

[HolidayName] [nvarchar](50) NOT NULL,

[Season] [nvarchar](50) NOT NULL,

[InsertAuditKey] [int] NOT NULL,

[UpdateAuditKey] [int] NOT NULL,

CONSTRAINT [PK_dbo.DimDate] PRIMARY KEY CLUSTERED

(

[DateKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

---------------- sql code ------------------------------

DECLARE @FullDate DATETIME

DECLARE @DayofWeek tinyint

DECLARE @DayName nvarchar(10)

DECLARE @DayofMonth tinyint

DECLARE @DayofYear smallint

DECLARE @MonthName nvarchar(10)

DECLARE @CalWEEK tinyint

DECLARE @CalMONTH int

DECLARE @CalQUARTER tinyint

DECLARE @CalYEAR int

DECLARE @FisWEEK tinyint

DECLARE @FisMONTH int

DECLARE @FisQUARTER tinyint

DECLARE @FisYEAR int

DECLARE @IsWeekend BIT

--DECLARE @IsHoliday BIT

DECLARE @LeapYear BIT

SELECT @FisWeek = 1 --assuming we start with 1 Jan YYYY

SELECT @CalWeek = 1

SELECT @LeapYear =0

DECLARE @startdate DATETIME ='01/01/2010'

DECLARE @enddate DATETIME ='12/31/2011'

declare @overallday int=1

declare @overallMonth int=1

declare @overallyear int=1

SELECT @FULLDATE = @STARTDATE

WHILE @FullDate<=@enddate

BEGIN

SELECT @DAYOFWEEK = DATEPART (DW , @FULLDATE)

SELECT @DAYNAME = DATENAME(WEEKDAY,@FULLDATE)

SELECT @DAYOFMONTH = DATEPART (DAY , @FULLDATE)

SELECT @DAYOFYEAR = DATEPART (DY , @FULLDATE)

SELECT @MONTHNAME = DATENAME(MONTH,@FULLDATE)

SELECT @CALYEAR = DATEPART (YEAR, @FULLDATE)

SELECT @CALQUARTER = DATEPART (QUARTER, @FULLDATE)

SELECT @CALMONTH = DATEPART (MONTH , @FULLDATE)

SELECT @CALWEEK = DATEPART (WEEK , @FULLDATE)

SELECT @FISYEAR = DATEPART (YEAR, @FULLDATE)

SELECT @FISQUARTER = DATEPART (QUARTER, @FULLDATE)

SELECT @FISMONTH = DATEPART (MONTH , @FULLDATE)

SELECT @FISWEEK = DATEPART (WEEK , @FULLDATE)

SELECT @ISWEEKEND = 0

--check for leap year

IF ((@calyear % 4 = 0) AND (@calYEAR % 100 != 0 OR @calYEAR % 400 = 0))

SELECT @LeapYear =1

ELSE SELECT @LeapYear =0

INSERT INTO DIMDATE

SELECT CAST(CONVERT(VARCHAR(8),@FULLDATE,112) AS INT), --- DATEKEY

CAST(@FULLDATE AS DATE), --- DATE

@FULLDATE , --- DATEFULLNAME

@DAYOFWEEK, --- DATE OF THE WEEK

@DAYOFMONTH, --- DAY NUMBER IN MONTH

@overallday, --- DAYNUMBEROVERALL

@DAYNAME, --- DAY NAME

SUBSTRING(@DAYNAME,1,3), --- DAY ABBREVIATION

DATENAME(WEEK,@FULLDATE), --- WEEK NUMBER IN YEAR

(@overallday/7) + 1 , --- WEEKNUMBEROVERALL

DATEADD(WW, DATEDIFF(WW,0,@FULLDATE), 0), --- WEEKBEGINDATE STARTS WITH MONDAY

@CALMONTH, --- MONTH

0, --- OVER ALL MONTH NUMBER

@MONTHNAME, --- MONTHNAME

SUBSTRING(@MONTHNAME,1,3), --- ABBREVIATED MONTHNAME

'Q' + CAST(DATEPART(QUARTER,@FULLDATE) AS VARCHAR(1)),-- QUARTER

@CALYEAR, --- YEAR

0, --- YEAR MONTH

@FISMONTH,

@FISQUARTER,

@FISYEAR,

CASE WHEN DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@FULLDATE)+1,0))=@FULLDATE THEN 'Y'

ELSE 'N' END , --- IS LAST DAY IN MONTH

DATEADD(DD,-365,@FULLDATE), --- SAME DAY LAST YEAR

CASE WHEN @DAYNAME IN ('SUNDAY','SATURDAY') THEN 'Weekend'

ELSE 'Weekday' END , --- DATETYPE

CASE WHEN @DAYNAME IN ('SUNDAY','SATURDAY') THEN 1 -- Weekend bit

ELSE 0 END ,

@LeapYear, --- Is Leap Year

0, --- IS HOLIDAY

'No Holiday', --- HOLIDAY NAME

'Season Name', --- SEASONNAME

0, --- INSERTAUDITKEY

0 --- UPDATEDAUDITKEY

SET @FULLDATE = @FULLDATE +1

set @overallday=@overallday +1

END

Search all text in SQL Server

http://www.mssqltips.com/tip.asp?tip=1525&ctc