In CATALOG.SCHEMA.OBJNAME specifications, CATALOG and/or SCHEMA may be empty-string to mean current-cat or current-schema. E.g. SELECT count(*) FROM ..tsttbl02; Roles are fixed to 8 fixed roles. Mostly useless. http://tomaslind.net/2014/02/20/list-sql-server-roles-users/ Default installed instance name (not necessarily what is connected to by default) is MSSQLSERVER. Do not name top-level installation directory under \PROGRA~1 instance-specific, because that is just a holder directory for the real instance directory homes like: C:\PROGRA~1\SQLServer_i1\MSSQL12.INST1 (Which, with my edition and options each holds just subdir MSSQL). installing SQL Server 2014 Express CTP2 Evaluation. (Reports itself as a "Technology Preview when installing). (After installation everything appears as if SQL Server 2012. Perhaps "Preview" versions are pre-GA?). When you execute the "SQLEXPRWT_x64_ENU.exe" download, it extracts and then seemingly executes the "SETUP.EXE" from the extracted dir. (Execute "SETUP.EXE" manually to start installer again, incl. to install additional Instances). Instance root: \PROGRA~1\SQLServer_i1 Shared features: \PROGRA~[12]\SQLServer_shared Nevertheless it also installed crap into \PROGRA~[12]\Microsoft SQL Server Samples not installed with DB system installations. You load samples afterward. De-selected features: Took install size from 2717 MB to 2315 MB DB Engine Svcs / SQL Server Replication Shared Feats / Client Tools Backwards Compatibility Shared Feats / Client Tools SDK Shared Feats /.../ Management Tools - Complete Shared Feats / SQL Client Connectivity SDK (is this required???) (installer status says that it installed it anyway. WTF.) Shared Feats / LocalDB "Management Tools - Basic" includes Management Studio support, SQLCMD, PowerShell provider, Replay tool (SQLCMD may require ODBC since it resides under SDK\ODBC) Named instance (a.o.t. "Default instance") "Inst1". Instance ID "INST1". This results in SQL Server dir: C:\PROGRA~1\SQLServer_i1\MSSQL12.INST1 Default DB Engine collation: SQL_Latin1_General_CP1_CI_AS Service Accounts: DB Engine Service account "NT Service\MSSQL$INST1". Won't let set password. I change from default Startup Type from Auto to Manual. Browser service account "NT AUTHORITY\LOCAL SERVICE". I left default Startup Type of Disabled. Disabled by default due to Express edition. Server Config. Change Authent. Mode from "Windows" to "Mixed...(SQL Server auth + Windows)" SQL Server authent. enables the "sa" account. sa password: Leave SQL Server administrators (unrestricted access to engine) Installing a 2nd instance. Instance root: \PROGRA~1\SQLServer_i2 "Default instance" instead of "Named instance". Appears to continue as if you specified instance name of "MSSQLSERVER". Change derived Instance ID from "MSSQLSERVER" to "MSSQLSERVER_iid". Derived dir "C:\PROGRA~1\SQLServer_i2\MSSQL12.MSSQLSERVER_IID" "sa" password: Changed some data dirs. User DB: ...\UData User DB Log: ..\UTLOg Temp DB + Log: ...\TempData Install 3rd just like 1st except "sa" password: Name: Inst3 Derived dir "C:\PROGRA~1\SQLServer_i1\MSSQL12.INST3" User data dir: ...\MSQL12.INST3\MSSQL\UData User T log dir: ...\MSQL12.INST3\MSSQL\UTLogs SQL Server Browser Service. Don't *think* I need It does get installed with one of the basic features, but, contrary to docs, it is disabled by default. Allows for browsing available servers Somehow assists connecting to correct server instance Connects to DAC endpoints Provides listings for SSAS too. QL Server Agent service that runs as user "Network Service" is also disabled by default. Executes jobs, mmonitors SQL Server, fires alerts, some admin automation. VSS Writer Service "interface to backup/restore" for some reason is Auto and running. Samples Sample databases and sample code for non-Express editions of SQL Server 2014, see Microsoft SQL Server Community Projects & Samples the CodePlex Web site. Sample databases and sample code for SQL Server Express, see SQL Server Sample Databases on the CodePlex Web site. Updates For information about how to use Microsoft Update to identify updates for SQL Server 2014, see the Microsoft Update Website Networking All IPC other than Named Pipes is disabled by default for Express installations. (Not so for all editions above Developer). Use SQL Server Configuration Manager to enable protocols wish to use. Hereafter SSCM = SQL Server Configuration Manager SSCM easiest to invoke from Start. Command line like: mofcomp c:\path\to\something.mof Client piece is SQLNCLI10, SQL Server Native Client DLL for OLE DB and ODBC. "Use SQL Server Setup to install SQLCLI as part of the SQL Server Tools." JDBC setup SSCM, SQL Server Network Configuration > Protocols for , Enable TCP/IP and double-click on TCP/IP, IP Addresses tab. Scroll to bottom and set IPAll > TCP Port to 1433. Restart main SQL Server () service. SSCM seems willing to listen to same port as another instance, but it don't work. Databases listed as "catalogs" through JDBC. From SQL: SELECT name from master..sysdatabases Current database is set at login time and may switched. USE The login database may be set per-user but is "master" by default. Current schema is "dbo" when log in as "sa" or newly created accounts by default. Can set a user's default schema too. I believe that instance is 1:1 with listener port, so there is no default instance and it can't be set per-user. *TODO: Update my sample sqltool.rc to state: If database name specified, you will connect to your default DB (which is "master" OOTB). Current schema will be your default schema (which is "dbo" OOTB). Specify instance only for validation purposes, since it is 1:1 with URL port. Exception would be if the JDBC driver supports UDP and SQL Browser service, which I don't know. Login user/password will be instance-specific (and can be database-specific). From SQL: SELECT @@SERVERNAME, @@SERVICENAME Critical file types *.mdf Primary data file *.ndf Datafile group data file Instance OOTB structure and objects SQL Server dirs like C:\PROGRA~1\SQLServer_i1\MSSQL12.INST1 They all have a single useless subdirectory "MSSQL". Useful beneath that. (Only 4 dirs contain *.mdf: Active, 2 template dirs, Binn\ has 1 static.) Active database files at DATA/*.mdf. .db + "model.mdf". All update either when database changes or upon SQL Server restart. Files open by the SQL Server service so can't open while DB running. Template database files under Binn/Templates and Template Data (with file differences and content diffs). Binn/Templates/*.mdf completely static with pre-install mod dates. Template Data/*.mdf modified only at installation time, exc "mssqlsystemresource.mdf" completely static with pre-install mod date and equiv to Binn\. Only "mssqlsystemresource.mdf" directly in Binn/. In all database file dirs, basically one *.mdf file for each DB, except: Capitalization of MSDBData varies, "model.mdf" also present, "tempdb.mdf" sometimes missing, (never-changing) 42MB "mssqlsystemresource.mdf" sometimes Databases/catalogs master. Main. See below for contents. msdb. Schemas: Same as for master + 3 more db_*, DatabaseMailUserRole, smart_admin, 3 SQLAgent*, TargetServersRole Tables: Same as for master + dbo.: autoadmin_*, backup*, log_shipping_*, logmarkhistory, msdb_version, MSdbms*, restore*, smart_bakcup_files, sqlagent_info, sys* tempdb [rdsadmin for AWS RDS] master Schemas db_* (9 of them, all system accounts) dbo guest [Does not have CONNECT privilege by default] INFORMATION_SCHEMA sys master Tables dbo.MSrepl.. 3 dbo.spt_fallback_* dbo.spt_monitor 2 sys.trace_* master Views dbo.spt_values 21 information_schema.* 404 sys.* Create global user (from 'master' catalog): use master; create login tstuser with password = 'werd'; THEN FOR DATABASES: use ; create user x for login x; GRANT whatever on tblx to x; exec sp_addsrvrolemember @loginame = 'MyNewAdminUser', @rolename = 'sysadmin'; commit; ELSEWHERE IT SAYS: CREATE LOGIN blaine FROM WINDOWS; Create contained database user create user tstuser with password = 'werd'; Create database/catalog: create database db01; Contained databases Seems only "partial contained" databases exist. I think started with SQL Server 2012. CREATE DATABASE x CONTAINMENT = PARTIAL; Seems need to "use RECONFIGURE" to set [sp_configure value 'contained database authentication'] the value_in_use. AWS RDS Express: DB identifier: express1 Master login: admin /