Best practice bring MS SQL database to remote sites Currently we use terminalservers to enable remote users to work with a central LOB-application. The terminalserver is on the same LAN as the MS SQL-server.

Now my question is: what is the preferred way to replicate/mirror the database to the remote sites, so users don't need a terminalserver and can work in their local LAN.

These are my concerns:

SQL over internet is often too slow. It would however be my preferred solution because of simplicity (if internet doesn't work this will only temporary effect clients, no worry about shipping log files, etc)
Mirror will be removed in a future version of Microsoft SQL Server. Afaik, it leaves the mirror constantly in restore-mode so the LOB-application can't actually use, correct?
Always On availability groups look very complicated to me but they do support Active Secondary Replicas which sounds like the ability that every remote site can use it's local replica for reading and the central-db for writing. However, I wonder, does this work with all LOB-applications? And does this require a very fast and stable internet-connection? It must not fail with every short internet/VPN hick-up.

More info:

I'm hoping for a general solution that works with 'all' Microsoft SQL-based applications (SQL over internet does when internet is fast and queries are limited).
Data needs to be read/write on remote sites. It's okay if data can't be modified in case of a broken internet/VPN connection. I was hoping that mirroring could do this. That modifications are committed to the principal-db but all reads are directly read from the mirror (transparant to the application / without using 2 different db-servers). A bit like domain-controllers work. This isn't how mirroring works however.
A delay of minutes would be acceptable in most LOB-applications if there is no risk off sync conflicts. I can imagine that if you work with orders, timesheets, etc. it doesn't matter if your data is a bit delayed because you are accessing it from another country.
Assume the vendor doesn't provide a solution. I won't say they don't support it. I asked one and they didn't have plans for such a system. Their solution was to move all infrastructure (servers + desktops) to the 'cloud'. But it's just MS SQL, so I'm hoping for a better solutions. With terminal-server you can have a slow user experience with everything you do. It would be better if you just have a (small) delay in your LOB-application only, not when visiting a website or scrolling a PDF.

Any recommendations? Or is a central terminal-server the only way?

Thanks in advance.

This is exactly what replication is designed for.  Sounds like Transactional replication with updateable subscriptions will work although MS is saying this will be removed down the road (no specifics)

Merge replication is also a choice, but is more complex to maintain.


How to start LaunchPad service of SQL Server on Ubuntu? I'm running SQL Server 2017 on Ubuntu 16.04.

When trying to run an external Python script, I got the following error:

Msg 39011, Level 16, State 1, Line 0
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

I searched and found out that the solution on Windows is to start the service of LaunchPad with Windows' service manager.

But I need the equivalent of Ubuntu. I don't even know what the LaunchPad service is called.

Linux Support Roadmap for SQL Server Machine Learning Services:

"Machine learning using R or Python in-database is not currently supported in SQL Server on Linux. Look for announcements in a later release.

However, on Linux you can perform native scoring using the T-SQL PREDICT function. Native scoring lets you score from a pretrained model very fast, without calling or even requiring an R runtime. This means you can use SQL Server on Linux to generate predictions very fast, to serve client applications."

The stand-alone Machine Learning Server is already supported on Linux.


Access query to locate changed records I have a database of specimens for a natural history collection. The scientific names for the specimens have changed since the last name update ~10 years ago. The actual specimen entries are recored in a catalog that references unique IDs in Names to show the identification. I updated the names and IDs (which are formulated from the names) in Microsoft Access and I now have three spreadsheets: Names, OldNames, and Catalog.

[Names] structure
    AllID NewID AllID Name

[OldNames] structure
    ID Name

[Catalog] structure
    ID (could be an AllID or NewID) Specimen#

Now I need to find all the names that changed between AllID and NewID, locate the corresponding specimen by number, and physically change the label. My current code is to run a query with [Names].[AllID] linked to [OldNames].[ID] and [Catalog].[ID].

Field: AllID
Table: Names
Criteria: Not[Names].[NewID]

I also attached [Catalog].[Specimen#] so that I can locate the actual specimen.

The problem is that the query does not retrieve all of the changed fields. I noticed this because a physical specimen with an old ID was not on the list created by the query. Should line of code

Criteria: Not [names].[NewID]

return all IDs that were updated? If not, what other code should I use?

All help is appreciated, including a link to the same question asked by another user. This seems quite simple and basic but I'm unable to find an answer so far.

I located the error: My joins were incorrect and the operation cannot be performed in Design View. I had to rewrite the joins in SQL view.

The original code:

SELECT OldNames.ID, Names.NewID, Catalog.Specimen#,
FROM Catalog INNER JOIN (OldNames INNER JOIN Names ON OldNames.ID = Names.AllID)
ON (Catalog.ID = Names.NewID) AND (Catalog.ID = Names.AllID)

Replace 'AND' with 'OR' to have the code function properly (although it can only be executed from SQL view)

SELECT OldNames.ID, Names.NewID, Catalog.Specimen#,
FROM Catalog INNER JOIN (OldNames INNER JOIN Names ON OldNames.ID = Names.AllID)
(Catalog.ID = Names.NewID) OR (Catalog.ID = Names.AllID)


Does a user who has permissions to select from a view also need select granted to the children entities of that view? If view 1 uses tables A and B, and a user has SELECT granted on view 1 is that sufficient for the user to select from view 1 or does he also need SELECT granted on tables A and B.

Specific details on my situation (in regards to Erik's comment):

I thought I understood permissions, but I'm a little confused right now.

I have a user specifically for testing permissions. I have view 1 in database 1 that references view 2 in database 2 and view 3 in database 2. View 2 in database 2 references table A in database 3. View 3 in database 2 references table B in database 2.

So at the individual entity level, I slowly was adding permissions as necessary and check with my test user to see what error I get back from SSMS.

I first granted select permissions to the test user on view 1. Then based on the errors I was getting, I also needed to grant select on view 2 and table A (that is referenced by view 2). But I did NOT need to grant select permissions to my test user on view 3. This is where I'm confused.

I then assumed my test user might be part of a role (or something) that gives it full select access on database 2, but then if I try to select directly from view 3 it gets a permission denied error, even though it CAN select from view 1 (which references view 3).

TL;DR: Why can my test user account select from a view but can't directly select from one of the entities that that view is dependent on?...is that normal permissions behavior?

Generally speaking, sub-objects owned by the same owner of the object that one has permission to will pass along that permission. This is Ownership Chaining. By default this behavior is constrained to working within the current database, and with non-dynamic SQL. When you start involving other databases and/or dynamic SQL, then you need some additional configuration to allow that to work (that is, if you don't want to grant direct access to those sub-objects).

The simple / easy mechanisms are to enable Cross-Database Ownership Chaining and/or TRUSTWORTHY (database property), depending on what you are trying to do. However, being simple and easy also means that they both open up rather large security holes.

Fortunately, there is a way to accomplish this with just a little bit of extra work, but the end result will be a much more secure setup. You want to use Module Signing. The only down-side is that you might need to change 1 or more of the Views into Multi-statement Table-valued Functions so that the object can be signed (Views and Inline TVFs cannot be signed). However, the security itself is much cleaner since it is highly specific and self-contained. You will create Certificate-based Users that have the permissions that you want, in each DB that you want them to be extended to (this is done by re-creating the same Certificate in each DB). Then you just need to grant the actual Login / User the EXECUTE permission to the main object.

For a full example of how to do this, please see my answers here:

Stored procedure security with execute as, cross database queries, and module signing
Access view based on table in another database without account in that other database
Permissions in triggers when using cross database certificates

And for more info on why you should use Module Signing instead of Cross-DB Ownership Chaining and/or TRUSTWORTHY, please see my post here:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


How to retrieve a large text from inside a table? for my own records I have a table in one of my servers where I like to save my activities and scripts

the table definition is:

IF OBJECT_ID('[dbo].[activity]') IS NOT NULL
DROP TABLE [dbo].[activity]
GO
CREATE TABLE [dbo].[activity] (
[dt]        DATE                             NOT NULL,
[i]         SMALLINT                         NOT NULL,
[activity]  NVARCHAR(max)                     NOT NULL,
[script]    NVARCHAR(max)                     NULL
           CONSTRAINT [DF__activity__script__6E01572D] DEFAULT (NULL))

GO
CREATE CLUSTERED INDEX [pk_activity]
   ON [dbo].[activity] ([dt] desc, [i] desc)

as this table is located in the DBA database on my DBA_SERVER server,
from my local machine I created a linked server to the DBA_SERVER so that I can insert scripts into my table.

this is how I insert a script into my table, the script is located on my local machine:

    INSERT INTO [MY_DBA_SERVER].[DBA].[dbo].[activity]
               ([dt]
               ,[i]
               ,[activity]
               ,[script])
    SELECT
               DT=cast ('3 may 2018' as date)
               ,I=1100
               ,ACTIVITY='MOVE APPLICATION ROWS - the Ids work'
               ,[script]=BulkColumn
    FROM Openrowset( Bulk 'C:\Users\MMiorelli\Downloads\applicationID consolidated.sql',
                     Single_Blob) as [the script]

and this seems to be working fine, because when I run the select below:

SELECT [dt]
      ,[i]
      ,[activity]
      ,[script]
  FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]
GO

I can see that the stuff I have just inserted is on my table.

and when I run the following script I find out how much data is in each field:

SELECT [dt]
      ,[i]
      ,[activity]
      ,[script]
      ,len_activity=DATALENGTH( [activity])
      ,len_script=DATALENGTH( [script])
  FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]
GO

and that gives me this:

My problem is that I cannot retrieve the whole content of my script.
I have tried to copy it using the mouse as you can see on the picture below:

But I have some hairy scripts that are very long, and copying like that cuts them short of their whole.

my question is:

How to retrieve this script from my table?

it can be via select or even saving it to a file,
I would prefer a T-SQL way of doing it if possible.

Just a curiosity
would that be the same way for images?

There's another way similar to Aaron's, using the little-documented processing-instruction... thing.
Using this gives you unchecked XML, which will won't replace predefined entities.
Also, the column will have XML identifying marks at the beginning and end, which may be another drawback depending on how you want to use the output.
But it'll be clicky and show you the whole thing!
CREATE TABLE dbo.LongText (
    Id INT IDENTITY,
    LongText_N NVARCHAR(MAX),
    LongText_X XML
    );

INSERT dbo.LongText ( LongText_N, LongText_X )
SELECT asm.definition, (SELECT asm.definition AS [processing-instruction(_)] FOR XML PATH(''))
FROM sys.all_sql_modules AS asm

SELECT * FROM dbo.LongText AS lt


mysql index on varchar and date field for improve query speed of select and insert query CREATE TABLE IF NOT EXISTS `gsm_smslog220717`
(
    `pdsid` int(11) NOT NULL auto_increment,
    `affiliateid` varchar(20) character set latin1 default NULL,
    `bpushno` longtext character set latin1,
    `psms` varchar(1) character set latin1 default NULL,
    `credits` varchar(5) character set latin1 default NULL,
    `mobileno` longtext character set latin1,
    `dlrid` longtext character set latin1,
    `dr` varchar(50) character set latin1 default NULL,
    `op` varchar(10) character set latin1 default NULL,
    `dtoken` varchar(200) character set latin1 default NULL,
    `sid` varchar(15) character set latin1 default NULL,
    `char` varchar(5) character set latin1 default NULL,
    `gid` varchar(20) character set latin1 default NULL,
    `msg` varchar(700) character set latin1 default NULL,
    `status` varchar(1) character set latin1 default NULL,
    `time` varchar(10) character set latin1 default NULL,
    `subdt` date default NULL,
    PRIMARY KEY  (`pdsid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2444466;

SELECT Query:

select  *
    from  gsm_smslog220717
    where  affiliateid ='hn137754388'
       && subdt = '2017-08-21'
       && status= 's'

How can I improve speed of query? In one second many insert queries are also run on live.

Please help to create best index for this query.

MyISAM and Many insert queries ?

I definitely recommend to use InnoDB instead of MyISAM. (Don't forget to create backup before change storage)

ALTER TABLE gsm_smslog220717 ENGINE = InnoDB;

If most of your queries for gsm_smslog220717 table like that, you can create composite index for speed up Select query.

ALTER TABLE `gsm_smslog220717` ADD INDEX `affiliateid` (`affiliateid`, `subdt`, `status`);


SELECT 1 Start, t1.time `Start Time`, 0 Stop, MIN(t2.time) `Stop Time`
FROM src_table t1
JOIN src_table t2 ON t2.time > t1.time AND t2.ign = 0
GROUP BY t1.time
WHERE t1.ign = 1


How do I resume log shipping? I was log shipping. I disabled the jobs, and restored the database on the secondary machine and used them for a bit. Now I want to undo this. I don't care about history or syncing, I simply want to turn log shipping back on and carry on my merry way.

I enabled the jobs, but 'running' them (right click > start job at step, if that's how you run them) just pops up and says success but nothing happens.

I am unsure of how I can start the log shipping going again. I tried deleting the databases from the secondary machine but that didn't help either.

Any ideas? Surely it's a simple thing?

Edit: Still at a loss. Says the job was successful but nothing happens, no database is created. It was working fine before I took the databases out of recovery on the secondary machine.

You need to take another full backup from the production database and restore it to the log shipping server in NORECOVERY mode.  Then the log shipping should be able to restart.  You may need to remove the log shipping and put it back by going through the wizard depending on how you set it up.


doctor appointment database design I have an application for doctors . pateints should can set appoint time .
I designed this :

an example :

date : 2016/22/8 has some times like : 09:00 , 09:45 , 14:30 users can selec on of them

is this design good ?

You need to consider one more parameter, if you are doing it for multispeciality hospital, you should add a

Doctor table,

Department table, also

Doctor schedule (ex: Mon-Wed 10 am to 2pm, Thus-Sat 12 pm to 4pm )


Measuring Always On Availability Group potential data loss I have read these articles few times. I do monitor potential data loss today using some of the methods described in these articles.

Measuring Availability Group synchronization lag by Derik Hammer
Monitor Performance for AlwaysOn Availability Groups
Monitoring Availability Group Replica Synchronization by Jonathan Kehayias

Reading these articles and the description of dynamic management views and performance counters, I do not see any way to measure the exact time for potential data loss if Primary goes down now.
I did inquire about using log sequence number (LSN) values from sys.dm_hadr_database_replica_states. Found out only certain LSN's has a timestamp. Plus, continuously querying the timestamp of those particular LSN is probably not a practical solution.
Do you know how to get the exact time for potential data loss at any given time?
A hypothetical solution will be finding the oldest LSN's timestamp that has not been copied to the secondary.

I've occassionally created specific tables for this purpose, sometimes called "tracer" or "canary" tables. It has one column which is a datetime. A job runs on the source system inserting the current time. This runs at whatever granularity you need - minutes apart was sufficient for me. Read the max value from the secondary & that's how far behind you are.


Is my db too big for my server? Should I go nosql or stick with relational db management? I have a mysql database that is about 22GB in size and has multiple tables with hundreds of thousands of records, my biggest table has 1.8 million records. The Database was not professionally designed by a DB admin, it was designed by a php developer with not a lot of experience in dealing with that much data. I know its not the largest amount of data but it is giving is very slow performance, some of it is the code written.

My question is, does my system currently support the DB of that size? I have a dedicated managed server from go-daddy, 32GB of RAM, quad core processor, 1TB of storage. We have about 100 users actively using our application looking for a single record within a specific table.

I am thinking about restructuring the DB and also going into cloud instead of dedicated server and going with nosql db management rather than our current relational.

However I am not a DB admin, I am a developer. I would love to hear your thoughts.

I would like to echo @Thronk 's remarks about not having enough information to fully answer your question.

However, take a look at this article by a guy who sold his business to eBay and is still senior there.

This suggests to me that you would be far better off redesigning/refactoring your database according to proper relational principals rather than jumping on the NoSQL bandwagon which will involve more work and greater risk. As @Thronk points out, 22GB is not a large system.

If you're using 5.6, try using the performance_schema (p_s) to pick the low-hanging fruit first, which may give you the breathing space you need to properly redesign.

Don't be afraid to come back here with design questions and problem SQL - don't forget that the more detail you put into your question, the better your answers will be. It may be that some reindexing may help - again, don't hesitate to ask here. p.s. welcome to the forum!


Multiple values in lookup fields I know newer versions of MS Access allow multiple selections in lookup fields.

For example, if I'm collecting information on my students, I can have a field called Race with lookup values that include White, Black, Hispanic, Asian, etc.  and then set that to allow multiple selection.

In term of db design, is it better to use this feature of Access.... OR to have a field for each Race category and then True/False values for each one?  And maybe even have this in a separate table?

--edit

I'm asking my question about the table, not the form. I realize the front-end display is not identical to the back-end strucure. Is it better to have a bunch of true/false fields in the table OR just one lookup field that allows multiples? (knowing the form is the best place to define the actual lookup values)

For a single student, why would you have multiple selection for race? This for the situation you mentioned as example.

I'd see both options valid (lookup with multiple selection or radio buttons with true-false) depending on the situation you need to display. What you show in the front is not identical with what tables you have in the database. Do your tables to fit simple normalization rules and then just build queries and views to display in the Access UI. Read about normalization and normal forms and design your tables accordingly (I prefer by default 3rd normal form, but it's not mandatory, you could increase/decrease the form level as you see fit).

I think that this tutorial is done with Access users in mind.

You can find some good free data models here: Free data models. Some of them even have attached Access databases.

PS: more of a comment than an answer, but too verbose.


Log shipping retention question One of the secondary's is failing log shipping restore due to insufficient drive space.  This might not be looked at by IT for several days.   Can log shipping primary server purge transaction log backup before secondary has a chance to restore in this scenario?  Or does Log shipping primary just hold onto transaction log backups until all secondary's have restored it?

The primary server must retain all transactions until the secondary restores them and acknowledges to the primary.
You must constantly monitor both the primary and secondary servers to avoid this situation.


Change DEPENDENT SUBQUERY to JOIN I have the following query. It calculates the best match for each row in flight based on the the data in airport

SELECT flight.id,
       (
           SELECT id
           FROM   airport
           WHERE  SQRT(
                      POW(69.1 * (latitude - flight.lat), 2) + POW(69.1 * (flight.lon - longitude) * COS(latitude / 57.3), 2)
                  ) < 10
           ORDER BY
                  ABS(
                      (magnetic -180) -ABS(
                          atan2(TAN(longitude - flight.lon), TAN(latitude -flight.lat)) * (180 / (22 / 7))
                      )
                  ) ASC LIMIT 1
       ) AS airport_id
FROM   flight
WHERE  STATUS = 1

Is there a way to remove the DEPENDENT SUBQUERY and change it to a JOIN? I can even put all the lat/lon in the same table without much effort using UNION.

SELECT id,
       lat,
       lon,
       NULL AS latitude,
       NULL AS longitude,
       NULL AS magnetic
FROM   flight
    UNION
SELECT id,
       NULL,
       NULL,
       latitude,
       longitude,
       magnetic
FROM   airport

I can't see why it would be useful to perform the UNION you describe (and, assuming flight.id and airport.id are the respect primary keys of those tables, there's no reason not to do a UNION ALL, avoiding checking for duplicates). To be specific, it seems like you'd be dumping the data from the two separate tables into one (in effect) separating them back out into flight and airport in the self-join.

To shift the sub-query into a JOIN, we'd also need a way to select the highest ranked airport returned. We'd wind up with something like this:

  SELECT id
        ,airport_id
  FROM   (
          SELECT flight.id
                ,airport.id as airport_id
                ,@RowNum := CASE WHEN @PrevFlightID = flight.id AND @PrevAirportId = airport.id
                              THEN @RowNum + 1
                              ELSE 1
                            END as rank
                ,@PrevFlightID := flight.id
                ,PrevAirportID := airport.id
          FROM   flight
                   INNER JOIN airport ON SQRT(
                                                POW(69.1 * (airport.latitude - flight.lat), 2)
                                              + POW(69.1 * (flight.lon - airport.longitude) * COS(airport.latitude / 57.3), 2)
                                             ) < 10
                   CROSS JOIN (SELECT @RowNum := 0
                                     ,@PrevFlightID := 0
                                     ,@PrevAirportID := 0
                              ) v
          WHERE  flight.STATUS = 1
          ORDER BY
                 ABS(
                      (airport.magnetic -180)
                     -ABS(atan2(TAN(airport.longitude - flight.lon), TAN(airport.latitude -flight.lat)) * (180 / (22 / 7)))
                    )
         ) ranked_data
  WHERE rank = 1
  ;

which, of course, still has a subquery, to filter out the rows we don't want. If I were you, I'd run some tests with both options, and see if one performs notably better than the other.  If not, I suspect I'd use what you already have; in my opinion, it's a little easier to follow.

(Note: code untested)

FYI: given that you have to plug the values from the tables into a somewhat complex equation in order to match them up, it's unlikely that adding indexes or anything like that would help in the matching process. If either table is relatively wide (has many more columns than the ones needed in the query), you might get better performance by creating covering indexes (indexes that have the columns this query needs; the engine can use that instead of the table, and probably will if the index has notably more rows per page than the table itself).

If this information is required frequently, but the flight and airport tables are not updated constantly during the day, then you might save the results of the query to a table. This would work best if the flight table is updated nightly (I assume the airport table is basically static), for example. Note that if the flight table is updated off the normal schedule, the saved results would need to be recomputed.


What could be the reason for shrinking the transaction log file before and after the database backup? I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who originally set things up are no longer available.
Server version: Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)
Jun 15 2019 07:56:34
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)
One of the first things that came to my attention when I looked at the server was an SQL Server Agent job that makes a backup of the main database. The database is in Simple recovery mode. The job has three steps:
-- step 1
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

-- step 2
declare @disk varchar(100) = replace(N'G:\DB_Backup\DWH_Main_&d&.bak','&d&', convert(varchar, getdate(), 112))
--print @disk
;
BACKUP DATABASE [DWH_Main]
    TO  DISK = @disk
    WITH  RETAINDAYS = 2
        , NOFORMAT
        , NOINIT
        ,  NAME = N'DWH_Main-Full Database Backup'
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , COMPRESSION
        ,  STATS = 10
GO

-- step 3
WAITFOR DELAY '00:05'
;
DBCC SHRINKFILE (N'DWH_Main_log' , 0)

The data file size is about 400GB, the log file size right now is about 50GB.
CREATE DATABASE [DWH_Main]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'DWH_Main', FILENAME = N'E:\Data\DWH_Main.mdf' , SIZE = 411114496KB ,
  MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'DWH_Main_log', FILENAME = N'F:\Log\DWH_Main_log.ldf' , SIZE = 53941184KB ,
  MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

I can see A LOT of Log File Auto Growth events, which is no surprise. DBCC LOGINFO returns 858 rows, i.e. 858 fragments, almost each of them 64MB.
What should I say to my boss about this situation? I don't understand why anyone would want to shrink the transaction log file every day after a full backup, especially for a database in a simple recovery mode.
My first reaction was to simply remove the DBCC SHRINKFILE commands from the script and then re-configure the initial size of the transaction log file. Grow it manually in 8GB chunks to ~64GB to reduce the fragmentation, but maybe I'm missing something?

Most likely the one who set up that configuration wasn't very experienced with SQL Server.
Or, disk space was extremely valuable, and even though you only reclaim some disk space before it (soon) grow again, the costs and disadvantages of doing shrink was considered lower priority than cost for disk space. Not likely, but a theoretical possibility.
Why a shrink both before and after the full backup? Beats me. A full backup does a checkpoint, which will empty the log (being in simple recovery), so doing the shrink after the full backup would be the reasonable thing to do. If one could use the word "reasonable" when we're talking about regular log shrinking.
I would remove the shrink and add some monitoring. I.e., poll the size of the log every 5 minutes and see if/when it grow.
And, of course, as you state, grow it to some reasonable size. That should probably be your high watermark file size, if you have that information available. Possibly the size before that first shrink - exceptional happenings in your database not counted...


How to copy & paste "Missing Index" in "Execution plan" tab of SSMS? Notwithstanding that blindly creating suggested missing indices is less than ideal, is there a way to copy and paste the suggested missing indices from the "Execution plan" tab in SQL Server Management Studio?

You get a tool-tip on mouse hovering over it but there does not appear to be a right-click menu copy them.  For years I've just typed out any I needed but am curious if the base form can be easily gotten from the tab.

Using SSMS 2017

You can Right-Click on the green text showing the suggested index, select "missing index details" which will open a new tab in SSMS with the index create script.


Feasibility of Automated and Continuous Performance Tuning in the future First a quick background: I'm a software developer, not a DBA and have been developing for more than 10 years. Recently I had to optimize a stored procedure and had to run execution plans, create/modify indexes, update statistics, etc which is a tedious task.

My question is very open, but I'd like to know if there is already a DBMS that automatically and continuously tunes itself (create/modify indexes, update statistics, etc) based on the data that lives in the database and its usage? If there isn't anything like that would we expect to see automated systems like that in the future? Given the increasing rate at which we're accumulating data these days seems like it will be a must in the near future.

What are the current impediments or negative side of doing that?

I understand that modifying the queries and stored procedures to be optimal may be hard to automate an more of a human task. But I believe good level of tuning automation could be done on the database side.

Perfect tuning is an art and the underlying system (meaning the data and its usage patterns) is almost always evolving in ways automation can't predict or react to. Tuning is also based on past activity; the system can't predict tomorrow's growth or usage patterns, but a DBA can to some degree. We're a long way away from decent artificial intelligence and I highly doubt this will be one of its first applications when it does arise.

For a while, SQL Server was marketed as a self-tuning system, "No DBA required." I'm not sure that's a good thing even if it were possible, but it's certainly not marketed that way today and it doesn't even seem to be a secondary or tertiary goal of Microsoft. Even the new Query Store feature in 2016 is designed to allow you to make decisions about query plans and regressions, not to make them for you. As a monitoring company we, too, strive to provide you with the information for you to make decisions, and try to make the pain points extremely obvious, but we stop short of telling you to change index x and tune query y.

Think about self-driving cars for a moment - while they may reduce accident rates slightly overall, they're going to put millions and millions of people out of work, from cab drivers to delivery people to truckers... never mind that software can't think like a human. I like cruise control, but it can't predict hills or know that I'd like to pass this truck at a slightly faster pace, so I don't always rely on it. That's pretty mature technology but it's far from perfect, even in its most advanced implementations (like maintaining a certain distance from the car in front of you, adjusting for its speed - I don't know if that's a good thing, either).


library data modeling I'm going to implement a library system as a web application. And I decided to use this library schema. But there are some problems which I don't understand.

The Books entity, is it possible to use isbn as a primary key?
Is there a possible relationship between the Books entity and the Libraries entity? Is it a correct many-to-many relationship? I think that any book must be in only one library. So it is considered that one-to-many is more appropriate.

Your model completely ignores the fact that books come in multiple copies.

Each copy of a book can only be in one place at one time, but each place could have multiple copies of any given book.

Since each copy of a book will have exactly the same ISBN, the answer to your first question is: "No, ISBN is not a sufficient primary key for Books."

What you need to account for is a Title entity.  This is going to be a combination of what you're thinking about for your Books entity.  A Title is an abstract object.  It will have a title, authors, publication date, and so forth.  You might even want to consider making a distinction between Title and Edition, since a book can be republished many times with variations each time.

Whether or not you want to distinguish between editions of a title, you must at least distinguish between Title and Books.  The answer to your second question is that each physical book will be in one place (either at a Library or on loan to a Member - or in transit between branches, or lost, etc. - depending on how sophisticated you want your model to be).

On the other hand, Titles, (or Editions) will be many-to-many with locations.


Calculate number of days since from timestamp I have a column in my database table containing timestamp.

SELECT id, millisecs, to_timestamp(millisecs) AS date_time FROM my_table;
id      | millisecs  |       date_time
--------+------------+------------------------
 126952 | 1494161691 | 2017-05-07 13:54:51+01
 126952 | 1494161692 | 2017-05-07 13:54:52+01
 126952 | 1494161693 | 2017-05-07 13:54:53+01
 126952 | 1494161694 | 2017-05-07 13:54:54+01
 126952 | 1494161695 | 2017-05-07 13:54:55+01

Then I want to calculate the number of days since '1899/12/30' using either the millisecs or date_time columns. As my table has large number of rows, doing this with a python function in notebook kills the kernel after sometime.

You can do this:

SELECT DATE_PART('DAY', date_time - '1899-12-31 00:00:00+01'::TIMESTAMPTZ)
FROM date_test;

(typo in previous answer - had 1899-01-01 instead of 1899-12-31.)

See the dbfiddle here:

Just also note in the 2nd last field in the fiddle (added):

SELECT TIMESTAMP 'epoch' + 1494161691 * INTERVAL '1 second';

Result:

epoch_date
2017-05-07 12:54:51

Which is the date in the main question.

So, it would appear that your field called millisec is, in fact, seconds from 1970 (aka UNIXTIME or similar in other RDBMS). Check out this link. Unless you are storing milliseconds, then I would strongly suggest that you shouldn't call it millisec.

Create and populate table:

CREATE TABLE date_test
(
  id INTEGER NOT NULL,
  millisecs INTEGER NOT NULL,
  date_time TIMESTAMPTZ NOT NULL
);

INSERT INTO date_test
VALUES
( 126952 , 1494161691 , '2017-05-07 13:54:51+01'),
( 126952 , 1494161692 , '2017-05-07 13:54:52+01'),
( 126952 , 1494161693 , '2017-05-07 13:54:53+01'),
( 126952 , 1494161694 , '2017-05-07 13:54:54+01'),
( 126952 , 1494161695 , '2017-05-07 13:54:55+01');

Then run the query above - result:

date_part
    42861
    42861
    42861
    42861
    42861

Links here and here helped!


Arithmetic overflow error If I run this code:

select 99999 * 9999

result = 999890001

If I run this code:

select 9999923 * 99999999

result:

  Msg 8115, Level 16, State 2, Line 1
  Arithmetic overflow error converting expression to data type int.

And If I run the following code:

select 922337299987987689745893 * 999999994564

result = 922337294974162127011191918541325652

Why am I getting this arithmetic overflow error within a specific range?

The rules that SQL Server uses to derive the type of literals are complex, with some odd behaviours that are maintained these days for backward compatibility. For example, the derived type may depend on whether simple parameterization is applied. There are other (undocumented) considerations as well.

With those caveats out of the way, the situation you face is one of the simpler ones. The untyped literals are typed as integer when they will fit in that data type. The data type of the result is as specified in * (Multiply) (Transact-SQL):

  Returns the data type of the argument with the higher precedence.
  For more information, see Data Type Precedence (Transact-SQL).

The two data types are both integer (with obviously the same precedence), so the result is also typed as integer. When the result does not fit, an error may be thrown (depending on settings).

When one of the literals is explicitly typed using CAST or CONVERT as (for example) bigint (which has a higher precedence than integer) the result type will be bigint, and no error occurs.

The two larger constants in the question are typed as numeric(24,0) and numeric(12,0) respectively. The result of multiplying them is typed as numeric(37,0) in accordance with the rules for e1 * e2 shown in Precision, Scale, and Length (Transact-SQL):

Result precision: p1 + p2 + 1 = 24 + 12 + 1 = 37
Result scale: s1 + s2 = 0 + 0 = 0

Result type: numeric (37,0)


Append duplicate records with count (mysql) We need to update a set of titles that have some duplicate entries. Because of the size of the database I would prefer to use direct mysql method, but I am open to any solutions out there.

If we have records (url_title):

a-stoney
a-stoney
ab-dille
ab-dille
ab-dille
ac-gifta
ae-jones
ae-jones

I want to be able to run against the table and end up with ONLY the duplicate url_titles updated as follows:

a-stoney
a-stoney-1
ab-dille
ab-dille-1
ab-dille-2
ac-gifta
ae-jones
ae-jones-1

Or some similar output, where non-duplicate url-titles are not edited, and any duplicates are applied a similar convention

Table syntax:

CREATE TABLE `exp_channel_titles` (
  `entry_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(4) unsigned NOT NULL DEFAULT '1',
  `channel_id` int(4) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL DEFAULT '0',
  `forum_topic_id` int(10) unsigned DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL DEFAULT '0',
  `title` varchar(100) DEFAULT NULL,
  `url_title` varchar(75) NOT NULL,
  `status` varchar(50) NOT NULL,
  `versioning_enabled` char(1) NOT NULL DEFAULT 'n',
  `view_count_one` int(10) unsigned NOT NULL DEFAULT '0',
  `view_count_two` int(10) unsigned NOT NULL DEFAULT '0',
  `view_count_three` int(10) unsigned NOT NULL DEFAULT '0',
  `view_count_four` int(10) unsigned NOT NULL DEFAULT '0',
  `allow_comments` varchar(1) NOT NULL DEFAULT 'y',
  `sticky` varchar(1) NOT NULL DEFAULT 'n',
  `entry_date` int(10) NOT NULL,
  `year` char(4) NOT NULL,
  `month` char(2) NOT NULL,
  `day` char(3) NOT NULL,
  `expiration_date` int(10) NOT NULL DEFAULT '0',
  `comment_expiration_date` int(10) NOT NULL DEFAULT '0',
  `edit_date` bigint(14) DEFAULT NULL,
  `recent_comment_date` int(10) DEFAULT NULL,
  `comment_total` int(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`entry_id`),
  KEY `channel_id` (`channel_id`),
  KEY `author_id` (`author_id`),
  KEY `url_title` (`url_title`),
  KEY `status` (`status`),
  KEY `entry_date` (`entry_date`),
  KEY `expiration_date` (`expiration_date`),
  KEY `site_id` (`site_id`)
) ENGINE=MyISAM AUTO_INCREMENT=181332 DEFAULT CHARSET=utf8;
    enter code here

From the sake of this question, I will call the source table aaronlax

PROPOSED SOLUTION

SET @ndx = 0;
SET @cur_hash = '';
SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
FROM (SELECT
   @new_hash := MD5(url_title),
   (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
   @cur_hash := MD5(url_title),
   url_title,entry_id
FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;

SAMPLE DATA

use test
DROP TABLE IF EXISTS aaronlax;
CREATE TABLE aaronlax
(
    entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    url_title varchar(75) NOT NULL,
    PRIMARY KEY (entry_id),
    KEY (url_title)
) ENGINE=MyISAM;
INSERt INTO aaronlax (url_title) VALUES
('a-stoney'),('a-stoney'),
('ab-dille'),('ab-dille'),
('ab-dille'),('ac-gifta'),
('ae-jones'),('ae-jones');

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS aaronlax;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE aaronlax
    -> (
    ->     entry_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->     url_title varchar(75) NOT NULL,
    ->     PRIMARY KEY (entry_id),
    ->     KEY (url_title)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERt INTO aaronlax (url_title) VALUES
    -> ('a-stoney'),('a-stoney'),
    -> ('ab-dille'),('ab-dille'),
    -> ('ab-dille'),('ac-gifta'),
    -> ('ae-jones'),('ae-jones');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from aaronlax;
+----------+-----------+
| entry_id | url_title |
+----------+-----------+
|        1 | a-stoney  |
|        2 | a-stoney  |
|        3 | ab-dille  |
|        4 | ab-dille  |
|        5 | ab-dille  |
|        6 | ac-gifta  |
|        7 | ae-jones  |
|        8 | ae-jones  |
+----------+-----------+
8 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @ndx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_hash = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT entry_id,CONCAT(url_title,IF(ndx>0,CONCAT('-',ndx),'')) url_title
    -> FROM (SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA) A;
+----------+------------+
| entry_id | url_title  |
+----------+------------+
|        1 | a-stoney   |
|        2 | a-stoney-1 |
|        3 | ab-dille   |
|        4 | ab-dille-1 |
|        5 | ab-dille-2 |
|        6 | ac-gifta   |
|        7 | ae-jones   |
|        8 | ae-jones-1 |
+----------+------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT

In case you are wondering why the proposed solution works, let's take a look at the subqueries

INNERMOST SUBQUERY

mysql> SELECT url_title,entry_id FROM aaronlax ORDER BY url_title;
+-----------+----------+
| url_title | entry_id |
+-----------+----------+
| a-stoney  |        1 |
| a-stoney  |        2 |
| ab-dille  |        3 |
| ab-dille  |        4 |
| ab-dille  |        5 |
| ac-gifta  |        6 |
| ae-jones  |        7 |
| ae-jones  |        8 |
+-----------+----------+
8 rows in set (0.00 sec)

mysql>

NEXT LEVEL SUBQUERY

mysql> SELECT
    ->    @new_hash := MD5(url_title),
    ->    (@ndx := IF(@new_hash=@cur_hash,@ndx+1,0)) ndx,
    ->    @cur_hash := MD5(url_title),
    ->    url_title,entry_id
    -> FROM (SELECT url_title,entry_id FROM aaronlax ORDER BY url_title) AA;
+----------------------------------+------+----------------------------------+-----------+----------+
| @new_hash := MD5(url_title)      | ndx  | @cur_hash := MD5(url_title)      | url_title | entry_id |
+----------------------------------+------+----------------------------------+-----------+----------+
| 911adc6db360f218da5069b20cfb1917 |    0 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        1 |
| 911adc6db360f218da5069b20cfb1917 |    1 | 911adc6db360f218da5069b20cfb1917 | a-stoney  |        2 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    0 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        3 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    1 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        4 |
| b468c0dd0ef78fde7cf5ae14765f9a5a |    2 | b468c0dd0ef78fde7cf5ae14765f9a5a | ab-dille  |        5 |
| 3fff0367af8bf5d3b3b0089b8f187c04 |    0 | 3fff0367af8bf5d3b3b0089b8f187c04 | ac-gifta  |        6 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    0 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        7 |
| 1909ba0f7a57ca081c82d2f7ba54c9e5 |    1 | 1909ba0f7a57ca081c82d2f7ba54c9e5 | ae-jones  |        8 |
+----------------------------------+------+----------------------------------+-----------+----------+
8 rows in set (0.00 sec)

mysql>

From this level, I only need three columns: entry_id, url_title, ndx in the outer most level

Then, I append to the url_title a hyphen and ndx if ndx is nonzero


75% of RAM for Innodb_buffer_pool size I have read many documentation including percona and MariaDB documentation where they recommend setting innodb_buffer_pool_size to 70-80% of RAM. My question is, is that total RAM or available RAM? For example, when i run free -m on my CentOS-6.5, i get the following:

[root@master5 ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         19991        999      10992          0         38         92
-/+ buffers/cache:        867      19123
Swap:         1999          0       1999

So is it 75-80% of 19991 or that of 10992?

Total, but remember that is only a rule of thumb. Use common sense when setting the value. InnoDB does like as much memory as possible, but there are a couple things to consider:

1) Make sure there is enough RAM left over for everything else that needs to run on the server

2) Remember that the point of the InnoDB buffer pool is to handle the normal working set of your data.  How large is your database total, including some growth factor?  There's no real need to set the buffer pool much more than that, even if you have the RAM.  But your working set of data (what is used in day-to-day activities) is often lower than the total size of the DB.  How much does it really need to keep in RAM for best performance?


SSIS - sp logs counters in debug mode but not from agent job Within an SSIS project I have a number of usp's that I execute to store row counters, like extracted row count, inserted row count, etc., during an ETL process.

My SSIS project is setup to run via an agent job.
The package runs ok, without any errors, but logs 0 for all my row counts.
When I run the project in debug mode everything logs ok, and the counters are correct.
I therefor presumed it was a permissions issue for the agent job, but everything is being executed ok and the counter logs are being written to, they just aren't being updated.

I ran a trace (forgive me, it was quicker) against the agent job execution and can see that the sp calls are being execute with 0's being passed in. So the agent job, sp's and ssis project are doing what is being asked of them.

Two key points to make:

my row count variables bubble up to a parent package via a script task, and the parent package executes the sp's to log the row counts. But as this works in debug mode, I think this part is ok.
I have set the property ProtectionLevel to EncryptSensitiveWithPassword. This is the first time I've used this property value, and I think it may be stopping the code in the script task from running, when the project is being executed from the agent job.

Can anybody offer some advice on this? Why would the agent job execution not log the row counts, but debug mode would? If this property setting is preventing the script task from running, how do I enable this?

Thanks in advance for any help you can offer.

EDIT:

I don't know if this is of any relevance but this project was built on a dev server and deployed across to the live server, where the SSISDB is and the agent job executes.

What this appeared to boil down to was the TargetServerVersion property for the project. What I hadn't realised was, where SSDT had been upgraded, the TargetServerVersion defaults to 'SQL Server 2017', whereas my target instance is 2016.

I originally tried to "downgrade" the target server version in the original project, but was still getting the same problem. I was also getting some informational messages about the script task in the child package, saying that something hadn't gone as planned when "downgrading". I eventually recreated the entire project with the TargetServerVersion set to 2016 from the beginning. This has done the trick, as all is working as it should.

The only other thing I changed was that I deployed the project using SQL Server 2016 Deployment Wizard, instead of 2017 as before. However, I'm not too sure this would have been the problem.

Moral of the story, always check your TargetServerVersion.

I have provided this as the answer, in case other people experience such an issue. If, in future, any evidence presents itself to indicate that the above was not the cause of the issue, I will gladly change the answer.


SMB 3.0 file share storage limitation What is/are the limitation/s to move from SAN storage to SMB 3.0 on windows Server 2012 - SQL server 2012 enterprise production environment?

I want to know the benefit of using SAN over SMB 3.0 as well ?

keep in mind the following: generally SAN in sthe best option for clustered environments. In non clustered Environment you can opt for locl disks.
Moving from SAN/Local Disk to SMB Windows share can hide some potential problems.

Using SMB network connectivity becomes a very very important piece of your architecture. Probalby you will have to use dedicated nics on database server. You have to consider who/what is sharing disks through SMB. Especially if the "sharing" server is a cheap NAS, you have to pay attention about performance and to the number/weight of all other clients.

The best uses for SMB share are:

build low cost clustered environments, specially when you have lightweight
databases
Test/Disaster recovery/developement environments;
Storing historical data;
Emergecy/temporary storage;
During database migration/upgrade processes.

If you want to use SMB in production environment I think you have to build a strong network infrastructure:

Nics redundancy;
Dedicated nics;
impement traffic isolation (VLAN implementation);
Buy robust NAS solution;
COnsider involving 10GB network interfaces;
Keep under control network performance, check periodically network interfaces counter. Tune parameter like MTU to obtain the best performance for your physical infratructure.


Running several versions of a MySQL database in parallel? We have a central read-only MySQL database that contains geo data, etc. It
is used by various projects. As part of a refactoring, I am thinking about
setting up a system for versioning the database. The rough idea so far:

The current database is version 1. It is available at:

1.database.example.com:3306/database_1

When in the future the database structure is changed in an incompatible
way, then the version number is increased to 2, and the new database is
made available at:

2.database.example.com:3306/database_2

That allows updating projects one by one from interfacing with
1.database.example.com to interfacing with 2.database.example.com. Due
to the different domain name, it is possible to use a different
database server for 2.database.example.com, even one not based on
MySQL.

Does that make sense? Is there a standard or recommended way for “versioning” a
read-only database?

One must separate: What do you want to "version"? The interface or the data?

Versioning the interface

If you only want to version the interface, you should (must) manage one single base of data and provide it in different formats at different interfaces. Otherwise you will run into consistency issues, if your data should change (and even geo data changes -- the rarity of changes makes it even more vulnerable to consistency issues, because this is done so infrequent that humans tend to do mistakes).

In this case I would use one single database and VIEWs for different interfaces. This is harder to setup, but saves headache in the long run.

An alternative would be to replicate the data periodically by a cronjob, which might be a waste of resources if this changes very infrequently.

You could also implement a script and make somehow sure it is always executed if some data changes. (Vulnerable to human errors this is.)

Versioning the data

If you want to version everything including the data, your approach seems fine to me. Although it might be more efficient to just use another database instead of a completely different server instance.


On Duplicate Key Update - million times per day optimization? I am using the following query with different variables millions of times per day, and I am wondering if there is any way to optimize it:

    INSERT INTO campaign_stats (user_id, date, $country)
    VALUES      (:user_id, :date, :country)
    ON DUPLICATE KEY UPDATE $country=$country+1

The ON DUPLICATE KEY UPDATE $country=$country+1 is true in 99% of the time, so it might be better to check for it first?

The table is InnoDB, correct?  (If not, that change would be important.)

If there is an AUTO_INCREMENT column, you will burning most of a million values each day.  Please provideSHOW CREATE TABLE.

That's about 12/second -- not a deadly rate of changes.

Please time the IODKU and time the following, then report back:

UPDATE ... country=country+1;
if rows_affected = 0 then
INSERT ...,

I suspect they will be about the same speed, but I have not tested it.

Oops, I have a subtle bug in that code -- If another thread sneaks in and does the Insert while I am fumbling around with rows_affected, then the Insert could get "dup key".  The fix:  Change the INSERT to IODKU.

If you end up with more than 100/second, consider collecting the values, then patch applying them.  I would probably do a variant on http://mysql.rjweb.org/doc.php/staging_table .


Basic alternative to fn_LocalTimeToUTC My cloud provider has prevented me accessing fn_LocalTimeToUTC and fn_UTCToLocalTime.
How would I go about getting the same functionality that these two functions offer manually and directly in SQL without using these functions?
To try to simplify it further, All our users are in the UK.

The difference between local time and UTC time can be reliably calculated by looking at the difference between GETDATE() and GETUTCDATE(), assuming the server operating system is configured in whatever the desired "local" time is (i.e. if local time for you is Central European Time, then the server must be configured to be in Central European Time).
As an example, consider the following:
GO
DROP FUNCTION dbo.LocalTimeToUTC;
GO
CREATE FUNCTION dbo.LocalTimeToUTC
(
    @LocalTime datetime
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT UTCTime = DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), @LocalTime)
)
GO

DROP FUNCTION dbo.UTCToLocalTime;
GO
CREATE FUNCTION dbo.UTCToLocalTime
(
    @UTCTime datetime
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT LocalTime = DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @UTCTime)
)
GO

SELECT *
FROM (
    VALUES ('2020-09-10T08:47:00')
    )
    v(LocalDate)
CROSS APPLY dbo.LocalTimeToUTC(v.LocalDate) u
CROSS APPLY dbo.UTCToLocalTime(u.UTCTime) l

The output looks like:
╔═════════════════════╦═════════════════════════╦═════════════════════════╗
║      LocalDate      ║         UTCTime         ║        LocalTime        ║
╠═════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2020-09-10T08:47:00 ║ 2020-09-10 13:47:00.000 ║ 2020-09-10 08:47:00.000 ║
╚═════════════════════╩═════════════════════════╩═════════════════════════╝
For clarity, this only works assuming all your clients are in the same time zone, and the server is installed to match your desired "local" time zone.
GETUTCDATE() is supported in all versions of SQL Server since at least 2008.
It's also worth noting that if you use this on datetime values saved in a table, and your local timezone uses daylight savings time, where the difference between local and UTC time varies during the year, you would need to adjust for the difference between standard time and daylight savings time.
A more reliable method of storing datetime data in SQL Server when you're concerned about converting between time zones is to the use datetimeoffset data type instead of datetime, and using the AT TIME ZONE modifier when querying the stored values.
An example of how that works:
DECLARE @dt datetimeoffset;
SET @dt = GETDATE() AT TIME ZONE 'Central Standard Time';

SELECT @dt;

(No column name)
2020-10-19 10:26:37.8100000 -05:00
To get the time at UTC:
SELECT @dt AT TIME ZONE 'UTC';

2020-10-19 15:26:37.8100000 +00:00


Writing Oracle BLOB field to raw file in a Cursor or For loop result empty file I am stuck at this for a while and had been Googling for answer but can't seem to find any....
I have an Oracle 9i table with BLOB field that I need to extract the BLOB field into a raw file on the LINUX server, there are thousand of rows so I need to write a kind of loop to accomplish this.

I got this :

DECLARE
    i    NUMBER := 0;
    l_file      UTL_FILE.FILE_TYPE;
    l_buffer    RAW(32767);
    l_amount    BINARY_INTEGER := 32767;
    l_pos       INTEGER := 1;
    l_blob      BLOB;
    l_blob_len  INTEGER;
BEGIN
    FOR rec IN (SELECT id, fname, fblob  FROM tablename where fname like '%png')
    LOOP

            i := i + 1;
            --DBMS_OUTPUT.put_line ('Record ' || i || ' id ' || rec.id);
            --DBMS_OUTPUT.put_line ('Record ' || i || ' fname ' || rec.fname);

        l_blob_len := DBMS_LOB.getlength(rec.fblob);

          -- Open dest file.
            l_file := UTL_FILE.fopen('BLOBDIR',rec.fname,'w', 32767);

          -- Read chunks of the BLOB and throw them to the file until done.
        WHILE l_pos < l_blob_len LOOP
                DBMS_LOB.read(rec.ATTACHMENT_BLOB, l_amount, l_pos, l_buffer);
                UTL_FILE.put_raw(l_file, l_buffer, TRUE);
                l_pos := l_pos + l_amount;
            END LOOP;
         END LOOP;

      -- DBMS_OUTPUT.put_line ('Procedure Looping Example is done');
   END;

I tested the Loop with the DBMS_OUTPUT and it worked fine. However:

When I ran the code all 10 of the *.png files show up in my BLOBDIR - but the files is empty.
When I ran the code for each individual file (i.e.  FOR rec IN (SELECT id, fname, fblob  FROM tablename where fname ='filename.png')) - I got the file and it has data.
When I ran the code with only 2 files in the loop (i.e. FOR rec IN (SELECT id, fname, fblob  FROM tablename where fname in ('filename.png', 'filename2.png')) - both files got written to the BLOBDIR - but only the first file has data, but the second file show 0 data.

I ran this code through TOAD, and through sqlplus - and same result.  Can someone help me with this ?
Thank you.

This is code that I took from a working Perl script. There is a limit of 32k for using utl_file, last I checked. You probably need to reset the v_curr_idx variable with every loop, otherwise it would write the first file and skip any smaller files.

DECLARE
   v_column_name VARCHAR2(50) := 'my_column';
   v_pkey_val    NUMBER       := 123;
   v_source_code BLOB;
   v_curr_idx    NUMBER       := 1;
   v_max_chunk   NUMBER       := 32760;
   v_lob_len     NUMBER;
   v_raw_bytes   RAW(32760);
   l_output      utl_file.file_type;
BEGIN
-- define output directory
   l_output      := utl_file.fopen( '$db_dir_name', '$file_name', 'WB' );
-- get length of blob
   SELECT dbms_lob.getlength( v_column_name )
     INTO v_lob_len
     FROM <my table>
    WHERE pkey_val = v_pkey_val;

-- save blob length
   v_lob_len := v_lob_len;

-- select blob into variable
   SELECT v_column_name
     INTO v_source_code
     FROM <my table>
    WHERE pkey_val = v_pkey_val;

   WHILE v_curr_idx < v_lob_len
   LOOP
      IF ( v_lob_len - v_curr_idx ) < v_max_chunk
      THEN
         v_max_chunk := ( v_lob_len - v_curr_idx )+2;
      END IF;

      DBMS_LOB.READ(    v_source_code, v_max_chunk, v_curr_idx, v_raw_bytes );
      UTL_FILE.PUT_RAW( l_output,      v_raw_bytes, TRUE );
      UTL_FILE.FFLUSH(  l_output );
      v_curr_idx := v_curr_idx + v_max_chunk;
   END LOOP;

   utl_file.fclose(l_output);
END;
/


Updating large table worked before, now taking hours BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not NULL; they are non-existent, but for reasons beyond my judgement they must be there). So I created essentially a "shell" table for all rows and columns (6 total columns) that should be there- this one is about 80M rows.

PROBLEM: The step I need help with is overlaying the original 30M rows on top of the 80M row table. (All 30M rows have a matching record in the larger table based on the values of three columns, and the remaining three columns, currently null, need to be overwritten with the original data.)

I successfully did this process for a larger dataset (40M/100M tables) and the process took about 4 hours. That's fine- it's a one-shot thing. But when I run it on this smaller data set, it runs for 10+ hours. The first time I got a time out error. I've tried adjusting a few things and running it twice more, but I've cancelled the query after about 10 hours thinking I could try something else.

Any thoughts on why it worked the first time for a larger table, but not on this smaller one? Any thoughts on how I can make the code more efficient or clear out something in a file somewhere?

CODE:

update [shelltable]
set shelltable.column4 = originaltable.column4,
    shelltable.column5 = originaltable.column5,
    shelltable.column6 = originaltable.column6
from shelltable left join original table
  on shelltable.column1 = originaltable.column1 and
     shelltable.column2 = originaltable.column2 and
     shelltable.column3 = originaltable.column3;

Things I've tried since the unsuccessful attempts:

Restarting the SQL Server instance so tembdb clears
Restarting the PC
Reducing the number of columns in the SET clause to 1 (I would re run for each column 4-6)
Update statistics on both tables
Ran DB Tuning Engine, which recommended and created an index on the larger table
Added 'WHERE originaltable.column4 is not null' to exclude writing on some of the rows

The latest attempt is still running after these adjustments and is currently at 9.5 hours. Thanks for your tips and insights!

It might help a lot if you would have listed the indexes you have or the query plan. If the columns 4, 5 or 6 are indexed, that can slow the process down, and having a clustered index on columns 1, 2 and 3 should speed it up.

If columns 4,5 or 6 are variable length (e.g. varchar) then updating new value can cause a lot of page splits, that definitely will make it slower.

I think what John M meant was that if there's a reasonable amount of distinct values in for example column1, you could first collect the distinct into a temp. table and then run the update separately for each of the values because doing the joining the tables in smaller parts is usually a lot faster.

Of course everything depends on several things, like data types, number of distinct values in the columns 1-3 etc.


"on duplicate key update" all the non-key fields mentioned in the insert How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion?

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        fieldname = 'fieldvalue'
        . . .;

You can use the VALUES(column) function:

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        fieldname = VALUES(fieldname)
        . . .;


Count rows in dataset SSRS2012 I want to count the number of rows in my result DataSet, I put in my DataSet a calculated field with this expression :

=CountRows("DataSet1")
But I have the following error message:

"The expression used for the calculated field '=CountRows' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions." What should I do ? thanks

You cannot use an aggregate like CountRows() in a calculated field in your dataset. But you can use CountRows() as an expression in a textbox (alone or within a table) scoped to your dataset. Now that you have your dataset created, you can put a textbox on the report and populate it with the expression =CountRows("DataSet1"), and it will provide the correct answer.

Otherwise, you will need to modify your dataset to include the rowcount. It would seem that using the expression in the report would be the desired option as modiying the dataset would give you a column populated with either a running total or the total rows repeated on each column.


Why can't this mysql user log in with password? In phpmyadmin I created a user called "edward" with password and all privileges:

Yet when I try to log in via phpmyadmin, it says "Cannot log in to the MySQL Server".
When I try to log in on the command line (Windows 7), it won't allow me either:

But if I login in without a password, I get in:

and shows me only two of the tables.
I'm sure the password is correct and I've created multiple users which all can't log in with their passwords no matter how many rights I give them.
How can I get this user to be able to log in only with password and see all databases?

Addendum:
Here is a screenshot from phpmyadmin showing that "edward" has just as many rights as "root":

Because you granted it access without password...
If a user has no password, logging in with one will always fail.

Set the password for user, only then you'll be able to use -p

In addition, you might want to remove the Any user.


Storing many small - and a few large - strings in an RDBMS Let's say I have a table, and (among other things) it needs to store a large number of strings. Most will be small (20-100 characters), but a few will be quite large. Reads will be frequent, writes will be rare.

What is a good general design for this type of table, using a traditional relational database?

Here are some of the things I have been thinking about:

A VARCHAR (let's say VARCHAR(200) and a CLOB field - One will be null, the other will contain data, depending on the size of the value
Keep a VARCHAR column in the main table but store CLOBs in a separate table - Then I can do one of:

OUTER JOIN the tables
UNION the tables
Use two separate queries to bring back data from each table

Keep all of the metadata in the main table, add a type field and store all text data in its own tables Again, this depends on an OUTER JOIN, UNION or multiple queries

The queries to return data from the table are fairly complicated, and I'm concerned that any solution involving multiple queries (or UNIONs) will be expensive to execute - but more importantly - complicated to maintain (i.e. two versions of almost identical queries).

Thoughts?

You failed to mention your DBMS but for Oracle and PostgreSQL you should simply put everything into a single CLOB column.

The small values will automatically be stored "inline" and the large ones will be store out of line anyway. So the DBMS applies the logic of "switching" that you imply with your two column approach anyway.

I would be surprised if other DBMS aren't doing the same thing.


Sending mail using DatabaseMail in SQL Server 2008 I have a profile called GPMail, and previously there was an account called  comments@guitarplayer.com, which was working as expected.

Now I want one more account with the same site, so instead of creating new profile I created new account called password@guitarplayer.com inside the same profile. But now when I try to send mail for password, I do not get the option for the account in the stored procedure -- sp_send_dbmail, so whenever I send the mail, the from field has the comments@guitarplayer.com address instead of password@guitarplayer.com.

Is there anyway that I  can specify the account name too with Profile name while sending mails?

Depending on the method you use for sending mail: Yes

If you use sp_send_dbmail  (which is what I use), then yes, you can set the FROM address in the call to send mail.

See docs:
http://msdn.microsoft.com/en-us/library/ms190307.aspx

@from_address  is what you want, I believe

If you want to actually log in to the mail server as a diff user, then you need to mod the profile or create a new one. The auth to the mail server is configured in the profile.


Please explain what does "for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)')" do in this code In this code I am converting the subjects(columns) English , Hindi , Sanskrith , into rows

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

 select @colsUnpivot
               =stuff(
         (select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)'),
        1, 8, ''
    );

print @colsunpivot

select  @query
              = 'select Name, Subject,Marks
                 from result2
                 unpivot
                        (
                          marks for subject in (' + @colsunpivot + ')) as tab'

exec sp_executesql @query;

Please explain what does for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)') do here .

A common technique for aggregate string concatenation before SQL Server 2017 is using XML data type methods along with STUFF to remove the extra delimiter. In SQL Server 2017 and later, STRING_AGG makes this task much cleaner and easier. The STRING_AGG equivalent:

select @colsUnpivot = STRING_AGG(quotename (C.name),',')
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

The components of the XML expression technique for aggregate string concatenation are as follows.

This subquery will generate a result set with one row per column in the source table with a comma preceding each column name:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

Example result:

,[column1]
,[column2]
,[column3]

Adding the FOR XML PATH(''), TYPE converts these rows into a single strongly-typed XML text node with the concatenated rows, allowing XML methods like value to be used on the type:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE

Result (XML type):

,[column1],[column2],[column3]

Invoking the method value('.', 'NVARCHAR(MAX)') on that XML node converts the XML node to an nvarchar(MAX) string. The value XML data type method takes an XQuery expression with '.' representing the current node (only node here) and the second argument being the desired SQL data type to be returned.

SELECT
         (select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)')

Result (nvarchar(MAX)):

,[column1],[column2],[column3]

Finally, the STUFF function removes the extraneous leading delimiter from the string. There is an error in your original example; the length should be 1 instead of 8 so that only the comma is removed:

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

Final result (nvarchar(MAX)):

[column1],[column2],[column3]

I sometimes see folks omit the , TYPE).value('.', 'NVARCHAR(MAX)') when using this technique. The problem with that is that some characters must be escaped with XML entity references (e.g; quotes) so the resultant string will not be as expected in that case.

EDIT:

Incorporating Mister Magoo's recommendation to use (./text())[1] instead of just . as the node spec, the query below will improve performance with large data sets.

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'),1,1,'');


How can I identify when to create a new table to hold data that can be obtained from a query? We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when figuring out the commission rate the agent gets, but nothing obscenely complex.

For example, it will probably never be more complex than this:

SELECT Payments.Amount * CASE
    WHEN DateDiff(year, Client.Received, Payments.DatePaid) = 1 THEN Rates.Rate1
    WHEN DateDiff(year, Client.Received, Payments.DatePaid) = 2 THEN Rates.Rate2
    ELSE Rates.Rate3 END

Would it make sense to build a 2nd table to hold this data instead of querying for it anytime it's needed? Or should I just stick with run-time queries that pull the data whenever it's requested?

And more importantly, what are the factors to use when determining if a query should be run anytime the data is needed, or if the data should be stored in a separate table of it's own?

One issue not covered in the accepted answer is "do you need this value over time" and "will the formula possibly change".

For instance consider the commision example. If the commission is paid, the amount should be stored as that is a historical figure of what was actually paid. The way to calulate commisions could change next month (and frequently does) but that won't change what was actually paid which must be stored separately.

It is the same idea as storing the price the customer actually paid for a product (after a calculation of discounts etc.) rather than relying on a formula against a price table to do anything except the initial calculation because the product price next month might not be the same as what the price was when the cutomer made the order.

If you need a historical record of what the value was at a point in time, always store that value after using the formula for the inital calulation.


In-place up-gradation of SQL server on Azure with pre-image installed. We have migrated our SQL server 2012  infrastructure from on-premise to Azure cloud recently where in all the production VM's are build with pre-images of SQL 2012 enterprise edition.

So,here is the question and issue that we are currently facing :

We want to upgrade SQL server from 2012 to 2016 and wanted to know if there is a way to perform in-place upgrade with pre-images installed without having to  actually migrate ? .

What we heard from our infrastructure team is that we can't do an in-place
up-gradation if it comes as a pre-image and will need to spin of another VM and perform migration which would incur cost again.

Any suggestions or workarounds without having to put migration efforts are much appreciated and thank you for all the help that you render to folks like me and to SQL community.

Once the VMs are deployed, you can do anything you want to them. Apply service packs, cumulative updates, or go full blown in place upgrade. You are responsible for anything that happens inside of the VM once you deploy it.  However, if you have to redeploy the VM, you may be reverted back to the previous version of SQL as it is built off of that image.

Additionally, it is a best practice to migrate to the new version of SQL vs doing in an in place upgrade. It is less risky to migrate and provides you more ways to roll back in the event of a problem than to do in place. You also can test your application on the new version before you upgrade so you know how your application will behave on the new version.

I can say with certainty, you will run into behavior differences as the query engine and cardinality estimator of SQL Server 2016 was completely rewritten to be much more efficient. In most cases, your existing queries will run faster, but in some cases, your queries will run slower. You will want to know that ahead of time so you can mitigate those issues before the users experience them and are soured on the platform.


How can I check a version of installed ODBC MariaDB connector? Several months ago I built MariaDB Connector/ODBC from Source following official instructions:

git clone https://github.com/MariaDB/mariadb-connector-odbc.git
cd mariadb-connector-odbc
cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCONC_WITH_UNIT_TESTS=Off -DCONC_WITH_MSI=OFF -DCMAKE_INSTALL_PREFIX=/usr/local .
cmake --build . --config RelWithDebInfo

But I don't remember what version of the Connector I built, so now I need to know it. How can I check version of MariaDB Connector/ODBC?

file won't give much information:

file libmaodbc.so
libmaodbc.so: ELF 64-bit LSB pie executable, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=5a3305eade1036378d57e16dcb09e02d541cc6a9, with debug_info, not stripped

A run of strings and grep commands gave me the version quite easily:

jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-3.0.9-ga-debian-x86_64/lib/libmaodbc.so | grep -A10 ^_client_version
_client_version
_server_host
_pid
_platform
libmariadb
3.0.9
Linux
x86_64
MYSQL_PWD
5.5.5-
compiled_in

However, that doesn't seem to match the release version well:

jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-2.0.18-ga-debian-x86_64/lib/libmaodbc.so | grep -A10 ^_client_version
_client_version
_pid
_platform
_server_host
libmariadb
2.3.6
Linux
x86_64
client
MYSQL_PWD
5.5.5-

I found another string like this, which seems to match the released version:

jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-2.0.18-ga-debian-x86_64/lib/libmaodbc.so | grep ma-
[ma-2.0.18]
jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-3.0.9-ga-debian-x86_64/lib/libmaodbc.so | grep ma-
[ma-3.0.9]
jynus@sangai:~/Downloads$ strings mariadb-connector-odbc-3.1.1-ga-rhel7-x86_64/lib/libmaodbc.so | grep ma-
[ma-3.1.1]

Use that command to find the version. Your mileage may vary, there may be a simpler way (e.g. using it), but this is a quick and dirty option.


Carriage return/line feed stopped working in SQL Server SQL was working fine last week with the old CHAR(13)+CHAR(10) for line feed/carriage return.

DECLARE @text varchar(2000)
SET @text =
'Attached is your new reporting ID and temporary password.'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ 'The new login/password will be updated  on ' + Convert(char(10), @ticketdate,101)

Last week this ran as expected giving me nicely formatted text with a space between the lines.

This week, the same code on the same server is returning one long run-on line of text.

This seems like this is a collation issue? Or something like that?

The code has worked as expect so something changed, but I can't identify what might have changed.

This seems like it could be collation issue? But it appears to be the default latin setting.

Since you're outputting to Grid View, I suspect that the option to Retain CR/LF on copy or save is disabled.  Enable this option, open a new query window, run the same query, and your newline characters should now come across after you paste results out of a Grid View.  In SSMS 2017, the Setting can be found under Tools → Options → Query Results → SQL Server → Results to Grid.  The path is in a similar location in other versions of SSMS.

Alternatively, if you want to apply this setting to the immediate query window only, enable the Retain CR/LF on copy or save option which can be found under Query → Query Options... → Results → Grid.  These settings won't be saved across all query windows, rather they are only applied to the current one.


Can't create database on volume mounted as folder (Error Msg 5123 & 1802) SQL Server 2016 SP1 + CU8 Installed on Windows Server 2016 Standard Edition.

Everything was working fine for a while and I was able to create databases until for some reason unbeknownst to me, a permissions issue popped up on my mounted volumes.

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.)
while attempting to open or create the physical file
'D:\MSSQL13.MSSQLSERVER\MSSQL\Data\UserDB\test.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created.
Check related errors.

The folder D:\MSSQL13.MSSQLSERVER\MSSQL\Data\UserDB is actually a volume mounted as a folder within the D:\MSSQL13.MSSQLSERVER\MSSQL\Data directory.

I googled around for hours and every link kept babbling on about ownership and icacls, and takecontrol, but that isn't the problem here. I want to be clear that the service account running SQL Server on this instance has FULL CONTROL over this mounted volume and what's above it too.

I verified Effective Access for the service account and it has explicitly granted FULL control.

My suspicion, is that it has something to do with granting permissions to a folder mounted as a volume instead of a disk. This i.s because in my tests, I can create a database with its files in the root of D:\ without any problems.
D:\ is also a volume using the same type of storage.

Also, my own user has full control, but when I try to create a file, I get this warning.

Strangely enough, I can add a folder to the UserDB folder/volume without difficulty.

How do I make this volume, mounted as a folder in the D:\ drive writeable for the MSSQL account?

Creating SQL Server Data files at the root of a volume is problematic, due to permissions issues. I always recommend creating a folder on the root of the volume, then placing data files inside that folder (this is also Microsoft's recommendation). Setting & verifying permissions at the root of the drive does not always work the same as setting permissions at the folder level.

When mounting the volume as a lettered drive, this means your data files would never go directly in D:\ but instead in D:\SomeFolder\. In your case, you are using a mount point where UserDb is the root of the volume. I'd suggest creating an additional folder inside your mount point: D:\...\Data\UserDb\SomeFolder\. Then, set permissions to make sure the SQL Server service account has full control for the SomeFolder directory.

Note that this guidance from Microsoft is still true:

  Do not install SQL Server to the root directory of a mount point, always specify a subdirectory for all files. This has to do with how permissions are granted. If you must put files in the root of the mount point you must manually manage the ACLs/permissions.

And also this more explicit advice for database files specifically:

  Warning: SQL Server does not support use of mount volume / mount point root directories for SQL Server databases.

Placing files directly in the root of the mount point means you'll have to manage permissions directly on every file to ensure they are correct. In the case of creating a new database (either from backup or from model), this can be problematic. As a result, it is best practice to never put files in the root of the mount point or drive. You can make it work, but it's not supported and not a good idea.


How to do incremental backup every hour in Postgres? Trying to do an hourly incremental backup of a single Postgres server (Win7 64).
I have the following setup in postgresql.conf:
max_wal_senders = 2
wal_level       = archive
archive_mode    = on
archive_command = 'copy "%p" "c:\\postgres\\foo\\%f"'

(restart)
I did a base backup with pg_basebackup -U postgres -D ..\foo -F t -x
Which made a big base.tar file in the foo folder and added some 16,384 KB files, which I assume are WALs.
What I don't understand is why the WALs in foo don't change. The WALs in data/pg_xlog change. Is pg not supposed to copy them over? How does it decide to do so?
Perhaps I need to set archive_timeout=3600 ?
I've seen several sites (pg's mailing lists, bacula's postgres page) that say you need to call pg_start_backup() and pg_stop_backup(), but I believe that those are not required. Is that true?
Secondary questions:

How often do the WALs in data/pg_xlog get written? What triggers a write?
It seems to update a WAL if I do some DML then \q in psql. Or edit a table in pgAdmin then close the window. I figured it would write on commit.

Best practices? pg_basebackup once a week? Archive WALs to same machine as PG or a remote machine?

There is an existing tool that'll help you a great deal, WAL-E. It provides an archive_command and restore_command for PITR to S3.

There are no commands to do incremental or differential logical backups. pg_dump can't take an incremental or differential. The only way to do that is via log archiving.

In theory you could take a new full backup, do a binary diff between it and the last backup, and upload the diff. This strikes me as a fragile and inefficient way to do things, though, and I really wouldn't recommend it.

Additionally, PgBarman supports integration with S3 via hook scripts, and will automate much of the backup rotation and management for you. Again, this may not be an option on Windows.


Keeping database in synch This might seem like a stupid question.
Can i restore logs of one database to another existing database of same schema.?

I have a database d1 in Server A.I took backup of that and restored as database d1  in another database server B
Then in server B i merged all data from database d1 to database d2.
But i still want to apply transnational logs from database d1(in Server A) to database d2.Is that possible?

This might seem a generic/broad question?But it is a valid question as i have similar scenario of migrating legacy data and current production data into a new sql server after merging current production into legacy, and stay in synch with current production.

I thought of log shipping and tried as well.The issue was server A is in lower version(2008 R2) and server B in higher version(2016).So i have to run log shipping in no recovery mode and i can't do merging on the data in secondary in no recovery mode.If i stop log shipping and bring the database to recover mode and do merge ,i can't put it back to non recovery mode again and enable log shipping.

Please note i merge database d1 to d2 in server B.

No.  In order for a log to be restored to a database, the log sequence number (LSN) of the log has to be the correct number to match what the database requires for the next log to be restored.  If you could somehow get the LSNs to match, restoring a log from a different database would corrupt it as some operations contain page IDs, and the page IDs are not going to be the same on both databases.

There are probably quite a lot of other reasons why this won't work, but it really isn't necessary to document all the reasons why as it is something that is simply not designed or expected to be able to work that way.


Is there performance loss in out of sequence inserted rows (MySQL InnoDB) I am trying to migrate from a MySQL AWS RDS instance with a huge SSD and too much excess space down to a small one, and data migration is the only method. There are four tables in the range of 330GB-450GB and executing mysqldump, in a single thread, while piped directly to the target RDS instance is estimated to take about 24 hours by pv (copying at 5 mbps).
I wrote a bash script that calls multiple mysqldump using ' & ' at the end and a calculated --where parameter, to simulate multithreading. This works and currently takes less than an hour with 28 threads.
However, I am concerned about any potential loss of performance while querying in the future, since I'll not be inserting in the sequence of the auto_increment id columns.
Can someone confirm whether this would be the case or whether I am being paranoid for no reasons.
What solution did you use for a single table that is in the 100s of GBs? Due to a particular reason, I want to avoid using AWS DMS and definitely don't want to use tools that haven't been maintained in a while.

Tables are by nature unsorted, so you will not have any performance loss on that site, after inserting your data, but we doen't how your instances is smaller, we can't  tell the impact it will have.
Your index on that field will be sorted and so will find the wanted rows quite fast, at least faster as scanning the hole column.


Produce fixed length output to file regardless of the length of data in each column/row Not sure about this as I'm kind new to this.
I need to output rows to fixed length files format.

The table's columns are ID, Name & Desc. The output should be the same length for each row regardless of the data length. Name in one row can be 5 chars and another can be 7 but in the end the file output should be 15.

So I believe the idea would be, I'll define the output length for each column and then output the column data and fill the difference between the data and the column length with ' '(spaces).

Not sure how to do that. I've read about rpad but it requires you know the length of the column date which is unknown to me.

The select below will add the spaces required for each field.  The date format is fixed to 10 characters.  The "|" sign is was added to show that the size of each coloum is fixed.  Can be removed if not needed.

SELECT LEFT(AccountCode + SPACE(20) , 20) +
'|' + LEFT(AccountName + SPACE(50) , 50)  +
'|' + LEFT((CAST(Account_Id AS NVarchar(10)) +
SPACE(10)), 10) + '|'+
FORMAT(LastPayDate , 'yyyy-MM-dd')
FROM [Accounts]


Use Create Trigger to create New table using calculated values from common groupid column values I have a TABLE_A in Database1 the contains rows with a common group id. I am wanting to use the CREATE TRIGGER function to generate a new TABLE_B in the same Database1 that contains calculated values (summed/multipled ect.) of certain columns with the same group id. and to group these all together. Using Mysql 5.5

ie:

TALBE_B (New Table Updated as TABLE_A data changes) (using CREATE TRIGGER..? )

Any help would be greatly appreciated!!!

We can use MySQL CREATE TABLE AS statement to create a TABLE_B from existing TABLE_A by copying the existing table's columns

CREATE TABLE TABLE_B AS
SELECT  id, name, SUM(cost) AS 'cost', organization
     FROM TABLE_A
     GROUP BY name, organization;

In MySQL, we cannot have after INSERT or UPDATE description together, like in Oracle unfortunately. So you have to create two TRIGGERs

delimiter //
CREATE TRIGGER update_tableb
 AFTER INSERT
  ON TABLE_A FOR EACH ROW
     BEGIN

   -- variable declarations

   -- trigger code

    END;//
delimiter ;


Relational database primary key requirements I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc.  The top-level entity corresponds to the business location, followed by entities for Room, Rack, RackU, and finally device.  Now, I know that primary keys are supposed to be unique, but is that unique over the entire database, or unique for the individual branch of the system?

By that I mean, there are one to many rooms in a location, and one to many racks in each room.  do the primary keys for the racks in one room in a given location need to be unique from those of either another room in the same location, or even from a room in a different location?

I probably didn't describe this very well, so feel free to ask for clarifications on or at any point.

For each relation (ie. table) the primary key value must be unique and immutable (ie. it doesn't change).

You're likely to have a relation for location, one for rooms, one for racks. The primary key values for each of them must all be unique, so two racks in different rooms would not have the same PK value. However you could have the same PK value for a rack and a location, because they are different relations. There would be no significance to the values being the same or different across relations.


Three tables with some matching data and some not - want to data from all three I have three tables

Costs (47 rows)
Budgets (60 rows)
Committed Costs (12 rows)

All three have the same 2 fields and than a number
JobNumber, CostCode, (either costs, budget, commitedcosts)

They all have the same JobNumber and they all CostCode in common.  However they also each have unique CostCodes.  I need to run a query that would get me results from all three tables like this:

JobNumber, CostCode, Costs, Budgets, CommitedCosts

Currently I am using outer joins on the three tables and only getting 47 results since the Costs table is the first table selected from.

Not sure if I'm completely following but maybe this is what you are looking for?

I'm taking all three tables and then using UNION ALL (combine but don't remove duplicate records) to convert them into a single table, placing NULL's in the columns that are not shared. Then I'm taking that consolidated table and grouping on your two common columns (JobNumber, CostCode). I'm not sure what kind of data you're looking to get out of the other three columns...are you looking for the COUNT of records from each table for that JobNumber+CostCode? Or maybe the SUM?

So this sample query, will give you a unique list of JobNumber+Costcode, and the Sum of Costs, Budgets and CommitedCosts for each one:

SELECT x.JobNumber, x.CostCode
    , Costs = SUM(x.Costs)
    , Budgets = SUM(x.Budgets)
    , CommitedCosts = SUM(x.CommitedCosts)
FROM (
    SELECT JobNumber, CostCode
        , Costs
        , Budgets = NULL
        , CommitedCosts = NULL
    FROM Costs
    UNION ALL
    SELECT JobNumber, CostCode
        , Costs = NULL
        , Budgets
        , CommitedCosts = NULL
    FROM Budgets
    UNION ALL
    SELECT JobNumber, CostCode
        , Costs = NULL
        , Budgets = NULL
        , CommitedCosts
    FROM CommitedCosts
) x
GROUP BY x.JobNumber, x.CostCode


Backup Database Task - What's the number at the end of the filename? When using a SQL Server 2012 EMaintenance Plan for backup purposes, the Backup Database Task creates a unique filename. I understand the format all the way to the final set of numbers.

For example, a Full Database Backup initiated via the Backup Database Task would create a filename like:

  AdventureWorks_backup_2014_12_17_080005_5862824.bak

What is 5862824 referencing? Is than LSN? Something else?

Since this logic is buried within the code for the SSIS package behind maintenance plans, I think you will only get guesses unless the author happens to swing by here. Here is my educated guess based on a few experiments:

For SQL Server 2008 and above, the 7 digits are the sub-second portion of the datetime2(7) output of SELECT SYSDATETIME(); at the moment the T-SQL for the backup command is actually generated. If you create a subplan for all user databases, and then go in to edit and choose View T-SQL, you will see this:

BACKUP DATABASE [SQLSentry] TO  DISK = N'<path>\SQLSentry_backup_2014_12_17_114845_6488663.bak'
WITH NOFORMAT, NOINIT,  NAME = N'SQLSentry_backup_2014_12_17_114845_6488663',
SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Views] TO  DISK = N'<path>\Views_backup_2014_12_17_114845_6644778.bak'
WITH NOFORMAT, NOINIT,  NAME = N'Views_backup_2014_12_17_114845_6644778',
SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

You'll notice that component of the filename, which happens to conveniently come after HHMMSS, is increasing with each subsequent command, but the numbers are still grouped together to some extent. If you repeat this a few times you will also see that they change every time, that they are not ever-increasing, and that they loop around to even show 7-digit numbers with leading 0s. Which indicates to me that it is not a sequence of any kind, and certainly not any component of the LSN or other meaningful properties of the database itself.

I couldn't think of a very easy way to have the SSIS package also include some output that would indicate SYSDATETIME() at the point of generation, and even if I could, I would have no idea if it would generate that value before or after, and how long it would take or what other delays it would introduce. So I'm not sure there is a way to confirm that's what it is.


MongoDB aggregate performance compared to Postgres I have collection with about 700,000 documents. These documents represent locations in a hex grid. There are only 500 different locations (ll_16k).  The aggregation of the 700,000 documents into 500 documents takes about 2 seconds.

  { $group:
    { _id: "$properties.ll_16k",
      total:
        { $sum: "$properties.price" },
      count:
        { $sum: 1 }
    }
  }

The same action in Postgres is flying... This query performs routinely under 500ms.

SELECT
  ll_16k,
  count(ll_16k),
  sum(price)
FROM ppd_2015_perf
GROUP BY ll_16k;

I am just getting started with MongoDB. Am I doing something wrong here or is this the expected performance difference?

This is a test subset. The actual dataset will be 20,000,000 documents/records in size and expected to grow about 1,000,000 records per year.

One difference to consider is the amount of IO happening. For Postgres data is normalised into tables and tables are read one page at at time. To complete the SQL it need only read however much disk is required to store all of table ppd_2015_perf.

Mongo's unit of IO, however, is a whole document.  All the denormalised/ duplicated stuff comes in along with the three fields in which you're interested.

Of course there a lot of things assumed here - RAM, compression, disk performance and data models being some.