mssql - info

 

目錄

  • Network Port

 


Network Port

 

Difference between TCP port and TCP dynamic port

 * The default instance of SQL Server listens for incoming connections on port 1433/TCP

 * By default, named instances (including SQL Server Express) are configured to listen on dynamic ports

    and the SQL Server Browser service will map you to it.

To configure a static port

Leave the TCP Dynamic Ports box blank and provide an available port number in the TCP Port box

If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

Warning

 * 改 port 後要立即 restart mssql service

The Database Engine begins listening on a new port when restarted.

However the SQL Server Browser service monitors the registry and

reports the new port number as soon as the configuration is changed,

even though the Database Engine might not be using it.

Restart the Database Engine to ensure consistency and avoid connection failures.

常用的Port Number

  • SQL Server預設Port:            1433/TCP
  • SQL Server DAC Port:          1434/TCP
  • SQL Server Browser Port:    1434/UDP
  • SQL Server Mirroring Port:   5022/TCP
  • SSAS Port:                          2383/TCP
  • SQL Server  Service Broker: 4022/TCP

Database Read-Only

 

Fix:

USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT
GO

 


OLE DB 與 ODBC

 

OLE DB:Object Linking and Embedding Database

OLE DB # 將被 ODBC 取代

 


SQL Server 2008 R2 |  Enterprise | Standard | Express 功能分別

 

SQL Server is licensed per processor socket, and not per logical CPU basis.

# Last update 2014/09/18

Datacenter

CPU: Operating system maximum
DB_Size: ?
Memory: ?

Enterprise

CPU: 8
DB_Size: 524P
Memory: 2T

Standard

CPU: 4
DB_Size: 524P
Memory: 64G

Web

CPU: 4
DB_Size:
Memory: 64G

Workgroup

CPU: 2
DB_Size: ?
Memory: 4G

Express

CPU: 1
DB_Size: 10G
Memory: 1 G

 

R2 有 SQL 2008 沒有的功能:

Support for Geospatial Visualization
Capitalize on Hardware Innovation (logical processors supported has been increased from 64 to 256)

 


Attach DB

 

唔可以 Attach network drive 的 mdb

 


uptime

 

[1]

SELECT create_date  AS StartTime
FROM sys.databases
WHERE name = 'tempdb';

[2]

SELECT login_time AS StartTime
FROM sys.dm_exec_sessions
WHERE session_id =1;

[3]

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

[4]

EXEC xp_readerrorlog;

 


Connection Time

 

SQL Server Profiler

  • Audit Logout # 有 login 及 logout 時間

 



MsSQL 2016

 

sa login mssql 2016 18456 error

Checking the Server Authentication Mode

right-click "Server Name" > Properties > Security > Server authenticationWindows

Windows Authentication mode (Default)

  • 選 "SQL Server and Windows Authentication mode"

 


Describe all tables in the database

 

[1] 例出所有 Tables

SELECT  *
FROM databaseName.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
GO

[2] sp_columns TABLE_NAME

[3] 批量例出

sqlcmd -S SQLCLUSTER -d MyDB -i Z:\test\myscript.txt -o Z:\test\out.log

 


Useful Link