Advertisement
Guest User

Untitled

a guest
Jan 16th, 2017
123
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.58 KB | None | 0 0
  1. Tips for Microsoft SQL Server Express Noobs
  2. ===========================================
  3.  
  4. Install SQL Server Express with Chocolatey or PackageManagement module:
  5.  
  6. ```
  7. choco install 'mssqlserver2014express
  8. # or
  9. Install-Package -Name 'mssqlserver2014express' -ProviderName 'chocolateyget'
  10. ```
  11.  
  12. However SQL Express cannot install into a folder that is NTFS compressed, so the
  13. here's a good script to make sure that the default installation directories are
  14. not NTFS compressed. It relies on the Carbon module to supply
  15. `Disable-NtfsCompression`. Alternatively `compact.exe` can be used.
  16.  
  17. ```
  18. New-Item -ItemType Directory -Force -Path "${Env:ProgramFiles}\Microsoft SQL Server"
  19. New-Item -ItemType Directory -Force -Path "${Env:ProgramFiles(x86)}\Microsoft SQL Server"
  20. Disable-NtfsCompression -Path "${Env:ProgramFiles}\Microsoft SQL Server"
  21. Disable-NtfsCompression -Path "${Env:ProgramFiles(x86)}\Microsoft SQL Server"
  22. if ($Force) {
  23. Install-Package -Name 'mssqlserver2014express' -ProviderName 'chocolateyget' -Force
  24. } else {
  25. Install-Package -Name 'mssqlserver2014express' -ProviderName 'chocolateyget'
  26. }
  27. Set-Service -Name 'MSSQL$SQLEXPRESS' -StartupType Manual
  28. Set-Service -Name 'SQLWriter' -StartupType Manual
  29. Stop-Service -Name 'MSSQL$SQLEXPRESS'
  30. Stop-Service -Name 'SQLWriter'
  31. ```
  32.  
  33. The above sets the service not to run on startup, and stops the service.
  34.  
  35. To restart the service use:
  36.  
  37. ```
  38. Restart-Service -Name 'MSSQL$SQLEXPRESS'
  39. ```
  40.  
  41. To get started using SQL Server Express, you must enable Named Pipes and TCP/IP
  42. connections via `Sql Server Configuration Manager`, and the settings are located
  43. at `SQL Server Network Configuration/Protocols for SQLEXPRESS`. When enabling
  44. the TCP/IP connection, make sure to check the properties so it is not listening
  45. on all IPs. Instead individually enable the interfaces you want SQL Server to
  46. listen on. In the beginning, only enable `127.0.0.1` and `::1` interfaces. Make
  47. sure to set the `TCP port` to `1433` (for both `127.0.0.1` and `::1`) and blank
  48. out the `TCP Dynamic Ports` field.
  49.  
  50. The installation of the package several new system PATHs. So after restarting
  51. Powershell, you can run `sqlcmd -S .\SQLEXPRESS`. This may not work inside a
  52. Cygwin environment, so you need a real Powershell terminal.
  53.  
  54. This launchs `sqlcmd` interactive session while connecting the default SQL
  55. Server Express instance called `SQLEXPRESS`, alternatively you can address it
  56. as `localhost\SQLEXPRESS`. Note that the real SQL Server's default instance will
  57. be `MSSQLSERVER`. It's possible to have multiple instances running, each
  58. listening on a different port. However for development purposes, we are just
  59. going to use the default instance.
  60.  
  61. When in the interactive environment try this (yes the command terminator is a `GO` line...):
  62.  
  63. ```
  64. >> sp_databases;
  65. >> GO
  66. ```
  67.  
  68. Or if running directly from the terminal:
  69.  
  70. ```
  71. > sqlcmd -S .\SQLEXPRESS -Q "sp_databases;"
  72. ```
  73.  
  74. The above command shows all the databases available to SQL Server.
  75.  
  76. There will be some default databases already available, these are the system
  77. databases, you won't be adding application tables to this database. However
  78. if you accidentally add lots of data to your master database (which is the
  79. default database) it is possible to reset your system databases by running:
  80.  
  81. ```
  82. .\setup.exe /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="POLYHACK-SURFB1\CMCDragonkai"
  83. ```
  84.  
  85. Note that the value for `/SQLSYSADMINACCOUNTS` depends on what your current
  86. account is. Mine is the `COMPUTERNAME\USERNAME`.
  87.  
  88. The `.\setup.exe` depends on the version you installed SQL Express at. Currently
  89. mine is at: `C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\setup.exe`.
  90.  
  91. To use a different database, first create one then run `USE database;` then `GO`.
  92.  
  93. `sqlcmd` has actually a pretty poor user experience, you really have to use a
  94. GUI tool. I suggest DBeaver. To connect via DBeaver, follow these instructions:
  95.  
  96. > To create a "Microsoft Driver" connection in DBeaver
  97. > Create a new connection, specifying MS SQL Server | Microsoft Driver
  98. > Fill out the info on the first (General) tab, without specifying User name and Password (leave them blank).
  99. > Go to the Driver properties tab and set integratedSecurity=true.
  100. > And, once again, just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
  101. > Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.
  102.  
  103. To run a T-SQL script file use:
  104.  
  105. ```
  106. sqlcmd -S .\SQLEXPRESS -d 'database-name' -i .\t-sql-script.sql
  107. ```
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement