SQL Azure cross database queries – evaluation

29-Oct-1818 Leave a comment

Existing documentation to create external database seems fine. Links include:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

https://ppolyzos.com/2016/07/30/cross-database-queries-in-azure-sql-databases/

Troubleshooting

Error: “The option ‘IDENTITY’ is not supported with external tables for sharded data”
Remove the IDENTITY from the CREATE EXTERNAL TABLE id column

 

Using a different name in target

If the target is already using the external name you may want to use a different name. See this article. Add following to the with statement.

SCHEMA_NAME = ‘Sales’, OBJECT_NAME = ‘Quote’

https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/

One option is to create a view in the source with the desired name. Then in the target reference that. Works.

Performance

Slightly slower than using the source database, but pretty good – certainly usable in most cases.

Filed under SQL Azure, Uncategorized

Run SQL Server with Docker

25-Oct-1818 Leave a comment

 

Why

    1. Developer machines
      On my developer machines I have installed various version of SQL Server. These run services in the background and are difficult to stop and uninstall.Containers offer the opportunity to install then destroy and start again.
    2. Development and Testing
      Store images/containers and then recreate
    3. Future: create images which derive from base images and store and share in a repository possibly in Azure Container Registry

Get Started:

Install docker for windows

https://docs.docker.com/docker-for-windows/install/

Turn on virtualisation
Hyper-V
Docker

Quickstart: Run SQL Server container images with Docker

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017

Note on using PowerShell

Some docker commands do not work inside the ISE.
For those, or all commands use PowerShell console window

Connect from outside the container

From SqlCmd or SSMS

Use the IP address of the machine that hosts your container (probably your machine)
e.g. 10.0.0.21,1433

The function inspect gives information about the container, but not the host IP:
docker inspect

docker inspect –format ‘{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ <container id>

 

 

Restore a database into the image

Now, we have SQL running in docker, it would be useful to restore a database into that.

  1. Outside Docker
    Backup database
    Tip: SSMS backup in UI, but then “Script action to new query window” and store it
  2. Copy file into the running image
    Use Bash
    docker exec -it sql1 “bash”Then Linux commands to make directory
    Exit Bash
    docker cp MyDatabase.bak sql1:\var\opt\mssql\backup
  3. Inside Docker
    Restore database – use script – may need to Move files

 

Bake files into an image for reuse

docker commit sql1 sqltestco:sql2017
docker images

Clean up with:
docker rmi sqltestco:sql2017

 

Use image to create docker images

Now we have an image with our database in it. We may want to use it once or many times.

Keep changing the name and the port
I found that –name needs to come first

docker run –name sqltestco1 -d -p 1434:1433 sqltestco:sql2017
docker run –name sqltestco2 -d -p 1435:1433 sqltestco:sql2017

Now have 3 instances of SQL running.

As needed:

docker start
docker stop
docker rm

 

Push images to Docker Hub

Use:
docker images
docker tag <imageid>:<tagname> <dockerId>/<repository>:<tagname>
docker login (good idea to spell everything correctly)
docker push <dockerId>/<repository>:<tagname>

Took a few attempts – got there. Private repository.

 

 

Troubleshooting

Seem to get a conflict if other containers exist but are stopped. Possibly good practice to be aware of all containers with:

docker ps -a

Error response from daemon: … userland proxy

Error response from daemon: driver failed programming external connectivity on endpoint … Error starting userland proxy: mkdir /port…

Restart docker – fixed it for me.

 

See also

Pluralsight : Getting started with Docker on Windows

https://app.pluralsight.com/library/courses/docker-windows-getting-started/table-of-contents

 

 

 

Filed under Docker, SQL Server

SQL Server Agent Job Verbose Logging

19-Oct-1515 Leave a comment

Please see useful page:

https://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/

  1. In Job Step – Advanced – Output file
  2. Likely strategy is that on first step do not tick “Append output to existing file”
  3. On subsequent steps tick this and then you will get the full output of the series

 

 

Filed under SQL Server

Arguments with regard to SpareField1 or specific schema

11-Sep-1414 Leave a comment

Because each new feature or property addition requires database changes there is the idea that instead you hold data in fields such as SpareText1, SpareInt1 etc. and this post discusses the relative merits of this and its alternatives. Another place this may be used is to create a common Lookup Field table.

It is my opinion that this should normally be avoided and instead specific schema is used.

Advantages of specific schema

  1. Data type integrity
  2. Required and non-required control by product (Null or Non-Null)
  3. Strongly typed code
  4. Reporting is easier
  5. Discoverability is easier – stay in context
  6. For lookup tables, you will gain referential integrity, and in any case it is possible and not too difficult to create abstract data update forms which may update any of these with data-driven addition of which tables to edit.

Disadvantages of specific schema

  1. You have to change the schema for each change
  2. If you have multiple client databases you may need to change several. If you have many this may be difficult

Mitigations to disadvantages of specific schema

  1. Learn and use simpler design techniques to speed up development. E.g. Entity Framework, code generation
  2. Write forms which may edit any tables. For instance for Lookup tables

Disadvantages of SpareField1

  1. You have to keep a record of what each Spare Field represents. Control and communication of that is difficult, control of a specific, e.g. Length field is not necessary because it says what it is
  2. That may get confusing, or there is a danger that Spare Field may be used in different ways by different people. So in the end it is more complex

Data type integrity

If you have a decimal with precision 4 and scale 2, i.e. 2 dp, max 99.99, then this controls the data. A SpareDecimal1 may have any data type definition, probably too large so as to cope with the general case, and as such bad data may appear, so for instance if you know length is always less than 100 units, then a data entry of 101 is bad data.

Also data type integrity would include Null control

Filed under SQL Server Tagged with ,

SQL Server hangs on drop foreign key

10-Jul-1414 Leave a comment

This actually happened on Azure during part of a SSIS SQL Server integration package, removal of key before delete and upload of data

Following post was very useful

http://weblogs.asp.net/jeffwids/lock-request-time-out-period-exceeded

To find sessions then

SELECT r.session_id, r.status, r.start_time, r.command, s.text,
r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

then Kill session id, but you cannot kill your own session, so open another session to do the kill

End.

Filed under SQL Azure, Transact SQL

SQL Server – Take database offline hangs

05-Jun-1414 2 Comments

With thanks to following and other web searches:

SQL Server – database “take offline” hangs

Scenario

New SQL Server on a new Azure VM, with a few new issues to learn from. I attempted to take a database offline but it hung.

Solution

USE master

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks

--Find troublesome session and kill it.
--You cannot kill your own process, so you may need to start another session
KILL [SPID]

To take the database offline without waiting try this:

alter database DOTNETNUKE_EBC set offline with rollback immediate

To bring database online:

alter database DOTNETNUKE_EBC set online

 

End

 

Filed under SQL Configuration, SQL Server

Use Microsoft.Data.DataConnectionDialog in your projects to retrieve connection string

28-May-1414 Leave a comment

This is the use of the Visual Studio connection dialog in your own projects, for instance tool kits to inspect databases. Steps are:

  1. NuGet
  2. Search and add Data Connection (Windows Forms)
  3. Add code, sample is in VB, but C# is similar

 


Imports Microsoft.Data.ConnectionUI

    Private Sub GetDataConnection()

        Dim dcd As New DataConnectionDialog
        DataSource.AddStandardDataSources(dcd)
        dcd.SelectedDataSource = DataSource.SqlDataSource

        If DataConnectionDialog.Show(dcd) = Windows.Forms.DialogResult.OK Then
            Select Case dcd.SelectedDataProvider.Name
                Case DataProvider.SqlDataProvider.Name
                    Dim cn As New SqlClient.SqlConnection(dcd.ConnectionString)
                    DataSourceToolStripTextBox.Text = cn.DataSource
                    DatabaseDefaultToolStripTextBox.Text = cn.Database
                    'MessageBox.Show(String.Concat("Found SQL: ", cn.ConnectionString))

                Case Else
                    MessageBox.Show(dcd.ConnectionString)
            End Select
        End If

    End Sub

See also
http://wildermuth.com/2007/1/29/Using_the_Visual_Studio_Connection_String_Dialog

Filed under SQL Server, Visual Studio

Azure Virtual Machines – with SQL Server

28-Apr-1414 1 Comment

Setting up a VM for SQL Server Web Edition

  1. Created my first virtual machine with SQL Server Web edition on it.
  2. Added a windows user. Added the equivalent SQL Server windows user.
  3. Checked could login to SQL Server OK
  4. Used sysprep as a starting point
  5. Then took an image.
  6. Then created a new virtual machine from that image
  7. Logged in to new VM ok – but error on logging in to SQL Server.
  8. Then after a weekend the error had changed to “A connection was successfully established with the server, but then an error occurred during the login process (provider: Shared Memory Provider: 0 – No process is on the other end of the pipe)(Microsoft SQL Server, Error: 233)
  9. This was because SQL Server Agent was not running – started it
  10. In fact in SQL Server Configuration SQL Server Agent and Browser had to be changed to automatic startup
  11. Then back to original error

Error Login failed for user (Microsoft SQL Server Error 18456) on VM with SQL Server

  1. It seems that the user who created the SQL login on the first machine is not the same as the user who created the new machine after the sysprep
  2. So destroyed the machine and started again.
  3. New strategy – put SQL Server into Mixed Authentication Mode
  4. Create a SQL Server Login
  5. Give that login sysadmin
  6. Create the image.
  7. Create the VM from the image.
  8. Confirm that the Windows logins do not work for SQL Server
  9. Login as that SQL Server login admin created before, rather than the Windows Authentication only.
  10. Then script to drop and create the windows logins
  11. Issue resolved

Mixed Authentication Mode is required if logging into the SQL Server from another machine in any case.

So if creating a new VM from the image then start from step 7 above

 

Error: Login failed for remote SQL server

 

Solution

In the VM > Network and Sharing Center > View your active networks

The network had been set to private – reset to public and it now works again

 

 

How to setup a Virtual Machine for connection to SQL Server from another Machine

http://azure.microsoft.com/en-us/documentation/articles/virtual-machines-provision-sql-server/

 

Filed under Azure, SQL Server

Problem with Microsoft Report on Terminal Server

06-Nov-1313 Leave a comment

http://connect.microsoft.com/SQLServer/feedback/details/585713/problem-with-reportviewer-10-on-ts-with-a-screen-that-is-not-4-3

http://support.microsoft.com/kb/2768741

http://go4answers.webhost4life.com/Example/problem-microsoft-report-terminal-63827.aspx

Filed under SQL SSRS, Troubleshooting, Visual Studio

Error setting up SQL Server CDC – Change Data Capture

13-Aug-1313 Leave a comment

Error when using:
EXECUTE sys.sp_cdc_enable_db

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
Could not update the metadata that indicates database <dbname> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

Solution:

Database > Properties > Files >
Check that the databse has an owner

For me this went wrong because ‘dbo’ existed on the server (pc) where this database was created, but did not exist on this server (pc) (SQL Server Developer Edition)

Filed under Errors, SQL Configuration Tagged with ,

← Older posts