any way to speed replication creation through scripts

As part of my previous thread on migrating the publisher to a new server, I
had scripted the drop and create replication scripts and I have around 100
articles and a few subscribers and what i want to do is to create the trans
replication with the "nosync" option as i ensured that the publisher and
subscribers are in sync.
So what i noticed is that although the drop replication runs in a min or 2,
the create runs for almost 30 mins
And looking into the script, I see that for each article,
it first runs
1) sp_add_article
and then for every subscriber runs
1) sp_addsubscription
2) sp_addpushsubscription_agent
and it runs all this in serial.
So the question is, can i somehow speed this up ? Is there a way to run
sp_addsubscription that say run it for "all" articles
Also I noticed that sp_addpushsubscription_agent just run the same set of
parameters except the subscriber name is different and it repeats it every
time for every article
Can someone please let me know if I could somehow avoid those default
scripted sql and make this all work at the end faster with less stored
procs?
I am using SQL 2005
Thanks
Replication puts schema locks on tables momentarily as part of the
publication creation process. You will get much faster publication creation
times if you kick all your users out of the database before creating the
publication(s).
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:eMmCKuUPIHA.4440@.TK2MSFTNGP06.phx.gbl...
> As part of my previous thread on migrating the publisher to a new server,
> I had scripted the drop and create replication scripts and I have around
> 100 articles and a few subscribers and what i want to do is to create the
> trans replication with the "nosync" option as i ensured that the publisher
> and subscribers are in sync.
> So what i noticed is that although the drop replication runs in a min or
> 2, the create runs for almost 30 mins
> And looking into the script, I see that for each article,
> it first runs
> 1) sp_add_article
> and then for every subscriber runs
> 1) sp_addsubscription
> 2) sp_addpushsubscription_agent
> and it runs all this in serial.
> So the question is, can i somehow speed this up ? Is there a way to run
> sp_addsubscription that say run it for "all" articles
> Also I noticed that sp_addpushsubscription_agent just run the same set of
> parameters except the subscriber name is different and it repeats it every
> time for every article
> Can someone please let me know if I could somehow avoid those default
> scripted sql and make this all work at the end faster with less stored
> procs?
> I am using SQL 2005
> Thanks
>
|||So what i tried yesterday and would like to validate with the experts here
is I first ran the sp_addarticle for each and every article
and then ran once for every subscriber the following 2 stored procs
sp_addsubscription -- I specified all articles here
sp_addpushsubscription_agent
If you script replication the above 2 stored procs runs for every article,
hence has 1000 more calls to these sprocs as opposed to what i did above and
run it once for every subscriber
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eYEdzKaPIHA.1756@.TK2MSFTNGP06.phx.gbl...
> Replication puts schema locks on tables momentarily as part of the
> publication creation process. You will get much faster publication
> creation times if you kick all your users out of the database before
> creating the publication(s).
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:eMmCKuUPIHA.4440@.TK2MSFTNGP06.phx.gbl...
>

ANSI_NULL and ANSI_QUOTED_IDENTIFIER

I am trying to set these two options to ON for default sp creation. or at LEAST be able to set them for sp's accessing databases on remote servers.

Seems though, that no matter what I set the rekated checkboxes to in the "edit SQL Server registration properties"'s connections pane, they get created as:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[sp_yada_yada_yada]
AS

DECLARE @.YoMama float
DECLARE @.YoDaddy float
DECLARE @.YoSista float

SET @.YoSista = @.YoDadd + @.YoMama

RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

(which I assume is a previous/default setting of some type).

I also had someone tell me that I need to set those params when I initially register the server, but I can't see any option to do so when I register my server(s) *scratching head*

I even verified in the connection pane's "running values" radio button, and they ARE checked (as I expected) but creating a new SP STILL results in the incorrect option settings being auto-magically applied.

help?
Thanks!


Any SET options that affect the results generated by Transact-SQL statements must have the same settings for all operations referencing the index. There are seven SET options that affect the results stored in computed columns and returned by views. All connections using indexes on computed columns or indexed views must have the same settings for these seven options:

These six SET options must be set to ON:
ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YIELDS_NULL

QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF.
These SET options must be set correctly for any connection that creates an index on a view or computed column. Any connection executing INSERT, UPDATE or DELETE statements that change data values stored in the indexes must have the correct settings.Why are you doing this?

Leave the settings alone unless you HAVE to change them...|||Originally posted by Brett Kaiser
Why are you doing this?

Leave the settings alone unless you HAVE to change them...

It's my understanding that I need to change them to keep from getting the error I'm getting when I try to access a database on a linked server...
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

I know of no other way around it - - currently I create my stored procedures and functions that access the linked servers though Query Analyzer - because it pays attention to my SET OPTIONS stuff outside the function/stored procedure.

If there's a way to get it to be kind to my linked server queries from the SQL Server Enterprise Manager, that would be cool...but nothing I've tried SO FAR will convince it to be nice to me.

Maybe there's a blurb in the Books Online that can help me understand more about why I shouldn't set these options to ON when doing a query to a linked server, but everything I've read so far says then need to be set ON...*shrug*|||found my answer here...but still...
http://www.dbforums.com/showthread.php?threadid=971846&highlight=Heterogeneous+queries+require+the+ANSI_N ULLS+and+ANSI_WARNINGS+options+to+be+set+for+the+c onnection

I guess it makes sense to just do it in the creation of the specific SP, rather than modifying a server option I know very little about *LOL*

Thanks for the loan of your time though :D|||Keep on truckin....

ANSI PADDING OFF not working

Hi,

I have set ANSI PADDING off by default for the database but even then when I create any table ANSI PADDING is on.

While creation of table I am able to set it off before I create it and then create the table with correct settings. But when I am altering the table it is always off even when I am setting specifically before altering the table.

Any ideas how to make this work?

Thanks.

The SQL Server ODBC driver and OLEDB provider sets several SET options ON by default. And ANSI_PADDING is one of those. So it deosn't matter if you switch it OFF at the database level. Any connection made by one of the data access API will automatically have ANSI_PADDING ON. I am curious as to why you want to set it to OFF. It is recommended to use the ANSI settings by default since lot of features depend on it (like computed column indexes, indexed view matching etc) and it is also compatible with other databases like DB2/Oracle. See the topics below for more details:

SET ANSI_PADDING topic

http://msdn2.microsoft.com/en-us/library/ms187403.aspx

SET ANSI_DEFAULTS topic

http://msdn2.microsoft.com/en-us/library/ms188340.aspx

Btw, the only way is to modify your application code to set ANSI_PADDING off after making the connection. This also means that if you need to work with the schema in OSQL or ISQLW or SSMS for example you need to do the same because they all use OLEDB/ODBC/ADO.NET to make connection to SQL Server. So it is actually easier to get into trouble not using the defaults. Note that in the future we may remove the SET options and consider making the ANSI settings default.

|||If ANSI Padding is on is stored with spaces and we do not want that.|||

I am not sure if there is a question in your response. If you want to use ANSI_PADDING OFF on the server-side then you need to enable in explicitly after connection. This is the only way due to the reasons I described before. Best is to trim values before it reaches the database server on the client-side.

Additionally, you need to be aware of recompilation of SPs/statements that access tables with ANSI_PADDING off and the session setting is different. So please be aware of all these limitations in addition to other missing enterprise features (computed column matching, indexed views matching etc). See the link below for the how batch compilation, recompilation etc works and how the SET options are used:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

|||

I can give you reasons NEVER to have padding on. Any tables you create will have a hidden padding on setting so that you can no longer control padding with set, ODBC, ADO or even database level switches. But more importantly, features such as the like clause with the underscore pattern match will return incorrect results on any of these “damaged” tables.

Eg. Select fred where fred like ‘123__’

Will return results of:

123

123A

123AB

It should only return 123AB. <null> should not be a character. Time consuming bugs like these are the reason I would recommend never setting padding on and I hope you seriously consider defaulting to padding off in future. It's by far the more useful setting.

ANSI PADDING OFF not working

Hi,

I have set ANSI PADDING off by default for the database but even then when I create any table ANSI PADDING is on.

While creation of table I am able to set it off before I create it and then create the table with correct settings. But when I am altering the table it is always off even when I am setting specifically before altering the table.

Any ideas how to make this work?

Thanks.

The SQL Server ODBC driver and OLEDB provider sets several SET options ON by default. And ANSI_PADDING is one of those. So it deosn't matter if you switch it OFF at the database level. Any connection made by one of the data access API will automatically have ANSI_PADDING ON. I am curious as to why you want to set it to OFF. It is recommended to use the ANSI settings by default since lot of features depend on it (like computed column indexes, indexed view matching etc) and it is also compatible with other databases like DB2/Oracle. See the topics below for more details:

SET ANSI_PADDING topic

http://msdn2.microsoft.com/en-us/library/ms187403.aspx

SET ANSI_DEFAULTS topic

http://msdn2.microsoft.com/en-us/library/ms188340.aspx

Btw, the only way is to modify your application code to set ANSI_PADDING off after making the connection. This also means that if you need to work with the schema in OSQL or ISQLW or SSMS for example you need to do the same because they all use OLEDB/ODBC/ADO.NET to make connection to SQL Server. So it is actually easier to get into trouble not using the defaults. Note that in the future we may remove the SET options and consider making the ANSI settings default.

|||If ANSI Padding is on is stored with spaces and we do not want that.|||

I am not sure if there is a question in your response. If you want to use ANSI_PADDING OFF on the server-side then you need to enable in explicitly after connection. This is the only way due to the reasons I described before. Best is to trim values before it reaches the database server on the client-side.

Additionally, you need to be aware of recompilation of SPs/statements that access tables with ANSI_PADDING off and the session setting is different. So please be aware of all these limitations in addition to other missing enterprise features (computed column matching, indexed views matching etc). See the link below for the how batch compilation, recompilation etc works and how the SET options are used:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

|||

I can give you reasons NEVER to have padding on. Any tables you create will have a hidden padding on setting so that you can no longer control padding with set, ODBC, ADO or even database level switches. But more importantly, features such as the like clause with the underscore pattern match will return incorrect results on any of these “damaged” tables.

Eg. Select fred where fred like ‘123__’

Will return results of:

123

123A

123AB

It should only return 123AB. <null> should not be a character. Time consuming bugs like these are the reason I would recommend never setting padding on and I hope you seriously consider defaulting to padding off in future. It's by far the more useful setting.

ANSI PADDING OFF not working

Hi,

I have set ANSI PADDING off by default for the database but even then when I create any table ANSI PADDING is on.

While creation of table I am able to set it off before I create it and then create the table with correct settings. But when I am altering the table it is always off even when I am setting specifically before altering the table.

Any ideas how to make this work?

Thanks.

The SQL Server ODBC driver and OLEDB provider sets several SET options ON by default. And ANSI_PADDING is one of those. So it deosn't matter if you switch it OFF at the database level. Any connection made by one of the data access API will automatically have ANSI_PADDING ON. I am curious as to why you want to set it to OFF. It is recommended to use the ANSI settings by default since lot of features depend on it (like computed column indexes, indexed view matching etc) and it is also compatible with other databases like DB2/Oracle. See the topics below for more details:

SET ANSI_PADDING topic

http://msdn2.microsoft.com/en-us/library/ms187403.aspx

SET ANSI_DEFAULTS topic

http://msdn2.microsoft.com/en-us/library/ms188340.aspx

Btw, the only way is to modify your application code to set ANSI_PADDING off after making the connection. This also means that if you need to work with the schema in OSQL or ISQLW or SSMS for example you need to do the same because they all use OLEDB/ODBC/ADO.NET to make connection to SQL Server. So it is actually easier to get into trouble not using the defaults. Note that in the future we may remove the SET options and consider making the ANSI settings default.

|||If ANSI Padding is on is stored with spaces and we do not want that.|||

I am not sure if there is a question in your response. If you want to use ANSI_PADDING OFF on the server-side then you need to enable in explicitly after connection. This is the only way due to the reasons I described before. Best is to trim values before it reaches the database server on the client-side.

Additionally, you need to be aware of recompilation of SPs/statements that access tables with ANSI_PADDING off and the session setting is different. So please be aware of all these limitations in addition to other missing enterprise features (computed column matching, indexed views matching etc). See the link below for the how batch compilation, recompilation etc works and how the SET options are used:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

|||

I can give you reasons NEVER to have padding on. Any tables you create will have a hidden padding on setting so that you can no longer control padding with set, ODBC, ADO or even database level switches. But more importantly, features such as the like clause with the underscore pattern match will return incorrect results on any of these “damaged” tables.

Eg. Select fred where fred like ‘123__’

Will return results of:

123

123A

123AB

It should only return 123AB. <null> should not be a character. Time consuming bugs like these are the reason I would recommend never setting padding on and I hope you seriously consider defaulting to padding off in future. It's by far the more useful setting.