目錄
- 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