SQL Server

2014 Express drove me nuts to enable SQL Authentication, but after several failed attempts I figured SQL Server Express must have disabled SQL authentication by default, it turned out to be true. Once that was known it simply took several seconds to get the job done. Below were the steps I performed :

1. Start SSMS Express 2014, and login with your windows account. In my case I had to create a brand new database, so new Database –> and hit OK. ( just so you guys know that I did not do anything special here )
2. Security -> Logins -> Right Click -> New Login
3. punch in log in name and password ( personally, I like to enforce sec policy hence I leave them as is ) and from the pull down menu on the bottom I changed the default database to the one that I just created ( optional but that way I donot have to change the database everytime I wanna execute a script as I give different names to my application’s database for statging, QA, dev etc )
4. Now go back to your database -> Security -> Users -> Right Click -> New User (If you’re coming from 2008 R2, you might notice a little change here.)
   punch in the desired user name, and then enter the login name you created earlier or click the “…” button –> Browse if you’ve got a fat finger like mine 😉 Similarly, default schema, I chose it to be “dbo”
5. Now at this point if you were to log out and log back in your SQL Server you might not be able to login !
6. … the catch is if you log back in your SQL Express 2014 using your windows authentication just one more time. n SQL Server Management Studio Object Explorer, right-click the server –> Properties.
   On the Security page, under Server authentication you will see Windows Authentication mode … aaaeeeww , select SQL Server and Windows authentication mode, and then click OK.
7. Restart your SQL Server
8. Now try to log in with your SQL login … and in my case I saw an error message saying “a connection was successfully established with the server, but then an error occurred during the login process. (provider: shared memory provider, error: 0 – no process is on the other end of the pipe.) (microsoft sql server, error: 233)”
9. Well, it’s more of a hint then an error message, and it also had a help link : http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233
   Which had the explanation : The SQL Server client cannot connect to the server. This error could occur because the server is not configured to accept remote connections. Okay, now how do we allow remote connections ?
10. Close SSMS
11. Fire up your SQL Server 2014 Configuration Manager ( How ? Start –> Microsoft SQL Server 2014 –> Configuration Tools –> SQL Server 2014 Configuration Manager )
12. On the tree left Expand “SQL Server Network Configuration” –> Protocols for SQLEXPRESS –> On the window right, you’d see TCP/IP –> Right Click –> Properties –> go to IP Address tab –> All the way down to “IP All” section –> and set the vaue for TCP Port from blank (null) to 1433 –> Click OK.
    Finally, from the tree left on the SQL Server 2014 Configuration Manager, select SQL Server Services and restart.
    Now you should be able to log in ! 🙂

D:/ 30GB free
E:/ 20GB free &
F:/ 80GB free

Sadly, as far as I know SQL Server would simply split the files in equal or nearly equal files. But I think, there *might* be a workaround … at the time of splitting up specify different drives. Do your math and see if that works for you ! 🙂

e.g. :

In the case above I should try to get each split chunk < 20 GB, I’d make 17 GB to be safe [ Why 20 GB ? Min (30,20,80). ]

DISK = ‘D:\Backups\NW_0803141035_1.bak’
,DISK = ‘D:\Backups\NW_0803141035_2.bak’
,DISK = ‘E:\Backups\NW_0803141035_3.bak’
,DISK = ‘F:\Backups\NW_0803141035_4.bak’
,DISK = ‘F:\Backups\NW_0803141035_5.bak’
,DISK = ‘F:\Backups\NW_0803141035_6.bak’

exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"

From http://stackoverflow.com/a/1149168

I am not saying it’s creepy but if you’re using diagrams and such in your database, it might also screw up your database’s sysdiagrams etc table ( and you might not realize ) I recommend this way :

USE [SomeDatabaseNameHere]
SELECT 'ALTER SCHEMA new_schema TRANSFER dbo.' + t.NAME
FROM sys.tables AS t
INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
WHERE s.NAME = N'dbo';

Not “WHAM & BAM” like the script above but still pretty straight forward and most importantly, it lets you review ( if you have a time to pause and scroll through the table names )

Secondly, at the end of the day it is still a select query with a WHERE clause ! You can have a granular control as you please ! For instance :

USE [SomeDatabaseNameHere]
SELECT 'ALTER SCHEMA new_schema TRANSFER dbo.' + t.NAME
FROM sys.tables AS t
INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
WHERE s.NAME = N'dbo' AND t.NAME LIKE '%configuration%'

… this way, you only change the schema for the tables that have configuration word in their name !

And finally this is how your outcome should look like :

ALTER SCHEMA app TRANSFER dbo.Table_1
ALTER SCHEMA app TRANSFER dbo.Table_2
ALTER SCHEMA app TRANSFER dbo.Table_3
.
.
.
ALTER SCHEMA app TRANSFER dbo.Table_N

All you have to do now is to copy-paste them in query and F5 !

Remember :

1. you don’t have to spend too much of your time to write a smart ass code
2. craft a clean + simple code – to increase readability
3. structure it, organize it, refactor it – to increase maintainability

Nugget :

So you have been using “IF … EXISTS” pattern in your TDD scenario or wherever you want to re-run a script … and if you have not specifically encountered a situation where you’ve to do it with schema, something might make you pull up your hairs !

USE [SomeDatabaseNameHere]
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'My_New_Schema')
begin
	CREATE SCHEMA [My_New_Schema] AUTHORIZATION [dbo]
end

… and Error : “Incorrect syntax near the keyword ‘My_New_Schema’.” !
… you make sure you don’t have a typo and error again ! WTH ?

Why in its infinite wisdom does SQL Server (2008 R2) give you error ? Thanks to Phil Steffek’s post ( quoted below ) for writing a shot and sweet post !

									IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'My_New_Schema')
									begin
										EXEC sp_executesql N'CREATE SCHEMA [My_New_Schema] AUTHORIZATION [dbo]'
									end
									

TC !