SQL Server 2016 Express LocalDBSQL Server 2016 Express LocalDB

本主題的適用對象: 是SQL Server沒有Azure SQL Database沒有Azure SQL 資料倉儲沒有Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

如需舊版 SQL Server 的相關內容,請參閱 SQL Server 2014 Express LocalDBFor content related to previous versions of SQL Server, see SQL Server 2014 Express LocalDB.

Microsoft SQL Server 2016 Express LocalDBSQL Server Express 的功能,專供開發人員使用。Microsoft SQL Server 2016 Express LocalDB is a feature of SQL Server Express targeted to developers. SQL Server 2016 Express with Advanced Services 中也會提供。It is available on SQL Server 2016 Express with Advanced Services.

LocalDB 安裝會複製啟動 SQL Server Database EngineSQL Server Database Engine所需的最少檔案。LocalDB installation copies a minimal set of files necessary to start the SQL Server Database EngineSQL Server Database Engine. 安裝 LocalDB 後,您可以使用特殊連接字串來起始連線。Once LocalDB is installed, you can initiate a connection using a special connection string. 連接時,就會自動建立及啟動必要的 SQL ServerSQL Server 基礎結構,應用程式不需複雜的組態工作即可開始使用資料庫。When connecting, the necessary SQL ServerSQL Server infrastructure is automatically created and started, enabling the application to use the database without complex configuration tasks. Developer Tools 為開發人員提供 SQL Server Database EngineSQL Server Database Engine ,讓他們撰寫和測試 Transact-SQLTransact-SQL 程式碼,而不需要管理 SQL ServerSQL Server的完整伺服器執行個體。Developer Tools can provide developers with a SQL Server Database EngineSQL Server Database Engine that lets them write and test Transact-SQLTransact-SQL code without having to manage a full server instance of SQL ServerSQL Server.

現在就試試看!Try it out!

  • 若要下載並安裝 SQL Server 2016 Express,請移至 SQL Server 下載To download and install SQL Server 2016 Express, go to SQL Server downloads. LocalDB 是您在安裝期間選取的功能,而且可以在下載媒體時使用。LocalDB is a feature you select during installation, and is available when you download the media. 如果您下載媒體,請選擇 [Express Advanced] 或 [LocalDB] 套件。If you download the media, either choose Express Advanced or the LocalDB package.

  • 有 Azure 帳戶嗎?Have an Azure account? 接著前往 這裡 ,來加速已安裝 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 的虛擬機器。Then go here to spin up a Virtual Machine with SQL Server 2016 (13.x)SQL Server 2016 (13.x) already installed.

安裝 LocalDBInstall LocalDB

透過安裝精靈或使用 SqlLocalDB.msi 程式來安裝 LocalDBInstall LocalDB through the installation wizard or by using the SqlLocalDB.msi program. LocalDB 是安裝 SQL Server 2016 ExpressSQL Server 2016 Express 時的選項。LocalDB is an option when installing SQL Server 2016 ExpressSQL Server 2016 Express.

在安裝期間,您可以在 [功能選擇/共用功能] 頁面上選取 [LocalDB]。Select LocalDB on the Feature Selection/Shared Features page during installation. 每個主要 版本只能有 LocalDB SQL Server Database EngineSQL Server Database Engine 二進位檔案的一個安裝。There can be only one installation of the LocalDB binary files for each major SQL Server Database EngineSQL Server Database Engine version. 多個 Database EngineDatabase Engine 處理序可以啟動,而且全部都會使用相同的二進位檔案。Multiple Database EngineDatabase Engine processes can be started and will all use the same binaries. LocalDB 形式啟動的 SQL Server Database EngineSQL Server Database Engine 執行個體,其限制與 SQL Server ExpressSQL Server Express 相同。An instance of the SQL Server Database EngineSQL Server Database Engine started as the LocalDB has the same limitations as SQL Server ExpressSQL Server Express.

SQL Server ExpressSQL Server Express LocalDB 的執行個體是使用 SqlLocalDB.exe 公用程式來管理。An instance of SQL Server ExpressSQL Server Express LocalDB is managed by using the SqlLocalDB.exe utility. SQL Server ExpressSQL Server Express LocalDB 應該用來取代已被取代的 SQL Server ExpressSQL Server Express 使用者執行個體功能。 LocalDB should be used in place of the SQL Server ExpressSQL Server Express user instance feature which is deprecated.

描述Description

LocalDB 安裝程式使用 SqlLocalDB.msi 程式在電腦上安裝必要的檔案。The LocalDB setup program uses the SqlLocalDB.msi program to install the necessary files on the computer. 在安裝後, LocalDB 就是可建立及開啟 SQL Server ExpressSQL Server Express 資料庫的 SQL ServerSQL Server 執行個體。Once installed, LocalDB is an instance of SQL Server ExpressSQL Server Express that can create and open SQL ServerSQL Server databases. 資料庫的系統資料庫檔案儲存在使用者本機上通常處於隱藏狀態的 AppData 路徑。The system database files for the database are stored in the users' local AppData path which is normally hidden. 例如 C:\Users\<使用者>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalDBApp1\For example C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalDBApp1\. 使用者資料庫檔案儲存在使用者指定的位置,通常是在 C:\Users\<使用者>\Documents\ 資料夾中的某個位置。User database files are stored where the user designates, typically somewhere in the C:\Users\<user>\Documents\ folder.

如需在應用程式中併入 LocalDB 的詳細資訊,請參閱 Visual StudioVisual Studio 文件:Local Data Overview (本機資料概觀)、Walkthrough: Creating a SQL Server LocalDB Database (逐步解說:建立 SQL Server LocalDB 資料庫) 與 Walkthrough: Connecting to Data in a SQL Server LocalDB Database (Windows Forms) (逐步解說:連接至 SQL Server LocalDB 資料庫中的資料 (Windows Form))。For more information about including LocalDB in an application, see the Visual StudioVisual Studio documentation Local Data Overview, Walkthrough: Creating a SQL Server LocalDB Database, and Walkthrough: Connecting to Data in a SQL Server LocalDB Database (Windows Forms).

如需有關 LocalDB API 的詳細資訊,請參閱< SQL Server Express LocalDB 執行個體 API 參考 >和< LocalDBStartInstance 函數>。For more information about the LocalDB API, see SQL Server Express LocalDB Instance API Reference and LocalDBStartInstance Function.

SqlLocalDb 公用程式可以建立 LocalDB的新執行個體,啟動及停止 LocalDB執行個體,並包含協助您管理 LocalDB的選項。The SqlLocalDb utility can create new instances of LocalDB, start and stop an instance of LocalDB, and includes options to help you manage LocalDB. 如需 SqlLocalDb 公用程式的詳細資訊,請參閱 SqlLocalDB 公用程式For more information about the SqlLocalDb utility, see SqlLocalDB Utility.

LocalDB 的執行個體定序設定為 SQL_Latin1_General_CP1_CI_AS,無法變更。The instance collation for LocalDB is set to SQL_Latin1_General_CP1_CI_AS and cannot be changed. 通常支援資料庫層級、資料行層級和運算式層級定序。Database-level, column-level, and expression-level collations are supported normally. 自主資料庫遵循 Contained Database Collations所定義的中繼資料和 tempdb 定序規則。Contained databases follow the metadata and tempdb collations rules defined by Contained Database Collations.

限制Restrictions

LocalDB 不得為合併式複寫訂閱者。LocalDB cannot be a merge replication subscriber.

LocalDB 不支援 FILESTREAM。LocalDB does not support FILESTREAM.

LocalDB 針對 Service Broker 只允許本機佇列。LocalDB only allows local queues for Service Broker.

內建帳戶 (例如 NT AUTHORITY\SYSTEM) 所擁有的 LocalDB 執行個體,可能因為 Windows 檔案系統重新導向而會有管理能力問題。請改為使用一般 Windows 帳戶作為擁有者。An instance of LocalDB owned by the built-in accounts such as NT AUTHORITY\SYSTEM can have manageability issues due to windows file system redirection; Instead use a normal windows account as the owner.

自動和具名執行個體Automatic and Named Instances

LocalDB 支援兩種類型的執行個體:自動執行個體和具名執行個體。LocalDB supports two kinds of instances: Automatic instances and named instances.

  • LocalDB 自動執行個體是公用的。Automatic instances of LocalDB are public. 這些執行個體會自動為使用者建立及管理,並且可供任何應用程式使用。They are created and managed automatically for the user and can be used by any application. 使用者電腦上安裝的每一個 LocalDB 版本各存在一個 LocalDB 自動執行個體。One automatic instance of LocalDB exists for every version of LocalDB installed on the user’s computer. LocalDB 自動執行個體提供順暢的執行個體管理。Automatic instances of LocalDB provide seamless instance management. 無需建立執行個體,它就會運作。There is no need to create the instance; it just works. 這允許應用程式輕鬆安裝和移轉到另一部電腦。This allows for easy application installation and migration to a different computer. 如果目標電腦已安裝指定的 LocalDB 版本,該目標電腦可以使用此版本的 LocalDB 自動執行個體。If the target machine has the specified version of LocalDB installed, the automatic instance of LocalDB for that version is available on the target machine as well. LocalDB 自動執行個體的執行個體名稱採用屬於保留命名空間的特殊模式。Automatic instances of LocalDB have a special pattern for the instance name that belongs to a reserved namespace. 這麼做可避免與 LocalDB具名執行個體發生名稱衝突。This prevents name conflicts with named instances of LocalDB. 自動執行個體的名稱是 MSSQLLocalDBThe name for the automatic instance is MSSQLLocalDB.

  • LocalDB 具名執行個體是私用的。Named instances of LocalDB are private. 這些執行個體是由負責建立及管理該執行個體的單一應用程式所擁有。They are owned by a single application that is responsible for creating and managing the instance. 具名執行個體與其他執行個體隔離,並透過減少與其他資料庫使用者的資源競爭來提高效能。Named instances provide isolation from other instances and can improve performance by reducing resource contention with other database users. 具名執行個體必須由使用者透過 LocalDB 管理 API 以明確的方式建立,或透過 Managed 應用程式的 app.config 檔案以隱含的方式建立 (儘管需要時 Managed 應用程式也可以使用此 API)。Named instances must be created explicitly by the user through the LocalDB management API or implicitly via the app.config file for a managed application (although managed application may also use the API, if desired). 每個 LocalDB 具名執行個體都有相關聯的 LocalDB 版本,指向各自的 LocalDB 二進位檔案集。Each named instance of LocalDB has an associated LocalDB version that points to the respective set of LocalDB binaries. LocalDB 執行個體名稱是 sysname 資料類型,最多可以包含 128 個字元The instance name of a LocalDB is sysname data type and can have up to 128 characters. (這不同於 SQL ServerSQL Server 的一般具名執行個體,其名稱限制為 16 個 ASCII 字元的一般 NetBIOS 名稱)。LocalDB 執行個體名稱可以在檔案名稱內包含任何合法的 Unicode 字元。(This differs from regular named instances of SQL ServerSQL Server, which limits names to regular NetBIOS names of 16 ASCII chars.) The name of an instance of LocalDB can contain any Unicode characters that are legal within a filename. 使用自動執行個體名稱的具名執行個體會成為自動執行個體。A named instance that uses an automatic instance name becomes an automatic instance.

    電腦的不同使用者可有同名的執行個體。Different users of a computer can have instances with the same name. 每個執行個體都是以不同使用者身分執行的不同處理序。Each instance is a different processes running as a different user.

LocalDB 的共用執行個體Shared Instances of LocalDB

為了支援電腦的多個使用者需要連接到單一 LocalDB執行個體的案例, LocalDB 支援執行個體共用。To support scenarios where multiple users of the computer need to connect to a single instance of LocalDB, LocalDB supports instance sharing. 執行個體擁有者可以選擇允許電腦上的其他使用者連接到他的執行個體。An instance owner can choose to allow the other users on the computer to connect to his instance. LocalDB 自動執行個體和具名執行個體都可以共用。Both automatic and named instances of LocalDB can be shared. 若要共用 LocalDB 執行個體,使用者必須為它選取共用名稱 (別名)。To share an instance of LocalDB a user selects a shared name (alias) for it. 因為電腦上的所有使用者都可以看到共用名稱,此共用名稱在電腦上必須是唯一的。Because the shared name is visible to all users of the computer, this shared name must be unique on the computer. LocalDB 執行個體的共用名稱與 LocalDB具名執行個體具有相同的格式。The shared name for an instance of LocalDB has the same format as the named instance of LocalDB.

僅電腦上的系統管理員可以建立 LocalDB共用執行個體。Only an administrator on the computer can create a shared instance of LocalDB. LocalDB 共用執行個體可由系統管理員或 LocalDB共用執行個體的擁有者取消共用。A shared instance of LocalDB can be unshared by an administrator or by the owner of the shared instance of LocalDB. 若要共用及取消共用 LocalDB執行個體,請使用 LocalDBShareInstance LocalDB LocalDBUnShareInstance API 的 方法,或 SqlLocalDb 公用程式的共用和取消共用選項。To share and unshared an instance of LocalDB, use the LocalDBShareInstance and LocalDBUnShareInstance methods of the LocalDB API, or the share and unshared options of the SqlLocalDb utility.

啟動 LocalDB 以及連接到 LocalDBStarting LocalDB and Connecting to LocalDB

連接到自動執行個體Connecting to the Automatic Instance

使用 LocalDB 最簡單的方式是透過連接字串 "Server=(localdb)\MSSQLLocalDB;Integrated Security=true" 連接到目前使用者所擁有的自動執行個體。The easiest way to use LocalDB is to connect to the automatic instance owned by the current user by using the connection string "Server=(localdb)\MSSQLLocalDB;Integrated Security=true". 若要使用檔案名稱來連接到特定的資料庫,請使用類似於 "Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf" 的連接字串進行連接。To connect to a specific database by using the file name, connect using a connection string similar to "Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf".

注意

電腦使用者初次嘗試連接至 LocalDB時,自動執行個體必須已建立及啟動。The first time a user on a computer tries to connect to LocalDB, the automatic instance must be both created and started. 建立執行個體所需的額外時間可能會導致連接嘗試失敗並顯示逾時訊息。The extra time for the instance to be created can cause the connection attempt to fail with a timeout message. 發生這種情況時,請等候幾秒鐘,讓建立程序完成,然後再重新連接。When this happens, wait a few seconds to let the creation process complete, and then connect again.

建立及連接到具名執行個體Creating and Connecting to a Named Instances

除了自動執行個體之外, LocalDB 也支援具名執行個體。In addition to the automatic instance, LocalDB also supports named instances. 您可以使用 SqlLocalDB.exe 程式來建立、啟動及停止 LocalDB的具名執行個體。Use the SqlLocalDB.exe program to create, start, and stop an named instance of LocalDB. 如需 SqlLocalDB.exe 的詳細資訊,請參閱 SqlLocalDB 公用程式For more information about SqlLocalDB.exe, see SqlLocalDB Utility.

REM Create an instance of LocalDB  
"C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe" create LocalDBApp1  
REM Start the instance of LocalDB  
"C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe" start LocalDBApp1  
REM Gather information about the instance of LocalDB  
"C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe" info LocalDBApp1  

以上最後一行傳回的資訊如下所示。The last line above, returns information similar to the following.

[屬性]Name "LocalDBApp1""LocalDBApp1"
VersionVersion <目前版本><Current Version>
共用名稱Shared name """"
[擁有者]Owner "<您的 Windows 使用者>""<Your Windows User>"
自動建立Auto create No
StateState 執行中running
上次啟動時間Last start time <日期和時間><Date and Time>
執行個體管道名稱Instance pipe name np:\\.\pipe\LOCALDB#F365A78E\tsql\querynp:\\.\pipe\LOCALDB#F365A78E\tsql\query

注意

如果應用程式使用 .NET 4.0.2 之前的版本,您必須直接連接到 LocalDB的具名管道。If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB. 執行個體管道名稱值是 LocalDB 執行個體接聽所在的具名管道。The Instance pipe name value is the named pipe that the instance of LocalDB is listening on. 執行個體管道名稱中 LOCALDB# 後面的部分會隨著每次 LocalDB 執行個體啟動而變更。The portion of the Instance pipe name after LOCALDB# will change each time the instance of LocalDB is started. 若要使用 Transact-SQLSQL Server Management Studio 連接到 LocalDB 執行個體,請在 [連接到 Database EngineDatabase Engine] 對話方塊的 [伺服器名稱] 方塊中,輸入執行個體管道名稱。To connect to the instance of LocalDB by using Transact-SQLSQL Server Management Studio, type the Instance pipe name in the Server name box of the Connect to Database EngineDatabase Engine dialog box. 從您的自訂程式,您可以使用類似於 LocalDB LocalDB SqlConnection conn = new SqlConnection(@"Server=np:\\.\pipe\LOCALDB#F365A78E\tsql\query");From your custom program you can establish connection to the instance of LocalDB using a connection string similar to SqlConnection conn = new SqlConnection(@"Server=np:\\.\pipe\LOCALDB#F365A78E\tsql\query");

連接到 LocalDB 的共用執行個體Connecting to a Shared Instance of LocalDB

若要連接到 LocalDB 共用執行個體,請在連接字串中加入 所需的最少檔案。\ 在連接字串中加入 (點 + 反斜線),以參考保留給共用執行個體的命名空間。To connect to a shared instance of LocalDB add .\ (dot + backslash) to the connection string to reference the namespace reserved for shared instances. 例如,若要連接到名稱為 的 LocalDB AppData 共用執行個體,請使用連接字串,例如 (localdb)\.\AppData ,做為連接字串的一部分。For example, to connect to a shared instance of LocalDB named AppData use a connection string such as (localdb)\.\AppData as part of the connection string. 使用者若要連接到他們並未擁有的 LocalDB 共用執行個體,則必須有 Windows 驗證或 SQL ServerSQL Server 驗證登入。A user connecting to a shared instance of LocalDB that they do not own must have a Windows Authentication or SQL ServerSQL Server Authentication login.

疑難排解Troubleshooting

如需有關疑難排解 LocalDB的詳細資訊,請參閱< 疑難排解 SQL Server 2012 Express LocalDB>。For information about troubleshooting LocalDB, see Troubleshooting SQL Server 2012 Express LocalDB.

PermissionsPermissions

SQL Server 2016 ExpressSQL Server 2016 ExpressLocalDB 執行個體是使用者建立、專供其使用的執行個體。An instance of SQL Server 2016 ExpressSQL Server 2016 ExpressLocalDB is an instance created by a user for their use. 電腦上的任何使用者都可以使用 LocalDB執行個體建立資料庫、在其使用者設定檔之下儲存檔案,並在其認證之下執行此處理序。Any user on the computer can create a database using an instance of LocalDB, storing files under their user profile and running the process under their credentials. 根據預設,對 LocalDB 執行個體的存取只限其擁有者。By default, access to the instance of LocalDB is limited to its owner. LocalDB 中所容納的資料受到資料庫檔案之檔案系統存取的保護。The data contained in the LocalDB is protected by file system access to the database files. 如果使用者資料庫檔案儲存在共用位置,擁有該位置之檔案系統存取權的任何人都可以使用其擁有的 LocalDB 執行個體開啟資料庫。If user database files are stored in a shared location, the database can be opened by anyone with file system access to that location by using an instance of LocalDB that they own. 如果資料庫檔案位於受保護的位置,例如使用者資料夾,則只有該使用者和擁有該資料夾存取權的任何系統管理員才可以開啟資料庫。If the database files are in a protected location, such as the users data folder, only that user, and any administrators with access to that folder, can open the database. LocalDB 檔案一次只能由一個 LocalDB 執行個體開啟。The LocalDB files can only be opened by one instance of LocalDB at a time.

注意

LocalDB 永遠都會在使用者安全性內容之下執行,也就是說, LocalDB 絕對不會使用本機系統管理員群組的認證執行。LocalDB always runs under the users security context; that is, LocalDB never runs with credentials from the local Administrator’s group. 這表示, LocalDB 執行個體使用的所有資料庫檔案都必須可使用擁有使用者的 Windows 帳戶加以存取,而不必考量本機系統管理員群組的成員資格。This means that all database files used by a LocalDB instance must be accessible using the owning user’s Windows account, without considering membership in the local Administrators group.

另請參閱See Also

SqlLocalDB 公用程式SqlLocalDB Utility