Checking compatibility for a SQL Server database migration to Azure with sqlpackage.exe

The SSMS installation comes with a command line utility called sqlpackage.exe . Commonly used to export sql server databases to Azure. On a typical default installation the path will be like : C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe One of it’s features is the compatibility checks ,generating a report of potential issues The basic structure for using the compatibility checks is : 'sqlpackage.exe /Action:Export /ssn:< server_name > /sdn:< database_name > /tf:< target_file > /p:TableData=< schema_name.table_name > > < output_file > 2>&1' Argument Description < server_name > source server name < database_name > source database name < target_file > file name and... Read more →

Resource database in SQL Server SystemResource db - mssqlsystemresource

Question: I’m getting error messages when starting SQL Server about a missing mssqlsystemresource.mdf file . How can I track down down some information about the Mssqlsystemresource database? The tactic I’ll deploy is to get a copy of the Mssqlsystemresource mdf and ldf files from another SQL Server instance and place in the location on the current corrupted SQL Server Answer: I’ve used this tactic previously and it can work – as long as the versioning is accurate and you replace the files on the correct paths. The two queries will assist in the troubleshooting . Before you replace from another... Read more →

Hide instance of SQL Server

Question: How can I hide a SQL Server Instance from clients attempting to make connections? SQL Server Browser allows a client is able to find a SQL Server Instance, but I want to hide the instance from the client. Answer: It is possible to hide the SQL Server instance using the SQL Server Configuration Server. Expand SQL Server Network Configuration | right click Protocols for my_instance | Click properties Use the HideInstance box in the Flags tab. Change it to Yes, and restart the SQL Server Instance If you attempt to connect , after you have changed the HideInstance box... Read more →

DAC and Server is in single user mode – Error 18461

I was working on an emergency fix of a TempDB file location issue and had to create a Dedicated Administration Connection . I followed these instructions and successfully started the SQL Service Attempting to connect via the SQLCMD –A –E –S switches , the following error message appeared Msg 18461, Level 14, State 1, Server MYSERVER\SQL1, Line 1 Login failed for user 'MYDOMAIN\MYID'. Reason: Server is in single user mode. On ly one administrator can connect at this time. After staring at the screen (for a short while) , I realised other services exist automatically taking a connection. Checking services... Read more →

SQL Sever - Dedicated Administrator Connection - DAC

The SQL Server Dedicated Administration Connection (DAC) once again saved the day! A bad logon trigger on a development SQL Server caused all Logons to go into ROLLBACK. In effect , the Logons were authenticating but at the point of creating a user session the Logon would ROLLBACK. 1) The DAC is only allowed on the local server. Remote connections are not allowed except if configured with the sp_configure. For local server access , you’ll either need to connect locally or use RDP. If you can't RDP logon because other users logged on read How to View Remote Desktop Sessions... Read more →