SQL Server

Liens

• SQL Server sur Ubuntu 16.04

Installation

• Management Studio
  • Management Tools - Basic
  • Management Tools - Complete
• Reporting
  • Analysis Service
  • Reporting Service
  • Integration Service

Version

SELECT @@version -- Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Jul 21 2018 07:47:45 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )

Services

SQL Server	serveur SQL
SQL Server Browser	permet la connexion au serveur SQL depuis d'autres applications
SQL Server Agent
SQL Server Reporting Services
SQL Server VSS Writer
SQL Server CEIP service

Sql Server Configuration Manager

• Menu → Computer Management → Services and Applications → Sql Server Configuration Manager
• Menu → SQLServerManager14.msc
• Windows + R → SQLServerManager14.msc

Autoriser les connexions distantes

1. Si besoin, créer un compte pour se connecter en SQL Server Authentication
   Management Studio → Security → Clique-droit sur Logins → New Login
2. Autoriser les connexions distantes
   1. SSMS → clique-droit sur le serveur → Properties → Connections → Allow remote connections to this server
   2. Computer Management → Services and Applications → Sql Server Configuration Manager → SQL Server Network Configuration → Protocols for MSSQLSERVER
      • Named Pipes → Enabled
      • TCP/IP → Enabled
      • Shared Memory → Enabled?
      • VIA → Enabled?
3. Redémarrer le service SQL Server
4. Démarrer le service SQL Server Browser

Les connexions distantes se font sur le port TCP 1433.

Activer SQL Server Authentication

1. SSMS → clique-droit sur le serveur → Properties → Security → Server authentication: SQL Server and Windows Authentication mode
2. Redémarrer le service SQL Server

Activer la connexion avec le compte sa

1. SSMS → Security → Logins clique-droit sur sa → Properties
2. General → Password
3. Status → Login → Enable

Filestream

Activer FILESTREAM sur un serveur déjà installé:

1. SQL Server Configuration Manager
2. SQL Server Configuration Manager (Local) → SQL Server Services → clique droit sur SQL Server (MSSQLSERVER) → Properties
3. Onglet FILESTREAM → cocher Enable FILESTREAM for Transact-SQL access
4. cocher Enable FILESTREAM for file I/O streaming access pour utiliser FILESTREAM depuis Windows

EXEC sp_configure filestream_access_level, 2 RECONFIGURE

Redémarrer le service SQL Server

1. SSMS → clique-droit sur MyDatabase → Properties
2. Filegroups → FILESTREAM → Add Filegroup (Name: FILESTREAM, Default: cocher)
3. Files → Add (Logical Name: MyDatabase_filestream, Filegroup: FILESTREAM)

SQL Server name alias

1. SQL Server Client Network Utility (configurer pour les applications 32 et 64 bits)
   • 32-bits → C:\windows\syswow64\cliconfg.exe
   • 64-bits → C:\windows\system32\cliconfg.exe
2. Onglet Alias → Add
3. Redémarrer le serveur

Fichiers *.mdf *.ndf *.ldf

Fichier	Description
*.mdf	fichier primaire, 1 par bdd
*.ndf	fichiers secondaires
*.ldf	fichiers contenant le journal des transactions

Changer l'emplacement des fichiers *.mdf et *.ldf

Copier les fichiers *.mdf et *.ldf dans leur nouvel emplacement.

-- Afficher les noms et les chemins SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'mabase'); -- name physical_name -- mabase_name C:\dossier\mabase.mdf -- mabase_name_log C:\dossier\mabase_log.ldf ALTER DATABASE mabase SET OFFLINE; GO ALTER DATABASE mabase MODIFY FILE ( NAME = mabase_name, FILENAME = 'C:\nouveau dossier\mabase.mdf' ); GO ALTER DATABASE mabase MODIFY FILE ( NAME = mabase_name_log, FILENAME = 'C:\nouveau dossier\mabase_log.ldf' ); GO ALTER DATABASE mabase SET ONLINE; GO

SQL local db

# name of the default instance sqllocaldb info sqllocaldb info "instance name" sqllocaldb start "instance name" sqllocaldb stop "instance name" sqllocaldb create "instance name" sqllocaldb delete "instance name"